0

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?

Alex
  • 3
  • 3

2 Answers2

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
Tony
  • 37
  • 1
  • 9
  • Thank you so much, Tony. This worked (especially the tip with anonymous access was very important). A follow up question: The idea behind accessing Clockify via API, was to get all my time entries in clockify with user name, description, project, client name, hourly rate, task, time interval etc as one long table. Do you know, how to do that? – Alex Nov 14 '18 at 17:11
  • I found a solution for my problem: Instead of adressing the workspace and the time entries, I built a report and addressed this one via https://api.clockify.me/api/reports/{your report ID} That's how I got access to all detailed time entries – Alex Nov 14 '18 at 23:45
  • @Alex, glad you figured it out. Honestly, I don't know anything about the Clockify API; I created an account yesterday just to figure out a solution to your specific problem. – Tony Nov 15 '18 at 21:17
  • @Alex, how did you get the report ID? – Steve Jan 31 '20 at 16:29
0

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

Jonathan Schiffner
  • 146
  • 10
  • Thank you Jonathan, to my regret it doesn't work. If I use X-Api-Key as proposed, I get an Expression syntax error. And if I use XApiKey I also get an expression error. = Web.Contents("api.clockify.me/api/workspaces/MYWORKSPACEID/…", [ Query=[ #"filter"="", #"orderBy"=""], XApiKey="APIToken" ]) --> Expression.Error: "XApiKey" ist keine gültige Web.Contents-Option. Gültige Optionen: ApiKeyName, Content, ExcludedFromCacheKey, Headers, IsRetry, ManualStatusHandling, Query, RelativePath, Timeout Any other ideas? – Alex Nov 08 '18 at 21:52
  • Hm Okay. I guess then that's not the problem. I just looked at your screenshot again and for me, it sounds a lot like your login data is wrong. Are you sure you are using the right API key? Have tried generating a new one? – Jonathan Schiffner Nov 09 '18 at 16:10
  • Thanks again. And yes I generated a new Api key 3 times for test purposes. – Alex Nov 10 '18 at 17:10