Google Oauth2 VBA authentication

[Tom Doan]

Hi Bruce,

First, I want to say nice site and forum! It is literally overflowing with great content, but I must say it's also a bit overwhelming for someone who is not at an Excel MVP-caliber level. I arrived at your site for the first time through this article:

http://ramblings.mcpher.com/Home/excelquirks/googleoauth2

All I want to do is try and get some basic metrics from GA into Excel 2010 (32-bit Win7). I followed your instructions closely and succeeded in getting the encrypted OAuth2 token saved in the Registry, but this is where the article ended. Is there a second part of the article that explains how one would retrieve this token when making an API query?

If you have a sample spreadsheet that retrieves some data from Google Analytics, could you please provide a link? I would love to be able to study the code and learn from there.

Thanks,

Tom

[Bruce Mcpherson]

Yes there actually an analytics examples - the entire oauth2 process is handled automatically...  if you've managed to set up a registry token already, you're almost there...

http://ramblings.mcpher.com/Home/excelquirks/json/rest/oauth2rest

bruce

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Tom Doan]

Hi Bruce,

Thanks for the link -- I will definitely check it out. Before seeing your reply I managed to get the token and get a response back by importing the required modules into my spreadsheet one-by-one from cDataSet until getGoogled() worked :-).

The bad news is that the response is not what I was expecting:

{"error":{"errors":[{"domain":"global","reason":"insufficientPermissions","message":"User does not have sufficient permissions for this profile."}],"code":403,"message":"User does not have sufficient permissions for this profile."}}

I did add xxxxxx...@developer.gserviceaccount.com as a user with all permissions enabled in my Google Analytics admin panel, so not sure why it's giving me this error (this email is also included in the Google Developers Console).

Could it be that I'm using the wrong profile ID? The number I'm using in ga:xxxxxx is my Google Analytics account ID, but I'm not sure if that is correct.

Tom

[Tom Doan]

Hi, it's me again. Finally solved it! For anyone who's stumped about how to get their profile ID, the answer can be found here:

http://www.paessler.com/knowledgebase/en/topic/35363-where-do-i-find-my-google-analytics-profile-id

Bruce, now I will read the article you sent me -- can't wait to see what automation you've cooked up :-).

Cheers,

Tom

[Bruce Mcpherson]

you can get all the required modules automatically into a workbook like this

http://ramblings.mcpher.com/Home/excelquirks/gitthat

the excel library entry indicates that oauth2 is required.. for example

        With .add("ua web properties")

            .add "restType", erSingleQuery

            .add "url", "https://www.googleapis.com/analytics/v3/management/accounts/"

            .add "results", "items"

            .add "treeSearch", True

            .add "ignore", vbNullString

            .add "authType", erOAUTH2

            .add "authScope", "analytics"

            .add "append", "/webproperties"

        End With



The crest class does all the token stuff, getting whatever you have in the registry and adding analytics scope, and will refresh if necessary. 


        If job.value = erOAUTH2 Then


            ' need to authorize and get token
            Set oa = getGoogled(cj.child("authScope").value)


later on the cbrowser class passes the token to analytics API


    ' this would be if we were doing an oauth2

    If (authHeader <> vbNullString) Then

        ohttp.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"

        ohttp.SetRequestHeader "Authorization", authHeader

    End If



bruce

On 2 January 2014 15:19, Tom Doan <thd...@gmail.com> wrote:

Hi Bruce,

Thanks for the link -- I will definitely check it out. Before seeing your reply I managed to get the token and get a response back by importing the required modules into my spreadsheet one-by-one from cDataSet until getGoogled() worked :-).

The bad news is that the response is not what I was expecting:

I did add xxxxxx...@developer.gserviceaccount.com as a user with all permissions enabled in my Google Analytics admin panel, so not sure why it's giving me this error (this email is also included in the Google Developers Console).

Could it be that I'm using the wrong profile ID? The number I'm using in ga:xxxxxx is my Google Analytics account ID, but I'm not sure if that is correct.

Tom

[Bruce Mcpherson]

btw.. the example  http://ramblings.mcpher.com/Home/excelquirks/json/rest/oauth2rest shows how to populate a sheet with all profile IDS allocated to your account.

bruce

[Tom Doan]

Thanks a lot, Bruce. This has been a big learning experience.

Cheers,

Tom