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.

Example

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

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.

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'

With getGoogled("viz", , , , , "drive")

Debug.Print .authHeader

.tearDown

End With

Public sheets

You can use the same functions to get public data. Here's an example - the only difference is the argument , oauthNeeded defaults to false.