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 initally registering oAuth2 on your computer are there too.

The library entry

The Oauth2 dance is handled completly 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

You can get me on Google plus, Twitter or this forum.

For help and more information join our forum,follow the blog or follow me on twitter .


Comments