Oauth2 and Excel-Rest library

Now that we have Rest to Excel library, and we also know how to use oAuth2 for authentication as described in OAUTH2 and VBA. we are ready to get data into Excel using Rest Queries that need oAuth2 validation. For illustration we’ll use Google Analytics Data. 

Set up scope

As described in Google Oauth2 VBA authentication, there is no need to store your credentials with each spreadsheet. Once you’ve stored them and allowed scopes once on a specific computer/user combination you shouldn’t need to do it again. That’s why I recommend a single, protected, workbook in which you store all your credentials and you just get it out from time to time to add new authentication scopes. Any other workbooks will access the registry to get required encrypted credentials. Assuming that you have done that for the “analytics” scope, here’s how to set up the rest library to get data from Google Analytics.

All these examples are implemented in the cDataSet.xlsm downloadable workbook. The examples for initially registering oAuth2 on your computer are there too.

The library entry

The Oauth2 dance is handled completely automatically by rest-excel library.  All you have to do is

• ensure you’ve registered the scope to you on this computer as described in OAUTH2 and VBA
• add these lines to the library entry, where the scope matches the value you’ve registered

.add "authType", erOAUTH2
            .add "authScope", "analytics"

Getting Analytics accounts.

This will retrieve all your analytics accounts, and populate a worksheet.

• create a library entry

 With .add("ua accounts")
            .add "restType", erSingleQuery
            .add "url", "https://www.googleapis.com/analytics/v3/management/accounts"
            .add "results", "items"
            .add "treeSearch", True
            .add "ignore", vbNullString
            .add "authType", erOAUTH2
            .add "authScope", "analytics"
        End With

• Add the column headers matching the data you want.

• Above is populated sheet after running this

Private Function oauthAnalyticsAccounts()
    With generalQuery("ua accounts", "ua accounts", vbNullString, , True)
        .tearDown
    End With
End Function

Getting Analytics web properties.

This will retrieve all your analytics web properties, and populate a worksheet.

• create a library entry

With .add("ua web properties")
            .add "restType", erSingleQuery
            .add "url", "https://www.googleapis.com/analytics/v3/management/accounts/"
            .add "results", "items"
            .add "treeSearch", True
            .add "ignore", vbNullString
            .add "authType", erOAUTH2
            .add "authScope", "analytics"
            .add "append", "/webproperties"
        End With

• Add the column headers matching the data you want.

• Above is populated sheet after running this

Private Function oauthAnalyticsWebProperties()
    Dim id As String
    ' id of web account
    id = "~all"
    With generalQuery("ua web properties", "ua web properties", id)
        .tearDown
    End With
End Function

Getting Analytics profiles

This will retrieve all your analytics profiles, and populate a worksheet.

• we can use the same library entry for this as for web properties
• Add the column headers matching the data you want.

• Above is populated sheet after running this

Private Function oauthAnalyticsProfiles()
    id = "~all"
    profiles = "~all"
    With generalQuery("ua profiles", "ua web properties", id, , , "/" & profiles & "/profiles")
        .tearDown
    End With
End Function

Getting analytics Data

This one is a little more complicated, since we want to make a dynamic sheet based on the data returned, as opposed to statically naming the columns. 

• Here’s the library entry

 With .add("ua data")
            .add "restType", erSingleQuery
            .add "url", "https://www.googleapis.com/analytics/v3/data/ga?ids=ga:"
            .add "results", ""
            .add "treeSearch", True
            .add "ignore", vbNullString
            .add "authType", erOAUTH2
            .add "authScope", "analytics"
        End With

• Here’s the result

• and here’s the code. Note that the profile ID is what’s required to get the data.

Private Function oauthAnalyticsData()
    Dim metrics As String, profiles As String, startDate As String, endDate As String, r As Range, _
        job As cJobject, joc As cJobject, jor As cJobject, dimensions As String
    metrics = "ga:pageviews,ga:visits,ga:visitors"
    dimensions = "ga:day"
    startDate = "2013-11-01"
    endDate = "2013-11-30"
    profiles = "34421202"
    With restQuery("ua data", "ua data", profiles, , , , , False, , , , , , , , , "&start-date=" & startDate & _
                    "&end-date=" & endDate & "&metrics=" & metrics & "&dimensions=" & dimensions)
        ' populating manually
        ' delete any existing data
        With .dset
            If Not .headingRow.where Is Nothing Then .headingRow.where.ClearContents
            If Not .where Is Nothing Then .where.ClearContents
            Set r = firstCell(.headingRow.where)
        End With
        
        ' create some column headings
        Set job = .jObject.childExists("columnHeaders")
        Debug.Assert Not job Is Nothing
        
        For Each joc In job.children
            r.Offset(, joc.childIndex - 1).value = joc.child("name").value
        Next joc
        
        ' now the data
        Set job = .jObject.childExists("rows")
        Debug.Assert Not job Is Nothing
        
        For Each jor In job.children
            For Each joc In jor.children
                r.Offset(jor.childIndex, joc.childIndex - 1).value = joc.value
            Next joc
        Next jor
        
        .tearDown
    End With
End Function

Continue reading about Rest to Excel Library here