How do I use VBA code in Google Sheets?

This site has moved here.

You can find this page here on the new site.

The Sheets V4 API is a very nice piece of work that exposes almost all of the Sheets object model in a REST API. Here's a VBA wrapper for that API. I'll add to it over time, but this version allows you to do this - in just a few lines of VBA.

• Authenticate to Sheets using Oauth2 from Excel
• Push sheet data to google
• Pull sheet data from google
• Get a list of sheets in a Google Spreadsheet

Examples

Let's get straight down to some examples. For the purposes of demo, I've made a sample workbook - sheetsAPI.xlsm - which you can get from the downloads page, under the data manipulation folder. 

It has two buttons - one to get this sheet from google, and another to send it there. 

Here's the code behind the buttons.

You can pass the name of the sheet (or a list of sheets separated by commas) to pull the data from Google. Here' im getting the activesheet.

Public Sub getThisSheet()

    Dim result As cjobject

    Set result = getStuffFromSheets(getMySheetId(), ActiveSheet.NAME)

    If (Not result.child("success").value) Then

        MsgBox ("failed on sheets API " + result.child("response"))

        Exit Sub

    End If

    writeToSheets result.child("data").children(1).child("valueRanges"), True

End Sub

And the same principle to push to google

Public Sub putThisSheet()

    Dim result As cjobject

    Set result = putStuffToSheets(getMySheetId(), ActiveSheet.NAME)

    If (Not result.child("success").value) Then

        MsgBox ("failed on sheets API " + result.child("response"))

        Exit Sub

    End If

End Sub

The sheet ID is the Google Drive ID of the workbook to write or read to. In my example, I'm simply returning a fixed id for a test spreadsheet. You may want to implement a form or a picker to select from multiple sheets. In any case you'll need to change this to whatever your own sheet id is.

Private Function getMySheetId() As String'// make this into your own sheet id

    getMySheetId = "1V54F5b1e1bOcJXJ_McRkpyJ5Dx_ndGnjdiZpBeyA4L0"

End Function

Authorization

People usually find OAUTH2 a little scary. I've tried to simplify it as much as possible. Here's what you need to do. 

• go to the google developers console (you can use the project associated with your spreadsheet, or as I do - create a new project that can be used for all spreadsheet activities).
• authorize the Sheets API
• generate some credentials
• copy them into this one off function, run it and allow it to access your sheets
• after that you can delete the one off function. It won't need to ask you again.

Private Function sheetsOnceOff()

    getGoogled "sheets", , _

    "109xxxxxxxxxxieuu2q3.apps.googleusercontent.com", _

    "CVgxxxxxxxxxePfe"

End Function

A note on different versions of windows/IE and locked down PCS

Behind the scene, the OAUTH2 process is fairly complex, and may not work on various combinations of Older IE and Windows. Some enterprises also lock down Windows so you can't POST from Excel, or you can't write to the registry, and various other combinations. getGoogled will only work if it can write to the registry, get to the internals of the IE DOM structure, and POST http requests to the google domain.  

When getGoogled has completed successfully, you'll find a registry entry for any scopes you've used. Note that you can have multiple entries if you have done some of the other integrations on this site that also use OAUTH2. The example above will create an entry for 'scope_sheets' that look like this under the xLiberation key. If you cannot get this to happen for some reason of lockdown or version combination, then you won't be able to get any further until that's solved. Look at the G+ community for others who have found various ways to get round lock down problems in their environment.

The console

Enable this API

Create a new oauth2 client ID

Choose other and then create 

Copy credentials and put them in the one off function

Run the one off function, and you'll get something like this

And that's it - you can use the example once you create the Google Sheet you want to play with and enter its id. You delete the once off function now if you like.

Compatibility

This will work on Windows 10 with Excel 2016. It will probably also work with older versions of both Windows and Excel too. It will need a few tweaks to work on Excel for Mac. If anybody would like to help with a Mac version then please ping me I can point you in the correct version. 

Some older installations have missing modern DLL versions, or some objects are blocked by policy. I've added some workarounds to to use different objects for external access from excel, as they have changed over time. If you get an error about xmlhttp, then if you can find out which xmlhttp object you do have installed, or is not blocked, then you can extend the function below (in the cBrowser class), to use it instead. If you do, then please leave feedback on the community so it can be implemented for others too, so more versions can be covered.

Private Function getHttpObject(Optional timeout As Long = 0) As Object

    '// some installation dont have server object installed, so fall back

    Dim ob As Object

    On Error GoTo missing

    Set ob = CreateObject("Msxml2.ServerXMLHTTP.6.0")

    ob.setOption 2, ob.getOption(2) - SXH_SERVER_CERT_IGNORE_CERT_DATE_INVALID

    ' can have change of timeout for complex/long queries

    If timeout <> 0 Then

        ob.setTimeouts 0, 30 * 1000, 30 * 1000, timeout * 1000

    End If

    Set getHttpObject = ob

    Exit Function

missing:

    On Error GoTo screwed

    Set ob = CreateObject("Msxml2.XMLHTTP.6.0")

    Debug.Print "falling back to client xmlhttp - server is not installed on this machine"

    Set getHttpObject = ob

    Exit Function

screwed:

    MsgBox ("cant find either server or client xmlhttp - there missing files from your windows installtion")

    Exit Function

End Function

More

I'll be adding to this api over time to be able to further collaborate between the two platforms.

Can I use VBA code in Google Sheets?

Can I use VBA macro in Google Sheets?

Can you run code in Google Sheets?

Is Google Apps Script similar to VBA?