Google docs import with oauth2

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.

Page Content hide

6 Share with your network

Approach

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

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

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.

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