Reading and writing Sheets from VBA

 

These articles are abridged from my book on Office to Apps migration. 

Going GASfrom 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.

method   purpose  
open (id, sheetName)given a sheet id and sheet name open a sheet. (in VBA the sheet id is the path on disk, defaulting to the active sheet and document).
getSheet

get the sheet managed by this SheetExec instance. 
setLowerCase(lowerCase)whether to convert headings to lower case when making objects out of sheet values.
getValues(a1Range , attrib)get the values in a sheet as two dimensional array. Optionally a range can be specified and an attribute other than values (for example FontColors) can be requested.
getData(a1Range , attrib)get the data in a sheet as a one dimensional array of key value pairs using the headings row as properties.. Optionally a range can be specified and an attribute other than values (for example FontColors) can be requested.  
clearContent (a1Range)clear the contents in a sheet. Optionally a range can be specified.
convertToObjects(values)converts a two dimensional array of values to a one dimensional array of key value pairs using the first row as the property names.
convertToValues(dataOb)converts to a two dimensional array of values from a one dimensional array of key value pairs, inserting a first row of property names as headings.

convertDatesToIso(dataOb)converts data values from JavaScript dates (which can’t be passed over Execution API) to ISO dates.
setData(dataOb, a1Range , attrib)convert data to values and write to a sheet using the property keys as the headings. Optionally a range can be specified and an attribute other than values (for example FontColors) can be requested.
setValues(values, a1Range , attrib)set the values in a sheet from a two dimensional array. Optionally a range can be specified and an attribute other than values (for example FontColors) can be requested.


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 Sheet


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.
/**
* given a sheet id & name, get the data converted to objects
* @param {object} options args
* @param {string} id the sheet id
* @param {string} sheetName the sheetName
* @return {[*]} 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

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

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments