I try to get a list of actions from iAuditor filtered by "template_id" by sending an HTTP Request with VBA. For the moment I have this code that return the list of all actions:
Sub Get_Data()
Dim hReq As Object
Dim sht As Worksheet
Dim authKey As String
Dim response As String
authKey = "d8a0df7d7e1XXXXXXXXXXXXXXXXXXXXXXXXff3c765cf2fcf"
Set sht = Sheets(1)
Dim strUrl As String
strUrl = "https://api.safetyculture.io/tasks/v1/actions/list"
Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "POST", strUrl, False
.SetRequestHeader "Authorization", "Bearer " & authKey
.SetRequestHeader "Content-Type", "application/json"
.Send
End With
response = hReq.ResponseText
'MsgBox Len(response)
sht.Range("A1") = response
End Sub
Now I want to "translate" a filter to VBA but I have no idea how to do it.
Here is my API Documentation about the website : https://developer.safetyculture.io/#actions
So what i want to translate in VBA looks like this in HTTP code i guess :
curl -X POST "https://api.safetyculture.io/tasks/v1/actions/list" \
-H "Authorization: Bearer {api_token}" \
-d {
"template_id": {
"operator": 7,
"value": ["fc2e53f6-4712-4ca5-b681-aba3ac954217"]
}
}
I thank You a lot for any help you can offer me.
Regards, Gabin
CodePudding user response:
According to this thread: https://www.vbforums.com/showthread.php?592624-Posting-data-using-XMLHttpRequest
you can add Data to a POST Request by passing it as a value in the send function:
hReq.send "'template_id': {'operator': 7,'value': ['fc2e53f6-4712-4ca5-b681-aba3ac954217']}"
Values are differentiated by a & Symbol inside the string.
CodePudding user response:
I've did it !!! :D
Here is my final code:
Sub Get_Data()
Dim hReq As Object
Dim sht As Worksheet
Dim authKey As String
Dim response As String
Dim response_objet As Object
Dim Body As String
authKey = "d8a0df7d7e19XXXXXXXXXXXXXXXXc65ec9eff3c765cf2fcf"
Body = "{ ""filters"": [ { ""template_id"": { ""value"": [""884b95df35104f4792a3c1fdfed63f0e""]}}]}"
Set sht = Sheets(1)
sht.Range("A3") = Body
Dim strUrl As String
strUrl = "https://api.safetyculture.io/tasks/v1/actions/list"
Set hReq = CreateObject("MSXML2.XMLHTTP")
With hReq
.Open "POST", strUrl, False
.SetRequestHeader "Authorization", "Bearer " & authKey
.SetRequestHeader "Content-Type", "application/json"
.Send Body
End With
response = hReq.ResponseText
'MsgBox Len(response)
sht.Range("A1") = response
Set response_objet = JsonConverter.ParseJson(response)
For Each Item In response_objet
a = a 1
Sheets("Biblio").Range("B" & a) = Item
Next Item
End Sub
Huge thanks to @CodingWolf , he put me on the right direction.
Have a nice day internet. Gabin,