Google Oauth2 (Google My Business API)

[highland18]

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.

[v-yuezhe-msft]

@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