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" } }
The result
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); }
The log sheet looks like this
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.
- Authentication and access
- Final migration steps
- Incremental migration
- JSON and VBA
- Migrating logic
- Reading and writing Sheets from VBA
- 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.