NOTE: with the new Google Sheets, this method has been largely deprecated. There is a complete revamp of this here Get Data From Google Docs. You can also check out the Sheets apps from Apps built with data abstraction, which can both read and write from Excel to Sheets.
In Google Oauth2 VBA authentication I covered how to use oauth2 from VBA. Elsewhere on this site you'll see other ways to get data out of google Spreadsheets such as Promises in VBA and Google Visualization. Neither of those used oAuth2 so they were limited to public facing spreadsheets.
Now here is how to get private data into Excel. You'll find these examples and libraries in googleImport.xlsm in the Downloads section, or you can get the code via gistthat - you'll need the crest manifest. It's also in the combination workbook, cDataSet.xlsm which has pretty much everything on this site in it.
As in Promises in VBA, we are going to import an entire workbook into Excel. This is a 2 step process, the first examines the schema of the spreadsheet, and the second imports all the worksheets using googlewire format used by the General Google visualization tool. All this is authenticated by Google Oauth2 VBA authentication
Let's say that we want to pick up an entire workbook from Google Spreadsheets that is private only to you. The variable code is as follows - you just need to supply the workbook key
The arguments are follows and should be fairly self-explanatory. Note that you can use the same function to get public sheets too. Just set oauthNeeded to false.
As described in Google Oauth2 VBA authentication, you need to provide your credentials the very first time. You can use any of the methods described there. I recommend that you do not store your credentials in this workbook, but keep a single workbook to authorize any scopes you need. That will mean you do not need to change any code here, nor store any credentials here.
A one off function to provide your credentials for the first time would look like this, and you can delete it once it has run.
Alternatively, if you have ever previously authenticated any scope, you can clone existing credentials without having them in any workbook like this - lets say you've previously authenticated to 'drive' and want to now register 'viz'
You can use the same functions to get public data. Here's an example - the only difference is the argument , oauthNeeded defaults to false.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Google Oauth2 VBA authentication >