PowerQuery to access Google Analytics API Data

[nicksoft]

We are tryign to build PowerQuery to access Google Analytics API Data using below syntax. 

We are keep getting error that client unauthorized.

we try to create the Cliendid information using google cloud platform and got refresh toekn using Google developer OAuthplayground console. 

We may not be gettting these 3 parameters incorrect. there might be a better way to get authentication using the powerquery.

If any one can share what we are doing wrong, that would help. We

let
clientId = "client_id=fdgdfsgdsgdfgdfgdfgdfsgdfgdsfgdsfg.apps.googleusercontent.com",
addSecret = clientId & "&client_secret=ghdfghfg76tr8ghfjnmnt7rut7",
addRefreshToken = addSecret & "&refresh_token=1//04SFLj9n4wHgaCgYIARAAGAQSNwF-L9Ir5QOoHwSzGR3Ics2SAeb58gTnqxRDYwO87zTyZz3TLDbmpg1BfbQ0PEhS7GCi8XsLW-s",
url = addRefreshToken & "&grant_type=refresh_token",
GetJson = Web.Contents("https://accounts.google.com/o/oauth2/token",
[
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary(url)
]
),
FormatAsJson = Json.Document(GetJson),
access_token = FormatAsJson[access_token]
in
access_token

[QueryWhiz]

Hi @nicksoft were you able to find a solution? I used the GA native connector but had some issues with my data sometimes. As a workaround, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the GA connector in the data sources list:

After that, just grant access to your GA account using your credentials, then on preview and destination page you will see a preview of your GA fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.

[v-yingjl]

Hi @nicksoft ,

Since Google Analytics uses OAuth2 authentication method, please try to refer this document about OAuth autentication in Power Query:

Implementing an OAuth Flow 

Simliar Blog: HOW TO GET GOOGLE ANALYTICS DATA IN POWER QUERY 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nicksoft]

thanks for the reply @v-yingjl . 

We did tried that article, but when we tried below URL using our clientid and codes, it do not work.

Any idea?

https://accounts.google.com/client_id=YourClientID
&response_type=code
&redirect_uri=urn%3aietf%3awg%3aoauth%3a2.0%3aoob
&approval_prompt=force
&scope=https%3a%2f%2fwww.googleapis.com%2fauth%2fanalytics.readonly
&access_type=offline

[nicksoft]

Any one Have tried this above route to acceess Google Analytics API data using PowerQuery instead of Google Analytics connector?

[quincy-vanee]

Hi I'm having the same issue. Has anyone figured this out yet?