Based on Alex' question (and own answer of this) here I have had a connection to a saved clockify report that has worked without issues for a while. However recently clockify has introduced pagination in the saved reports with the result that only (up to) the first 50 entries are returned from the report.
Clockify support has pointed me in the direction of "count" or "page" as possible solutions ("count" (default 50) should indicate the number of returned entries and "page" should indicate the page of the report returned). Unfortunately i am not able to make either make a difference in what is returned.
= Json.Document(
Web.Contents("api.clockify.me/api/reports/{my report}",
[Headers= [ContentType="application/json",
#"X-Api-Key"="{my API Key}",
count="9999"
]
]
)
)
The example above, i believe should return up to 9999 entries, but actually only returns up to 50. The "count" parameter is completely ignored...
I was barking up the wrong tree...
It turns out there is no way to avoid the pagination in the saved reports, and the count parameter does nothing. This API is in development, and as such, changes like this can happen.
Instead clockify support suggests using POST /workspaces/{workspaceId}/reports/summary/ instead, which requires some parameters in the body of the request. Example:
{
"startDate": "2018-01-01T00:00:00.000Z",
"endDate": "2018-12-31T23:59:59.999Z",
"me": "false", (Options: "false", "true", "TEAM", "ME", "null")
"userGroupIds": [],
"userIds": [],
"projectIds": [],
"clientIds": [],
"taskIds": [],
"tagIds": [],
"billable": "BOTH", (Options: "BOTH", "BILLABLE", "NOT_BILLABLE")
"includeTimeEntries": "true",
"zoomLevel": "week", (Options: "week", "month", "year")
"description": "",
"archived": "Active", (Options: "Active", "All", "Archived")
"roundingOn": "false"
}
(I got a few more parameters from clockify support, but as far as i can tell, at present they do not make a difference. I have outcommented them below.)
I got this to work in Power Query:
let
url="https://api.clockify.me/api/workspaces/{workspaceId}/reports/summary/",
content ="{
""startDate"": ""2017-01-01T00:00:00.000Z"",
""endDate"": ""2025-12-31T23:59:59.999Z"",
""me"": ""false"",
""userGroupIds"": [],
""userIds"": [],
""projectIds"": [],
""clientIds"": [],
""taskIds"": [],
""tagIds"": [],
""billable"": ""BOTH"",
""includeTimeEntries"": ""true"",
""zoomLevel"": ""week"",
""description"": """",
""archived"": ""Active"",
""roundingOn"": ""false"",
""groupingOn"": ""false""
"/* ,
""groupedByDate"": ""false"",
""page"":0,
""count"":9999,
""sortDetailedBy"": ""timeAsc"" (Options: "description", "descriptionAsc", "userName", "userNameAsc", "duration", "durationAsc", "time", "timeAsc")
*/
&" }",
Source = Json.Document(
Web.Contents(url, [Headers= [ #"X-Api-Key"="{yourAPIkey}"
,ContentType="application/json"
,#"Content-Type"="application/json"
]
,Content=Text.ToBinary(content)
]
)
)
in
Source
(Sources: https://community.powerbi.com/t5/Desktop/How-to-add-body-into-Web-Contents/td-p/128996
Further development: