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

Google Oauth2 (Google My Business API)



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:

    ufnGetList = (pgToken) =>
            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]
    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


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

Requires establishing a Twitter application in order to obtain a Consumer Key & Consumer Secret

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

 // Concatenates the Consumer Key & Consumer Secret and converts to base64
 authKey = "Basic " & Binary.ToText(Text.ToBinary("***my client id***:***my client secret***"),0),
 url = "",
 // Uses the Twitter POST oauth2/token method to obtain a bearer token
 GetJson = Web.Contents(url,
         Headers = [#"Authorization"=authKey,
              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),


Any help is appreciated.

Moderator v-yuezhe-msft

Re: Google Oauth2 (Google My Business API)


Please change the second code block to the following:


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

url = app_credentials & "&refresh_token=" & "xxxxx" & "&grant_type=refresh_token",
GetJson = Web.Contents("",
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)

There is a similar thread for your reference.


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.