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. As the migration progresses, there is less and less happening in VBA, and its role is reduced to one of orchestration. In this example, all the data has moved to Sheets along with the logic. VBA still orchestrates the process, and potentially makes some other changes, or updates based on the logic result that are outside the scope of the process being migrated.
This case gets the results of matching as in the previous example, and updates another worksheet (also on Sheets), incrementing the number of times a particular valid flight number is detected, possibly enriching it with some data from some other process.
VBA process orchestration code
This example retrieves the flight validation results from testFlights as covered in the previous section, does some processing, and sends an array of flights to be logged by Apps Script in a different Sheet.
Private Function testLog()
Dim api As cExecutionApi, matchResults As cJobject, job As cJobject, _
execPackage As cJobject, args As cJobject, sheetExec As cSheetExec
' does an update following a match - first do the the match process
Set matchResults = testFlights
' make sure it worked
If (isSomething(matchResults.childExists("error"))) Then
'' test for error
Debug.Print JSONStringify(matchResults, True)
Debug.Assert False
Else
'' only the data to add to an array
Set args = New cJobject
' there's only 1 argument
With args.Init(Nothing).addArray
'.. which is an array
With .add.addArray
'' maybe some more processing would happen with the resutls here...
''' here's i''l just filter on the good results
For Each job In matchResults.child("response.result").children
If (job.toString("status") = "ok") Then
.add.attach job
End If
Next job
End With
End With
' do the api call
Set api = New cExecutionApi
Set execPackage = api _
.setFunctionName("execLog") _
.setProject("MMo4EFhHV6wqa7IdrGew0eiz3TLx7pV4j") _
.setDevMode(True) _
.setArgs(args) _
.execute
End If
' see what we got
Debug.Print JSONStringify(execPackage, True)
' clear this up
args.tearDown
End Function
{
"name":"execLog",
"done":true,
"response":{
"@type":"type.googleapis.com/google.apps.script.v1.ExecutionResponse"
}
}
Apps Script logging code
This updates a log sheet with flights and the number of times they’ve been seen. If it’s a new flight it’s added to the sheet.
/**
* this one increments a log with all the found flights
* @param {object} results the results from an execmatch
* @return {[object]} the data from the log
*/
function execLog (results) {
// get the sheet data
var sheetExec = new SheetExec().sheetOpen(Settings.LOG.ID, Settings.LOG.NAME);
var log = sheetExec.getData();
/// log the results
results.forEach(function (d) {
if (d.status === "ok") {
// need to log
var findLog = log.filter(function(f) {
return f[Settings.LOG.HEADINGS.FLIGHT].toLowerCase() === d.flight.toLowerCase();
});
// add if its new
if (!findLog.length) {
var item = {};
item[Settings.LOG.HEADINGS.FLIGHT] = d.flight;
item[Settings.LOG.HEADINGS.COUNT] = 0;
log.push(item);
}
else {
var item = findLog[0];
}
// increment
item[Settings.LOG.HEADINGS.COUNT]++;
}
});
/// write the data (no need to clear)
sheetExec.setData (log);
}
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.