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.
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 Oauth2 dance is handled completly automatically by rest-excel library. All you have to do is
This will retrieve all your analytics accounts, and populate a worksheet.
This will retrieve all your analytics web properties, and populate a worksheet.
This will retrieve all your analytics profiles, and populate a worksheet.
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.
You can get me on Google plus, Twitter or this forum.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > excel to json and back > Rest to Excel library >