How to convert a Postman authorization POST to a VBA JSON POST

Question

I am using Postman to send in an authorization request to google to retrieve an access token. When I enter all the required information on the Authorization tab and click on the Get New Access Token button, I get a response back from google with an access token. This proves that the data I am sending in is valid and my project is set up properly on google. Now, I need to convert this request to a VBA request.

When I view the Postman log I can see the data that Postman sends to Google. This is what I see:

And this is the response that google sends back:

This is the VBA code that I am using to try to replicate the Postman call (I have the redirect url set to my local machine which is in the list of approved redirect url's on my google project):

Dim sClient_ID As String
Dim sClient_Secret As String
Dim sGrant_Type As String
Dim sCode As String
Dim sRedirect_URI As String
sClient_ID = "{the client id that google provided to me}"
sClient_Secret = "{the client secret that google provided to me}"
sGrant_Type = "authorization_code"
sCode = "4/0AWtgzh75ZFps55t9vPx-gm_rm8W_uyWQbZwBF1qTLthpUuPjaAXBr9iywT-RweVvagcGPg"
sRedirect_URI = "https://localhost:8080"

' build the json string which will be sent to get the google access token

Dim a As New Scripting.Dictionary

a.Add "grant_type", sGrant_Type
a.Add "code", sCode
a.Add "redirect_uri", sRedirect_URI
a.Add "client_id", sClient_ID
a.Add "client_secret", sClient_Secret

Dim Json_Get_Access_Token As String
Json_Get_Access_Token = JsonConverter.ConvertToJson(a, Whitespace:=" ")

' send the json string via POST
Set httpCall = CreateObject("MSXML2.ServerXMLHTTP")
Dim sTokenURL As String
sTokenURL = "https://oauth2.googleapis.com/token"
httpCall.Open "POST", sTokenURL, False
httpCall.setRequestHeader "Content-Type", "application/json;charset=UTF-8"
httpCall.Send Json_Get_Access_Token
Dim sReturnToken As String
sReturnToken = httpCall.responseText

When I run the code and look at the value is sReturnToken, I see this:

{
  "error": "invalid_grant",
  "error_description": "Bad Request"
}

Any idea what I'm not setting up properly? Thank you.

Answer 1

EDIT:

You can actually send the requests as json or encoded URL

Send request as JSON

Set Content-Type to application/json and convert the a dictinary to json

Dim Json_Get_Access_Token As String
Json_Get_Access_Token = JsonConverter.ConvertToJson(a, Whitespace:=" ")
'...
httpCall.Send Json_Get_Access_Token 
'...

Send request as Encoded URL

Set Content-Type to application/x-www-form-urlencoded and concatenate the values of the a dictionary like this: data=value&data2=value2, each value must be encoded with WorksheetFunction.EncodeURL function that only works in Excel 2013+ if you have an older version or non-Excel app then use this function

Dim strQueryString as String
For Each vKey In a.Keys()
   strQueryString = strQueryString & vKey & "=" & WorksheetFunction.EncodeURL(a(vKey)) & "&"
Next
' Remove last &
strQueryString = Left(strQueryString, Len(strQueryString) - 1) 
'...
httpCall.Send strQueryString 
'...

Here more info

Tested output:

{
  "access_token": "ya29.xxx",
  "expires_in": 3599,
  "refresh_token": "1//xxx",
  "scope": "https://www.googleapis.com/auth/calendar.events.readonly",
  "token_type": "Bearer"
}