|
| 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.
| 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 );
}
