Trying to Export the Data from Clockify-CodePudding

I have been trying to extract all the data from Clockify into Excel using VBA and using below code but there is no output coming with that code Your help will be much appreciated.

Getting this reponse when run the code {"code":405,"message":"HTTP 405 Method Not Allowed"}

Public Sub GetAllProjects()
    
Dim httpCaller As MSXML2.XMLHTTP60
Set httpCaller = New MSXML2.XMLHTTP60

httpCaller.Open "GET", "https://reports.api.clockify.me/v1/workspaces/*****/reports/detailed"
httpCaller.setRequestHeader "X-Api-Key", CLOCKIFY_API_KEY
httpCaller.setRequestHeader "Content-Type", "application/json"
httpCaller.send
Debug.Print httpCaller.responseText
    
End Sub

CodePudding user response：

A POST would look something like this in VBA:

Public Sub GetAllProjects()
    
    Dim httpCaller As MSXML2.XMLHTTP60, body As String, obj As Object, json As Object, result
    Dim ti As Object
    
    'not sure how much of the request body is required....
    'adjust dates below as needed
    body = "{""dateRangeStart"": ""2020-05-10T00:00:00.000"", " & vbLf & _
            " ""dateRangeEnd"": ""2020-05-16T23:59:59.000"", " & vbLf & _
            " ""detailedFilter"": {""page"": 1,""pageSize"": 50}} "
    Debug.Print body
    
    Set httpCaller = New MSXML2.XMLHTTP60
    With httpCaller
        .Open "POST", "https://reports.api.clockify.me/v1/workspaces/*****/reports/detailed"
        .setRequestHeader "X-Api-Key", CLOCKIFY_API_KEY
        .setRequestHeader "Content-Type", "application/json"
        .send body  'include JSON body
        result = .responseText
        Debug.Print "---Response---" & vbLf & result
    End With
    
    Set json = JsonConverter.ParseJson(result)
    
    For Each obj In json("totals")
        Debug.Print "------"
        Debug.Print obj("totalTime")
        Debug.Print obj("totalBillableTime")
        Debug.Print obj("entriesCount")
        Debug.Print obj("totalAmount")
    Next obj
    
    For Each obj In json("timeentries")
        Debug.Print "------"
        Debug.Print obj("_id")
        Debug.Print obj("description")
        'etc etc
        'access the "timeinterval" sub-dictionary
        Set ti = obj("timeInterval")
            Debug.Print , ti("start")
            Debug.Print , ti("end")
            Debug.Print , ti("duration")
    Next obj

End Sub