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
Public Sub testWorkBookImport() Dim key As String key = "0At2ExLh4POiZdE43aGo4TENEWlVOeFBkRlVPcEhIbnc" If Not importGoogleWorkbook(key, , , True) Then MsgBox ("failed to import workbook at " & key) End If End Sub
The arguments follow and should be fairly self-explanatory. Note that you can use the same function to get public sheets too. Just set oauthNeeded to false.
Public Function importGoogleWorkbook(key As String, _ Optional deleteAllSheetsFirst As Boolean = False, _ Optional replaceConflictingSheets = True, _ Optional oauthNeeded As Boolean = False, _ Optional headers As Boolean = False) As Boolean
First time in
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.
With getGoogled("viz", , "xxxxx.apps.googleusercontent.com", "yoursecret") Debug.Print .authHeader .tearDown End With
Alternatively, if you have ever previously authenticated any scope, you can clone existing credentials without having them in any workbook like this – let’s say you’ve previously authenticated to ‘drive’ and want to now register ‘viz’
With getGoogled("viz", , , , , "drive") Debug.Print .authHeader .tearDown End With
You can use the same functions to get public data. Here’s an example – the only difference is the argument, oauthNeeded defaults to false.
Public Sub testPublicWorkBookImport() Dim key As String key = "0AodxbO8eOvBZdE93VnNiaVNRdjdxMXJNMWJlNVRMWGc" If Not importGoogleWorkbook(key) Then MsgBox ("failed to import workbook at " & key) End If End Sub