Google oauth: Refresh tokens in Power Query

Question

I am trying to connect to youtube/google analytics using the oauth method and power bi. I have managed half way and I need some help. This is where I am at:

I obtain a authorization token manually using:

https://accounts.google.com/o/oauth2/auth?scope=https://www.googleapis.com/auth/yt-analytics.readonly&response_type=code&access_type=offline&redirect_uri=urn:ietf:wg:oauth:2.0:oob&approval_prompt=force&client_id={clientid}

Once I have that, I put it in my query and I am able to get both the access_token and the refresh_token:

Now, if I understand the documentation correctly, when the access_token expires after an hour, I can then use the refresh_token I got, to create a new access_token automatically,

Is that possible to do in Power Query? Has anybody tried?

I am completely clueless as to how to do that and I am not a developer so my skills are limited :(

Any help is appreciated!

Answer 1

Figuring out how to do OAuth flows automatically isn't easy for us on the Power BI developer team either ;)

PowerBI Desktop has a built-in data source connector GoogleAnalytics.Accounts() which handles OAuth tokens automatically.

(Google Analytics isn't available in Power Query today, sorry.)

For YouTube Analytics, there's a PowerBI UserVoice thread tracking demand for this feature. Show your support there!

Answer 2

We had a similar need to connect to the Analytics API directly, to circumvent shortcomings of the built-in connector. It was a little awkward to get the Web version of PowerBI to accept the auth endpoint as an "anonymous" source, but a reverse proxy can trick it by responding to the 'probe' GET requests with a 200 OK. Here's the main PowerQuery / M logic, broken up into functions:

GetAccessToken_GA

let
    Source = (optional nonce as text) as text => let
        // use `nonce` to force a fresh fetch

        someNonce = if nonce = null or nonce = ""
            then "nonce"
            else nonce,

        // Reverse proxy required to trick PowerBI Cloud into allowing its malformed "anonymous" requests to return `200 OK`.
        // We can skip this and connect directly to GA, but then the Web version will not be able to refresh.
        url = "https://obfuscated.herokuapp.com/oauth2/v4/token",

        GetJson = Web.Contents(url,
            [
                Headers = [
                    #"Content-Type"="application/x-www-form-urlencoded"
                ],
                Content = Text.ToBinary(
                    // "code=" & #"Google API - Auth Code"
                    // "&redirect_uri=urn:ietf:wg:oauth:2.0:oob"

                    "refresh_token=" & #"Google API - Refresh Token"
                    & "&client_id=" & #"Google API - Client ID"
                    & "&client_secret=" & #"Google API - Client Secret"
                    // & "&scope=https://www.googleapis.com/auth/analytics.readonly"
                    & "&grant_type=refresh_token"
                    & "&nonce=" & someNonce
                )
            ]
        ),
        FormatAsJson = Json.Document(GetJson),

        // Gets token from the Json response
        AccessToken = FormatAsJson[access_token],
        AccessTokenHeader = "Bearer " & AccessToken
    in
        AccessTokenHeader
in
    Source

---

returnAccessHeaders_GA

The nonce isn't used by the GA API, I've used it here to allow Power BI to cache API requests for at most one minute.

let
    returnAccessHeaders = () as text => let
        nonce = DateTime.ToText(DateTime.LocalNow(), "yyyyMMddhhmm"),
        AccessTokenHeader = GetAccessToken_GA(nonce)

    in
        AccessTokenHeader
in
    returnAccessHeaders

---

parseJsonResponse_GA

let
    fetcher = (jsonResponse as binary) as table => let
        FormatAsJsonQuery = Json.Document(jsonResponse),

        columnHeadersGA = FormatAsJsonQuery[columnHeaders],
        listRows = Record.FieldOrDefault(
            FormatAsJsonQuery,
            "rows",
            {List.Transform(columnHeadersGA, each null)}
            // a list of (lists of length exactly matching the # of columns) of null
        ),
        columnNames = List.Transform(columnHeadersGA, each Record.Field(_, "name")),

        matchTypes = (column as record) as list => let
            values = {
                { "STRING", type text },
                { "FLOAT", type number },
                { "INTEGER", Int64.Type },
                { "TIME", type number },
                { "PERCENT", type number },
                { column[dataType], type text } // default type
            },

            columnType = List.First(
                List.Select(
                    values,
                    each _{0} = column[dataType]
                )
            ){1},

            namedColumnType = { column[name], columnType }

        in namedColumnType,

        recordRows = List.Transform(
            listRows,
            each Record.FromList(_, columnNames)
        ),

        columnTypes = List.Transform(columnHeadersGA, each matchTypes(_)),
        rowsTable = Table.FromRecords(recordRows),
        typedRowsTable = Table.TransformColumnTypes(rowsTable, columnTypes)

    in typedRowsTable

in fetcher

---

fetchAndParseGA

The first parameter to Web.Contents() must be a string literal, or sadness ensues.

let
    AccessTokenHeader = returnAccessHeaders_GA(),

    fetchAndParseGA_fn = (url as text) as table => let
        JsonQuery = Web.Contents(
            "https://gapis-powerbi-revproxy.herokuapp.com/analytics",
                [
                    RelativePath = url,
                    Headers = [
                        #"Authorization" = AccessTokenHeader
                    ]
                ]
            ),
        Response = parseJsonResponse_GA(JsonQuery)
    in
        Response
in
    fetchAndParseGA_fn

---

queryUrlHelper

Allows us to us Power BI's 'Step Editor' UI to adjust query parameters, with automatic URL encoding.

let
    safeString = (s as nullable text) as text => let
        result = if s = null
            then ""
            else s
    in
        result,

    uriEncode = (s as nullable text) as text => let
        result = Uri.EscapeDataString(safeString(s))
    in
        result,

    optionalParam = (name as text, s as nullable text) => let
        result = if s = null or s = ""
            then ""
            else "&" & name & "=" & uriEncode(s)
    in
        result,

    queryUrlHelper = (
        gaID as text,
        startDate as text,
        endDate as text,
        metrics as text,
        dimensions as nullable text,
        sort as nullable text,
        filters as nullable text,
        segment as nullable text,
        otherParameters as nullable text
    ) as text => let
        result = "/v3/data/ga?ids=" & uriEncode(gaID)
            & "&start-date=" & uriEncode(startDate)
            & "&end-date=" & uriEncode(endDate)
            & "&metrics=" & uriEncode(metrics)
            & optionalParam("dimensions", dimensions)
            & optionalParam("sort", sort)
            & optionalParam("filters", filters)
            & optionalParam("segment", segment)
            & safeString(otherParameters)
    in
        result,

    Example = queryUrlHelper(
        "ga:59361446", // gaID
        "MONTHSTART", // startDate
        "MONTHEND", // endDate
        "ga:sessions,ga:pageviews", // metrics
        "ga:userGender", // dimensions
        "-ga:sessions", // sort
        null, // filters
        "gaid::BD_Im9YKTJeO9xDxV4w6Kw", // segment
        null // otherParameters (must be manually url-encoded, and start with "&")
    )
in
    queryUrlHelper

---

getLinkForQueryExplorer

Just a convenience, to open a query in the Query Explorer.

let
    getLinkForQueryExplorer = (querySuffixUrl as text) as text => let
        // querySuffixUrl should start like `/v3/data/ga?ids=ga:132248814&...`
        link = Text.Replace(
            querySuffixUrl,
            "/v3/data/ga",
            "https://ga-dev-tools.appspot.com/query-explorer/"
        )
    in
        link
in
    getLinkForQueryExplorer

---

Identity

Returns its input unchanged; this function's main use is to allow another way to update query variables via the convenient 'Step Editor' UI.

let
    Identity = (x as any) as any => let 
        x = x
    in
        x
in
    Identity

---

getMonthBoundary

// Get a list of the start and end dates of the relative month, as ISO 8601 formatted dates.
//
// The end date of the current month is considered to be the current date.
//
// E.g.:
// ```
// {
//     "2016-09-01",
//     "2016-09-31"
// }
// ```
//
// Source: <https://gist.github.com/r-k-b/db1eb0e00364cb592e1d8674bb03cb5c>

let
    GetMonthDates = (monthOffset as number) as list => let
        now = DateTime.LocalNow(),
        otherMonth = Date.AddMonths(now, monthOffset),
        month1Start = Date.StartOfMonth(otherMonth),
        month1End = Date.AddDays(Date.EndOfMonth(otherMonth), -1),

        dates = {
            month1Start,
            month1End
        },

        result = List.Transform(
            dates,
            each DateTime.ToText(_, "yyyy-MM-dd")
        )
    in
        result
in
    GetMonthDates

---

replaceUrlDates

// 
// E.g., on 2016-10-19 this is the result:
// ```
// replaceDates(-1, "/foo?s=MONTHSTART&e=MONTHEND") === "/foo?s=2016-09-01&e=2016-09-28"
// ```

let
    replaceDates = (monthOffset as number, rawUrl as text) as text => let
        boundaryList = getMonthBoundary(monthOffset),

        stage01 = Text.Replace(
            rawUrl,
            "MONTHSTART",
            boundaryList{0}
        ),

        stage02 = Text.Replace(
            stage01,
            "MONTHEND",
            boundaryList{1}
        ),

        stage03 = replaceViewNames(stage02)
    in
        stage03

in
    replaceDates

---

An example query

let
    QueryBase = queryUrlHelper("All Web Site Data", "MONTHSTART", "today", "ga:sessions,ga:pageviews,ga:pageviewsPerSession", "ga:deviceCategory,ga:yearMonth", null, null, null, null),
    MonthOffset = Identity(#"Months Back to Query"),
    QueryURL = replaceUrlDates(MonthOffset, QueryBase),
    CopyableLinkToQueryExplorer = getLinkForQueryExplorer(QueryURL),
    Source = fetchAndParseGA(QueryURL)
in
    Source

---

As a bonus, this can generalize to any OAuthV2 data source, and also requires minimal adjustment to work with the powerful V4 API.