Accessing Google Sheets directly from Excel

Here’s a VBA app that uses Database abstraction with google apps script via the DataHandler library. You need to download the latest version of the cDataSet.xlsm workbook from Downloads It’s made up of these components

DataHandler REST API which uses the DataHandler and  the DriverSheet and Database caching to access Google Sheets.

The VBA JSON and cDataset libraries in the latest version of cDataSet.xlsm

You would normally have created your own copy of DataHandler REST API so you could access your own files, but for the purposes of this example, since we are still in beta, I’ve shared a public sheet for you to use for testing. Since others will have access to it, do not put any sensitive or private data there, and I will be clearing it out regularly.

Once you have played around with this, you can set up your own environment fairly easily, as described in DataHandler REST API

I have implemented Google Oauth2 VBA authentication which will allow you to protect your private sheets. Check back here from time to time or follow me on G+ to see when I publish a how to for that.

Let’s get into the code – it’s self explanatory, but How to use cJobject and How to use cDataSet will explain the VBA libraries in use here.

Using this you can read, write and query Google Sheets directly from your VBA code.  This example does all of the following

  • Deletes everything on a Google sheet (creating it first if necessary)
  • Copies an entire excel worksheet to a google sheet
  • Does a query on a Google sheet and returns the matching records
  • Deletes rows on a Google sheet that match a query
  • Counts number of rows that match a particular query
  • Retrieves and sorts a subset of data from a Google sheet and writes it to an Excel sheet.

Private Sub publicDataHandler()    ' this demonstrates the use of the GAS datahandler to access a google sheet directly from google.    ' note that as of writing, the handler is ALPHA does not yet support dates.    ' this example writes to a public sheet I'll do one with oauth2 later        ' read a sheet from excel    Dim ds As cDataSet, sheetName As String    sheetName = "vbaparseCustomers"    Set ds = New cDataSet    ds.load (sheetName)        ' datahandler is the API, sheetKey is the sheet where data is to be written/read    Dim dataHandlerUrl As String, sheetKey As String, url As String, driver As String, job As cJobject    dataHandlerUrl = "https://script.google.com/macros/s/AKfycbyNxrJg2SbjoKWJQgGxqjECkcA-A57xaoRQWzsJkTPbVyTWbCDi/exec"    sheetKey = "12pTwh5Wzg0W4ZnGBiUI3yZY8QFoNI8NNx_oCPynjGYY"    driver = "sheet"    url = dataHandlerUrl & "?driver=" & driver & "&dbid=" & URLEncode(sheetKey) & "&siloid=" & URLEncode(sheetName)        Dim cb As cBrowser    Set cb = New cBrowser  ' delete everything on the sheet    cb.httpGET url + "&action=remove"    If Not dataHandlerStatusOk(cb) Then Exit Sub     ' copy the sheet to google spreadsheet    cb.httpPost url + "&action=save", ds.jObject(, , , , "data").stringify    If Not dataHandlerStatusOk(cb) Then Exit Sub    ' see how many are in somalia from google spreadsheet    cb.httpGET url + "&action=count&query=" & URLEncode(JSONParse("{'country':'Somalia'}").stringify)    If Not dataHandlerStatusOk(cb) Then Exit Sub    Debug.Print "there are " & JSONParse(cb.Text).child("data.1.count").value & " customers in somalia" 

‘ delete somalia customers

    cb.httpGET url + "&action=remove&query=" & URLEncode(JSONParse("{'country':'Somalia'}").stringify)
    If Not dataHandlerStatusOk(cb) Then Exit Sub    ' count them again (note i use nocache to ensure i get the latest updates reflected)    cb.httpGET url + "&nocache=1&action=count&query=" & URLEncode(JSONParse("{'country':'Somalia'}").stringify)    If Not dataHandlerStatusOk(cb) Then Exit Sub    Debug.Print "there are now " & JSONParse(cb.Text).child("data.1.count").value & " customers in somalia"    ' create a new sheet in excel with the first 50 records from google docs, sorted by name    cb.httpGET url + "&nocache=1&action=query&params=" & URLEncode(JSONParse("{'limit':50,'sort':'name'}").stringify)    If Not dataHandlerStatusOk(cb) Then Exit Sub    Dim dsNew As New cDataSet    dsNew.populateJSON JSONParse(cb.Text).child("data"), firstCell(wholeSheet("newCustomers"))    ' clean up    dsNew.tearDown    ds.tearDown    cb.tearDownEnd Sub Private Function dataHandlerStatusOk(cb As cBrowser) As Boolean    Dim job As cJobject     dataHandlerStatusOk = False    If (Not cb.isOk) Then        MsgBox ("failed to make a connection " & cb.status & " " & cb.Text)   Else        Set job = JSONParse(cb.Text, , False)        If (Not job.hasChildren) Then            MsgBox ("failed to get a proper rest response " & cb.Text)         ElseIf (job.child("handleCode").value < 0) Then            MsgBox ("request returned a failure " & job.stringify)                Else            dataHandlerStatusOk = True         End If        job.tearDown    End IfEnd Function Of course since the DataHandler is database agnostic, the entire code above will work with only 2 parameter modifications to any known database, but more of that later.
Subpages