topic Re: PowerQuery to access Google Analytics API Data in Power Query

PowerQuery to access Google Analytics API Data

Anonymous — Fri, 19 Nov 2021 22:50:37 GMT

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

Re: PowerQuery to access Google Analytics API Data

Anonymous — Fri, 19 Nov 2021 23:37:29 GMT

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

Re: PowerQuery to access Google Analytics API Data

Anonymous — Mon, 22 Nov 2021 13:03:40 GMT

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

Re: PowerQuery to access Google Analytics API Data

v-yingjl — Tue, 23 Nov 2021 07:53:03 GMT

Hi Anonymous ,

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.

Re: PowerQuery to access Google Analytics API Data

Anonymous — Thu, 25 Nov 2021 13:57:14 GMT

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

Re: PowerQuery to access Google Analytics API Data

QueryWhiz — Thu, 04 Jan 2024 20:43:15 GMT

Hi Anonymous 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.