cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
highland18 Frequent Visitor
Frequent Visitor

Google Oauth2 (Google My Business API)

Hi,

 

I've done a lot of searching on this the past couple of days but keep hitting a brick wall. Basically I'm looking to use the Google My Business API within Power BI.

 

Now I manage to connect to the API using the access token in the uri, but obviously this expires in 1 hours time. The code below i'm using is:

let    
    ufnGetList = (pgToken) =>
        let 
            result = Json.Document(
                        Web.Contents("***My google business uri here with access token and page token parameter" & pgToken)
                        ),
            nextPageToken = try result[nextPageToken] otherwise null,
            reviews = result[reviews],
            record = [reviews = reviews, nextPageToken = nextPageToken]
        in
            record,
 
    resultSet = List.Generate(
                    () => ufnGetList(""),
                    each _[nextPageToken] <> null,
                    each ufnGetList(_[nextPageToken]),
                    each [nextPageToken = _[nextPageToken], reviews = _[reviews]]
                    ),
 
    lastPageToken = List.Last(Table.FromRecords(resultSet)[nextPageToken]),
    lastResultSet = ufnGetList(lastPageToken)[reviews],
    firstResultSet = List.First(Table.FromRecords(resultSet)[reviews]),
    combineList = List.Combine({ firstResultSet, lastResultSet}),

The above gets the dataset and will loop over the pages to return all results.

 

My issue, as mentioned comes with the access token.

 

I know Google uses Oauth 2 and provides a refresh token but can anyone help me with how I would implement this in Power Query?

 

I've tried multiple post methods but keep getting hit with a (400) Bad Request error from https://accounts.google.com/o/oauth2/token

 

I've tried to follow the Twitter guide thats out there for using an Oauth 2 API but the code below fails;

 

/*
This M script gets an bearer token and performs a tweet search from the Twitter REST API
https://dev.twitter.com/oauth/application-only

Requires establishing a Twitter application in order to obtain a Consumer Key & Consumer Secret
https://apps.twitter.com/

IMPORTANT - The Consumer Key and Consumer secret should be treated as passwords and not distributed
*/

let
 // Concatenates the Consumer Key & Consumer Secret and converts to base64
 authKey = "Basic " & Binary.ToText(Text.ToBinary("***my client id***:***my client secret***"),0),
 url = " https://accounts.google.com/o/oauth2/token",
 // Uses the Twitter POST oauth2/token method to obtain a bearer token
 GetJson = Web.Contents(url,
     [
         Headers = [#"Authorization"=authKey,
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
              Content = Text.ToBinary("grant_type=authorization") 
     ]
 ),
 FormatAsJson = Json.Document(GetJson),
 // Gets token from the Json response
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "bearer " & AccessToken,
 // Uses the Twitter GET search/tweets method using the bearer token from the previous POST oauth2/token method
 GetJsonQuery = Web.Contents("***url to access google my business reviews***",
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
 FormatAsJsonQuery = Json.Document(GetJsonQuery),
in
FormatAsJsonQuery

 

Any help is appreciated.

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Google Oauth2 (Google My Business API)

@highland18,

Please change the second code block to the following:

let

app_credentials ="client_id=XXXXXXm&client_secret=XXXXXX",

url = app_credentials & "&refresh_token=" & "xxxxx" & "&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)
]
),
AccessToken = Json.Document(GetJson)[access_token],
AccessTokenHeader = "bearer " & AccessToken,
 GetJsonQuery = Web.Contents("***url to access google my business reviews***",
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
 FormatAsJsonQuery = Json.Document(GetJsonQuery)
in
FormatAsJsonQuery


There is a similar thread for your reference.
https://community.powerbi.com/t5/Service/Refresh-API-GOOGLE-ANALYTICS/td-p/270807

Regards,
Lydia

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