Reading and writing Sheets from VBA

These articles are abridged from my book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available  from O’Reilly or Amazon.

If you just want to transfer data between sheets and excel and back again, the new Sheets API provides a simpler way to do it. I have an Excel wrapper for that – see Using the Google Sheets V4 API from VBA to integrate Sheets and Excel

We need a generic abstracted way of manipulating sheets using Apps Script from VBA, since we can’t access the individual SpreadsheetApp methods. I’ve created a function that provides access into any sheet directly from VBA, and also implemented a VBA version. With each version using the same methods and creating compatible data it’s easy to switch from Excel access to Sheets access.

These methods would be used in the Apps Script code as a standard way to get and present data to the co-operating VBA process. I recommend always using using getData() and setData() for data transfer.

Example

For this example we’ll use a workbook that contains a lookup sheet of airline carrier codes to names.

In addition to using this sheet as a reference, this workflow will also maintain a sheet counting the number of times particular flight codes have been encountered.

Moving a Workbook to Sheets

The objective of this step is to replace the data access part of the VBA automation so that it refers to workbook now converted to a Sheet.

VBA code to read Sheet

This function is a generic function to return all the data in any workbook/sheet. This pattern is fairly standard for all execution API access.

The project key is used both as part of the request and also to access the Oauth2 entry created earlier.

Private Function testSheetGet() As cJobject
    Dim api As cExecutionApi, execPackage As cJobject, args As cJobject
    '-- set up a run
    ' generic - get all the data on a given sheet from a given workbook
    Set args = JSONParse("[{'id':'1f4zuZZv2NiLuYSGB5j4ENFc6wEWOmaEdCoHNuv-gHXo', _
        'sheetName':'lookup'}]")
    Set api = New cExecutionApi
    Set execPackage = api _
        .setFunctionName("execGetData") _
        .setProject("MMo4EFhHV6wqa7IdrGew0eiz3TLx7pV4j") _
        .setDevMode(True) _
        .setArgs(args) _
        .execute
    ' see what we got
    Debug.Print JSONStringify(execPackage, True)
    ' clear up
    args.tearDown
    ' maybe useful for something else
    Set testSheetGet = execPackage
End Function

Here’s a small snippet of what get’s returned.

{
      "name":"execGetData",
      "done":true,
      "response":{
         "@type":"type.googleapis.com/google.apps.script.v1.ExecutionResponse",
         "result":[            {
               "carrier":"8P",
               "date added":"7/26/15 16:56",
               "name":"Pacific Coast Airlines"
            },
            {
               "carrier":"9R",
               "date added":"7/26/15 16:56",
               "name":"Satena"
            },

Apps Script code to read Shee0t

This is the function that is executed by Apps Script on behalf of VBA, in order to read a sheet when provoked by the execution API request. Note the function name and options match those passed from VBA.

} the data
*/
function execGetData (options) {
// maybe there are no arguments
options = options || {};
var exec = new SheetExec();
var data = exec.sheetOpen(options.id, options.sheetName).getData();
// need to convert any dates since its not transferrable
return exec.convertDatesToIso(data);
}

VBA code to write data to local workbook

Perhaps the next step could be to write the retrieved data somewhere, or to otherwise process it. Because the SheetExec class is compatible on both VBA and Apps Script, the data transferred between them can be written directly to a workbook without intervention.

This executes the previous snippet (testGetSheet) to request data from the sheet from Apps Script, then uses the VBA implementatino of SheetExec to dump that data to a local Excel workbook.

Private Function writeDataToSheet()
    '' use the get data test to pick up current data from SHEETS
    Dim execPackage As cJobject, error As cJobject, sheetExec As cSheetExec
    ' do the reading test to get some data from sheets
    Set execPackage = testSheetGet
    ' make sure it worked
    If (isSomething(execPackage.childExists("error"))) Then
        '' test for error
        Debug.Print JSONStringify(execPackage, True)
        Debug.Assert False
    Else
        '' it was good
        '' copy the data to an excel sheet
        Set sheetExec = New cSheetExec
        sheetExec.sheetOpen _ (, "fromGAS").clearContent.setData _ 
        execPackage.child("response.result")
    End If
    ' clear up
    execPackage.tearDown
End Function

VBA code to write to Sheet from local workbook

This example uses the VBA SheetExec to read data from a local workbook, and sends it to Apps Script to be written to a given Sheet. Note the use of the cJobject.attach method to add the data retrieved from the local workbook to the data property of the first argument required by the receiving Apps Script function.

Private Function testSheetSet()
    Dim api As cExecutionApi, execPackage As cJobject, args As cJobject, sheetExec As cSheetExec
    ' set all the data to a given sheet from a given workbook
    Set args = JSONParse( _
        "[{'id':'1f4zuZZv2NiLuYSGB5j4ENFc6wEWOmaEdCoHNuv-gHXo','sheetName':'fromExcel'}]")
    ' this time get the input data from the local excel sheet, and add to arguments
    Set sheetExec = New cSheetExec
    args.children(1).add("data").attach sheetExec.sheetOpen(, "dataSheet").getData
    ' do the api call
    Set api = New cExecutionApi
    Set execPackage = api _
        .setFunctionName("execSetData") _
        .setProject("MMo4EFhHV6wqa7IdrGew0eiz3TLx7pV4j") _
        .setDevMode(True) _
        .setArgs(args) _
        .execute
    ' see what we got
    Debug.Print JSONStringify(execPackage, True)
    ' clear this up
    args.tearDown
End Function

This produces a result like this, and of course the data is written to the selected Sheet.

{
  "name":"execSetData",
  "done":true,
  "response":{
    "@type":"type.googleapis.com/google.apps.script.v1.ExecutionResponse"
  }
}

Apps Script code to write to Sheet from local workbook

The data produced by the VBA implementation of SheetExec can be consumed without modification by the Apps Script version.

/**
* given a sheet id & name, set the given data
* @param {object} options args
*   @param {string} id the sheet id
*   @param {string} sheetName the sheetName
*   @param {boolean} clearFirst whether to clear first
*/
function execSetData (options ) {
  // maybe there are defaults
  options = options || {};
  var sheetExec = new SheetExec().sheetOpen(options.id, options.sheetName);
  // clear it ?
  if (options.clearFirst) sheetExec.clearContent();
  // write the data
  sheetExec.setData ( options.data );
}

All code samples can be found on Github. Note that that VBA samples have been developed for Office for Windows and may need some tweaking for Office for Mac.
Other items in this section

• Authentication and access
• Final migration steps
• Incremental migration
• JSON and VBA
• Migrating logic
• Migrating orchestration
• Testing Javascript on the PC
• VBA and Apps Script inventory

For more like this, see Execution API and Office to Apps migration. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.