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 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

 

Getting Analytics accounts.

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

  • create a library entry
  • Add the column headers matching the data you want.
  • Above is populated sheet after running this

Getting Analytics web properties.

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

  • create a library entry
  • Add the column headers matching the data you want.
  • Above is populated sheet after running this

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

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
  • Here’s the result
  • and here’s the code. Note that the profile ID is what’s required to get the data.

Continue reading about Rest to Excel Library here