I've been scouring the interwebs for days now looking for the solution to translating my API script into VBA to allow it to operate directly within an Excel file. I have managed to replicate the signature key that is produced in my python script. However I'm still hitting invalid credential errors as I believe i'm structuring the headers and parameters in the correct way.
HEADER_A,HEADER_B and HEADER_C are placeholders for the headers required as outlined in the documentation. The parameter "job_id" is also required.
Working python script:
signature = generateSignature(sigtag,key)
headers = {"HEADER_A": signature,
"HEADER_B": timestamp,
"HEADER_C": employee_id}
apirequest = requests.get(GEOPAL_BASE_URL + apicall,
headers=headers,
params={"job_id": job_id})
apifeedback = apirequest.json()
I've been piecing together approaches to the problem, however i cannot find an example where someone has had to use both parameters and headers like I had to use with the requests library above.
Here is my progress so far in VBA:
Private Sub cmdgetjobinfo()
Dim URL As String, timestamp As String, empID As String, key As String, method As String, signature As String, hash As String, params As String
Dim objHTTP As Object: Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
Dim ws As Worksheet: Set ws = Worksheets("API")
URL = "https://app.addressofwebsite.com/"
uri = "api/jobs/get"
timestamp = Format(Now(), "ddd, DD MMM YYYY HH:MM:SS") & " +0000"
empID = "715"
method = "get"
key = "abckey"
hash = ComputeHMACSHA256(key, method & uri & empID & timestamp)
signature = EncodeBase64(hash)
params = ("job_id=testid")
objHTTP.Open "GET", URL & uri, False
objHTTP.setRequestHeader "Content-Type", "application/json", "Accept", "application/json"
objHTTP.setRequestHeader "Authorization", "HEADER_A", signature,"HEADER_B", timestamp,"HEADER_C", empID
objHTTP.send params
strResult = objHTTP.responseText
Worksheets("API").Range("A10:A10") = strResult
End Sub
Current output from script:
{"status":false,"tag":"api\/jobs","error_code":0,"error_message":"Invalid Credentials"}
I know the issue exists around the 'objHTTP.send' and 'objHTTP.setRequestHeader' lines. I've tried them all on separate requestheader lines, without the 'authorisation' tag and lots of other random things. Is anyone able to point out where I'm failing here?
Many many thanks for your time!