How to access Clockify API through 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"
])

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

Does anyone have an idea how to solve this?

回答1:

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

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}.

2. 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.

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.)

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

回答2:

Doesn't it have to be X-Api-Key instead of ApiKeyName?