如何通过Power Query访问Clockify API [英] How to access Clockify API through Power Query

问题描述

我正在尝试通过Power Query直接将数据从Clockify API中获取到Excel.我在Power Query中使用以下代码:

I am trying to get my time entries from Clockify API directly via Power Query to Excel. I use the following code in the Power Query:

= Web.Contents("https://api.clockify.me/api/workspaces/ID of my workspace/timeEntries/", [
 Query=[ #"filter"="", #"orderBy"=""],
 ApiKeyName="APIToken"
])

当我随后尝试运行代码时，系统会提示我输入Web API身份验证，在输入正确的Web API代码在此处查看屏幕截图

When I then try to run the code I am prompted to enter the Web API authentification, which delivers an error ("English translation: Authentification does not work. Try again") after I enter my correct Web API code see screenshot here

有人知道如何解决吗?

推荐答案

在Power Query/M中调用基于REST的API时要记住两点:

There are two things to keep in mind when making calls to REST-based APIs in Power Query/M:

1. 使用Web.Contents()函数时，最好将API密钥作为参数传递给请求标头本身.在您的情况下，X-Api-Key应该等于{your API key}.

1. When using the Web.Contents() function, it's best to pass your API key as a parameter within the request header itself. In your case X-Api-Key should equal to {your API key}.

使用匿名访问连接到API.屏幕快照表明您正在尝试使用"Web API"进行连接.清除Schlüssel"字段中的值，然后使用"Anonym".

Use anonymous access to connect to the API. Your screenshot suggests you're trying to connect using "Web API". Clear the value in the "Schlüssel" field and use "Anonym" instead.

这是一个简单的示例，其中我按工作空间ID返回有关工作空间的信息. (我已经屏蔽了我的工作区ID和API密钥；将这些值替换为您的工作区ID和API密钥.)

Here's a simple example where I return the information about a workspace by workspace ID. (I've masked both my workspace ID and API key; replace these values with your workspace ID and API key.)

这在Excel和Power BI中都对我有效:

This works for me in both Excel and Power BI:

let
    Source = 
        Web.Contents(
            "https://api.clockify.me/api/workspaces/{your workspace ID}", 
            [
                Headers=[
                    #"Content-Type"="application/json", 
                    #"X-Api-Key"={your API key}
                ]
            ]
        ),
    jsonResponse = Json.Document(Source)
in
    jsonResponse

这篇关于如何通过Power Query访问Clockify API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！