Send OAuth Access Code to WebService through macros

AthmanArumugam · Dec 10, 2018

Hi,

I would like to know if anyone has passed in OAuth acces code to a webservice in excel macros. The below code works fine for the webservice when the access token is not passed.
Right now the development team has updated the service to include access token for client credentials with cleitn id, client security and scope.

Coudl you kindly guide me how to update the below module to access the webservice using access code.

Code:

Sub SubmitTokenisationRequest(strPaylodValue)
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
     url = "https://application/test/token"
    objHTTP.Open "POST", url, False
    objHTTP.setRequestHeader "Content-Type", "application/xml"
    objHTTP.setRequestHeader "Accept", "application/xml"
    objHTTP.setProxy 2, "proxy.autozone.com:80", ""
    objHTTP.send (strPaylodValue)
    strResponseStatus = objHTTP.Status
    strResponseText = objHTTP.responseText
    strResponseText = CStr(strResponseText)
   
End Sub

Chihiro · Dec 10, 2018

It really depends.

What does REST API documentation for the site say?

Typically you'd include token in url string. But exact string composition will depend on the site.

Ex: For Google API, you use issued token along with query parameters needed to get json response.

Code:

Function GetJson(start As String, dest As String)
    Const MyKey As String = "Key"
    BaseUrl = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="
    Url2 = "&destinations="
    Url3 = "&language=en&key=" & MyKey
    Url = BaseUrl & Replace(start, " ", "+") & Url2 & Replace(dest, " ", "+") & Url3
    With CreateObject("MSXML2.ServerXMLHTTP")
        .Open "GET", Url, False
        .setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
        .Send
        GetJson = .responseText
    End With
End Function

AthmanArumugam · Dec 11, 2018

Thanks Chihiro for your response. Still my issue is not resolved. I have a question, when I generate the access token in postman and send it to the service I can see that that the access token is sent to the header section of the service in postman.

Access code is sent as "Authorization : Bearer [access_code]". When I send the generated access_code in macro, the service does not respond correctly. I used a EncodeBase64 function, still I don't get the response to work. Could you please guide me.

Code:

Sub SubmitTokenisationRequest(strPayloadValue)
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    access_token = "fb3482a5-1b72-44e2-be20-4b49c4ca9da9"
    URL = "https://application/test/token"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    objHTTP.setRequestHeader "Accept", "application/xml"
    x = EncodeBase64("fb3482a5-0b72-44e2-be20-4b49c4ca9da9")
    objHTTP.setRequestHeader "Authorization", "Bearer " & x
    objHTTP.setProxy 2, "proxy.autozone.com:80", ""
    'MsgBox strPaylodValue
    objHTTP.send (strPaylodValue)
    strResponseStatus = objHTTP.Status
    strResponseText = objHTTP.ResponseText
    strResponseText = CStr(strResponseText)
   
End Sub
Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As MSXML2.DOMDocument60
  Dim objNode As MSXML2.IXMLDOMElement

  Set objXML = New MSXML2.DOMDocument60
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text

  Set objNode = Nothing
  Set objXML = Nothing
End Function

Chihiro · Dec 12, 2018

Typically there is no need to Encode header.

You simply have to send the correct string.

This part...

Code:

objHTTP.setRequestHeader "Authorization", "Bearer " & x

Looks off.

Try using...

Code:

objHTTP.setRequestHeader "Authorization:Bearer " & x

Or...

Code:

objHTTP.setRequestHeader "Authorization:", "Bearer " & x

If this doesn't work, try consulting documentation to find the appropriate string that should be sent.

AthmanArumugam · Dec 12, 2018

Hi,

I tried both but it is not working. When I contacted the documentation for this I was informed that the developers access the service by sending in Application : Bearer [access code].

When I run the below code, I am getting Unsupported Content TYpe.

Code:

Sub SubmitTokenisationRequest(strPaylodValue)
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = "www.goolg.come"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "Content-Type", "application/xml"
    objHTTP.setRequestHeader "Accept", "application/xml"
     x = "2d0e5164-d864-4c46-86d7-da7d4c12846a"
    objHTTP.setRequestHeader "Authorization", "Bearer " & x
    objHTTP.setProxy 2, "proxy.autozone.com:80", ""
    'MsgBox strPaylodValue
    objHTTP.send (strPaylodValue)
    strResponseStatus = objHTTP.Status
    strResponseText = objHTTP.ResponseText
    strResponseText = CStr(strResponseText)
  
End Sub

Chihiro · Dec 12, 2018

You'd need ":" in the string like I've shown.

What is the exact response you get from the request?

Chihiro · Dec 12, 2018

From the response you posted in conversation...

Code:

{
"errors": [{
"id": "ab5053a0-27fd-495b-8507-d0442951aaad",
"code": "415",
"status": "500",
"title": "Unsupported Content-Type.",
"detail": "",
"links": [

]
}]
}

Issue is even before your token. As you are sending unsupported content type.

Code:

    objHTTP.setRequestHeader "Content-Type", "application/xml"
    objHTTP.setRequestHeader "Accept", "application/xml"

Check their documentation on appropriate header and Content-Type accepted.

AthmanArumugam · Dec 12, 2018

Hi Chihiro,

I checked with the development team and they are using Content-Type as application/xml . When I use the same in postman it is working.

Not sure why I ma getting this error today.

Also when I use "Authorization: Bearer", excel macro itself threw an error.

The below error I get when the line

x = "6c850a6a-c77e-4017-bf98-93eb4ece2130"
objHTTP.setRequestHeader "Authorization:", "Bearer " & x

is executed.

Chihiro · Dec 12, 2018

On which line do you get the error? That error usually doesn't occur at .setRequestHeader line.

AthmanArumugam · Dec 12, 2018

Hi Chihiro,

I get the error in line objHTTP.setRequestHeader "Authorization:", "Bearer " & x

Chihiro · Dec 12, 2018

Then check with the vendor.

Each API has slightly different settings and syntax.

I've used above without issue for few APIs (ex: Flurry API, Yammer API etc), though usually request is made using "application/x-www-form-urlencoded".

Sorry, but can't really help you without documentation and info on site specific requirements.

Chihiro · Dec 12, 2018

Ex: Code I use to query Flurry API.

Code:

Sub Demo()
Dim oPath As String: oPath = "C:\test\flurry.json"
Dim intFF As Integer: intFF = FreeFile()
Dim Url As String, qStr As String, mToken As String
Url = "https://api-metrics.flurry.com/public/v1/data/"
qStr = "appUsage/month/app/appVersion/country/region?metrics=sessions,activeDevices,newDevices,timeSpent&dateTime=2018-02/2018-03"
mToken = "tokenstring"
With CreateObject("MSXML2.xmlhttp")
    .Open "GET", Url & qStr, False
    .setRequestHeader "Authorization:", "Bearer " & mToken
    .Send
    Open oPath For Output As #intFF
    Print #intFF, .responseText
    Close #intFF
End With
End Sub

AthmanArumugam · Dec 12, 2018

I Use WinHttp.WinHttpRequest.5.1 instead of MSXML2.xmlhttp. Is that the reason for the issue.

Chihiro · Dec 12, 2018

Could be. But why use WinHttp for API request?

But you should still be able to send token in the header.

Edit: See link for an example.
https://stackoverflow.com/questions/44218694/vba-api-post-with-x-auth-token

AthmanArumugam · Dec 12, 2018

Hi,

Sorry for bothering you. But when I use the MSXML2.xmlhttp object, I get the below error in the .send command.

Code:

    Set objHTTP = CreateObject("MSXML2.xmlhttp")
    URL = "https://test/token"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "Content-Type", "application/xml"
    objHTTP.setRequestHeader "Accept", "application/xml"
    objHTTP.setRequestHeader "Authorization:", "Bearer 7920190f-249a-4163-9cb2-e54925179c05" - Did not get error here
      
    objHTTP.send (strPaylodValue) -- Get error here
    strResponseStatus = objHTTP.Status
    strResponseText = objHTTP.ResponseText
    strResponseText = CStr(strResponseText)

Chihiro · Dec 12, 2018

You should just use...

Code:

objHTTP.send strPaylodValue

No "()" needed.

AthmanArumugam · Dec 12, 2018

Hi Chihiro - I LOVE YOU. Issue resolved.

When I used MSXML2.ServerXMLHTTP.6.0 object instead of MSXML2.xmlhttp, issue got resolved.

Code:

    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    URL = "https:///commercial/payments-processing/v1/test/token"
    objHttp.Open "POST", URL, False
    objHttp.setRequestHeader "Content-Type", "application/xml"
    objHttp.setRequestHeader "Accept", "application/xml"
    objHttp.setRequestHeader "Authorization:", "Bearer [Access Token]"
    objHttp.send (strPaylodValue)
    strResponseStatus = objHttp.Status
    strResponseText = objHttp.ResponseText
    strResponseText = CStr(strResponseText)

Send OAuth Access Code to WebService through macros

AthmanArumugam

Member

Chihiro

Excel Ninja

AthmanArumugam

Member

Chihiro

Excel Ninja

AthmanArumugam

Member

Chihiro

Excel Ninja

Chihiro

Excel Ninja

AthmanArumugam

Member

Chihiro

Excel Ninja

AthmanArumugam

Member

Chihiro

Excel Ninja

Chihiro

Excel Ninja

AthmanArumugam

Member

Chihiro

Excel Ninja

AthmanArumugam

Member

Chihiro

Excel Ninja

AthmanArumugam

Member