0

I am trying to get all data into excel using the Get data from web option but I am not sure how to properly configure it. I did get the API key.

EDIT: Below is the path I use, but that is only the time entries for one user, I need them for ALL users. https://api.clockify.me/api/v1/workspaces/myWorkspace/user/myUser/time-entries

Any help is appreciated.

Steve
  • 1,028
  • 7
  • 25
  • 42

1 Answers1

1

EDIT: I didn't read that you wanted to use Power Query in Excel. The Advanced Editor only works with this code in Power BI Desktop.

You'll need to program the request in the Advanced Editor in Power Query.

Here is an example of connecting to the Get Workspace Clients endpoint:

let

   baseUrl = "https://api.clockify.me/api/v1/",
   apiKey = "your-api-key",
   workspaceID = "5dfb421c1b30342708229760",

GetWorkspace = (workspaceID as text) =>
   let
       options = [Headers = [#"X-Api-Key"= apiKey, #"Content-Type" = "application/Json"], 
                            RelativePath = "workspaces/" & workspaceID & "/clients"],
       call = Web.Contents(baseUrl, options),
       jsonParsed = Json.Document(call)
   in
       jsonParsed

in
    GetWorkspace

Using this function you just have to change the parameters you need according to the endpoint you want to hit. The baseUrl will be the same, you'll need to change the RelativePath with the rest of the url and if you need to pass some query parameters, put them after RelativePath in a record, like this:

RelativePath = "workspaces/" & workspaceID & "/clients", Query = [page = "1"]],

I recommend using Postman to make the calls and Fiddler to track how the Url is being constructed. Then compare Postman requests with your power query requests, to check the differences.

Here are some other threads about the subject:

How to access Clockify API through Power Query

How to pull data from Toggl API with Power Query?

Dreekun
  • 422
  • 2
  • 12
  • Thank you for your suggestions, I should have been much clearer in my ask. I already have been using PostMan but I am not able to pull ALL the data for ALL users in a specified workspace. When I open excel, I want all the data available for all my reports I need to create. Currently, I can only use the data for ONE user: https://api.clockify.me/api/v1/workspaces/myWorkspace/user/myUser/time-entries – Steve Feb 01 '20 at 15:43
  • Okay, so you need to figure out the endpoint that suits your needs, if there is one. It is possible to make a request dynamic, and maybe enter the User as parameter. That way we could fetch the data for all users, as long as we have the user ids of the workspace, or something like that. – Dreekun Feb 06 '20 at 11:18
  • Yes, that is a good idea, as their current implementation of the API does not support pulling all data for all users yet. Thank you very much for your suggestions. – Steve Feb 06 '20 at 22:44
  • You're welcome. If they do implement that and you need any help, feel free to write back. – Dreekun Feb 07 '20 at 09:20