Migrating orchestration

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.

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

        '' 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) _

    End If

    ' see what we got
    Debug.Print JSONStringify(execPackage, True)

    ' clear this up

End Function

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;
      else {
        var item = findLog[0];

      // increment


  /// 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.

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.