Migrating logic

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

At this stage of migration, some data has already been moved to Sheets, it is being accessed remotely from VBA using the Execution API as described in Reading and writing Sheets from VBA and now it’s time to move some logic from VBA to App Script.

Migrating logic

One of the tasks in this imaginary system being used as an example, is to automatically search incoming mail for content containing valid flight numbers from a selected list of airlines, and to process attachments and initiate email conversations when they are detected.

Now it’s time to move this logic to Apps Script.

Using the execution API, we can migrate the airline data and the selection process immediately to Google Apps, with only minimal changes to the VBA code. Other steps in the process can be gradually migrated one step at a time.

VBA code to initiate logic on Apps Script

This snippet simplifies the problem with some example data extracted from emails. The content is passed to Apps Script for processing, and the result should be equivalent to what the VBA version previously produced.

The VBA logic can now be replaced with a delegated call to the execution API.

Private Function testFlights() As cJobject
    Dim api As cExecutionApi, execPackage As cJobject, args As cJobject
    '-- set up a run
    ' this one the sheet is known to the exec function already
    ' the logic is in apps script
    Set args = JSONParse("[" & _
        "['scheduled to take LH123'," & _
        "'not an interesting XX123 airline number'," & _
        "'Going to AU on QF928 tomorrow']]")
    Set api = New cExecutionApi
    Set execPackage = api _
        .setFunctionName("executeMatch") _
        .setProject("MMo4EFhHV6wqa7IdrGew0eiz3TLx7pV4j") _
        .setDevMode(True) _
        .setArgs(args) _
        .execute
    ' see what we got
    Debug.Print JSONStringify(execPackage, True)
    ' clean up
    args.tearDown
    ' might need this later
    Set testFlights = execPackage
End Function

The result

{
  "name":"executeMatch",
  "done":true,
  "response":{
    "@type":"type.googleapis.com/google.apps.script.v1.ExecutionResponse",
    "result":[ {
      "flight":"LH123",
      "carrier":"LH",
      "name":"Lufthansa Airlines",
      "status":"ok"
    },
    {
      "flight":"not an interesting XX123 airline number",
      "status":"not found"
    },
    {
      "flight":"QF928",
      "carrier":"QF",
      "name":"Qantas Airways",
      "status":"ok"
    }
  ]
 }
}

Logic code delegated to Apps Script

Now both the lookup data and the logic are delegated to Apps Script in the following function. All that’s needed is for VBA to pass over the text to be validated.

 
/**
* @param {[string]} flightNumbers to check
* @return {object} the result
*/
function execMatch (flightNumbers) {
  // get the sheet data
  var lookup = new SheetExec()
    .open(Settings.LOOKUP.ID, Settings.LOOKUP.NAME)
    .getData();
  // generate a regex for flightnumbers of each interesting airline
  var rx = new RegExp (getRegex(lookup) ,'gmi');
  return flightNumbers.map(function(flightNumber) {
    // match against given flight code 
    var found = rx.exec(flightNumber);
    // return the airline code, name and the flight number
    return found ? 
      { status:"ok",
        flight:found[0],
        carrier:found[1],
        name:lookup.filter(function(d) { 
          return d[Settings.HEADINGS.CODE].toLowerCase() === found[1].toLowerCase();
        })[0][Settings.HEADINGS.NAME]
      } : 
      { status:'not found',
        flight:flightNumber
      };
   });
}
/**
* make the regex for flight matching
* @param {object} lookup the lookup data
* @return {Regexp} the matching regex
*/
function getRegex (lookup) {
  // in case the sender canr do json objects
  var ob = typeof lookup === typeof 's' ? JSON.parse(lookup) : lookup;
  return '\b(' +
    ob.map(function(d) { 
      return d[Settings.HEADINGS.CODE].toLowerCase();
    }).join("|") +
    ')([a-z]?)(\d{1,4}[a-z]?)\b';
}

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 orchestration
• 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 follow me on twitter to ensure you get updates when they are available.