401 error when querying GA Data API through Excel/VBA - any ideas why?!

[marks]

Hi there,

I'm setting up a custom Excel spreadsheet with some VBA to pull data in from GA via the API. I've set up OAuth 2.0 authentication and I can obtain the access/refresh codes easily enough. 

The problem comes when I try to use them to query the GA Data API. I'm using the following VBA code to query the API (all variables are defined at the start of the module so don't appear here):

Sub GetGAData()

Call UseRefreshToken 'update the access token using the UseRefreshToken Sub Returns access token to 'strAccessToken'

strGAQueryUrl = "https://www.googleapis.com/analytics/v3/data/ga"

strGAQueryText = Worksheets("Sheet1").Range("B6").Value 'get the query definition from a cell in the spreadsheet

strGAQueryUrl = strGAQueryUrl & strGAQueryText 'append the query definition to the basic url

Set objHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

objHttpReq.Open "GET", strGAQueryUrl, False 'GET request to the GA API with the url defined above

objHttpReq.setRequestHeader "Authorization", "OAuth " & strAccessToken 'send access token using (what I think is) the method shown in the OAuth 2.0 Playground

objHttpReq.Send

strGAQueryResults = objHttpReq.responsetext

Worksheets("Sheet1").Range("B8").Value = strGAQueryResults 'Paste query response into cell B8 for debugging

End Sub

I'm getting a '401 Unauthorized' response every time with the following in the JSON response:

"{"error":{"errors":[{"domain":"global","reason":"authError","message":"Invalid Credentials","locationType":"header","location":"Authorization"}],"code":401,"message":"Invalid Credentials"}}"

So it appears that its something to do with my use of the access token but I'm stumped as to what I'm doing wrong. I've also tried the setRequestHeader using "Bearer " instead of "OAuth " with no joy.

I've also double and triple checked I'm extracting the right string for the Access Token and it looks fine with all characters present and no leading or trailing spaces.

Does anyone have any thoughts?!

Thanks in advance.

[Ferdi S.]

Hi Marks, have you found a solution for this issue? I have been working with the same v2.3 macro in the past and even though I understand the old code, I don't know enough about the technical side to update to v3.0 myself. Is there any way I could ask you to share your solution?

Thanks,

Ferdi

[Nick]

Hi,

I get this in the OAuth 2 playground:

> Authorization: OAuth ya29.Axxxxxxxxx

But in your code there is no : or space after Authorization

Are you sure you are sending exactly the same headers?

-Nick

[airikbs]

Hi,

I think you need to put 

https://www.googleapis.com/auth/analytics.readonly

read https://developers.google.com/accounts/docs/OAuth2WebServer on Forming the URL. 

and put the above url on the scope parameter. it should solve the invalid credentials issue. 

[salvador....@gmail.com]

Hi Mark,

Would you pleas your code UseRefreshToken with us?

Thank you in advance!!!!