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.tearDown
End 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 If
    
    
End 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.

For help and more information join our forum,follow the blog or follow me on twitter .



Comments