I’ve been using the Lockify API for almost two years now. I used to extract data regularly through the API to get real-time insights, and everything was working well. However, recently, I faced an issue where it stopped extracting data for the entire year. I checked the Lockify API, but it seems unchanged, and I couldn’t figure out why this problem occurred.

To troubleshoot, I turned to Clockify Reports and exported data from there for comparison. It turns out the issue is in the code, but I’m struggling to identify and fix it. I’m hopeful that someone can assist me in resolving this issue.

Below is the API format which i am using.

    Dim httpCaller  As MSXML2.XMLHTTP60, body As String
    Set httpCaller = New MSXML2.XMLHTTP60
    
   body = "{""dateRangeStart"": ""2023-01-01T00:00:00.000"", " & vbLf & _
           " ""dateRangeEnd"": ""2023-12-31T23:59:59.000"", " & vbLf & _
           " ""detailedFilter"": {""page"": 1,""pageSize"": 1000}} "
  
   ' Debug.Print body
    httpCaller.Open "POST", "https://reports.api.clockify.me/v1/workspaces/Key/reports/detailed"
    httpCaller.setRequestHeader "X-Api-Key", "API_Key"
    httpCaller.setRequestHeader "Content-Type", "application/json"
    httpCaller.send body

>Solution :

Within the detailedFilter key, you are using static values for the page value. This means that it will only ever return the latest 1000 records. You’ll need to create a loop to determine how many total records are within your daterange.

There is a field called entriesCount in every response which you can calculate off of and loop through the pages iteratively. For example, if the entriesCount value is 3250, you’ll need to repeat the API call where the page value increases iteratively as the values 1,2 then 3.

The reason why older timesheets are not included is that Clockify returns the timesheets newest to oldest so it is returning the 1000 most recent timesheets.