Migrating orchestration

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.

Other items in this section

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