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.
Translated chunks of code can be hard to test outside the environment in which they were designed to site. There comes a time when end to end tests are required, before any data has been migrated and before the system is complete.
Using the Execution API, it is possible to retrieve code from Apps Script modules, and to run them on the PC under the control of VBA and in place of the VBA code they replace. Not all code is suitable for this treatment, but pure logic code could be. Not only that, but the results can also be tested against the original VBA code in place.
This small demonstration is drawn from the example in this section. The purpose of this Apps Script function is to generate a regular expression based on the contents of the column of a lookup worksheet. At this early stage in the process, no data has been moved (it’s still in Excel), and no other functions have been written in Apps Script.
This technique is explored in more detail in How to use javaScript from VBA
Retrieving code and executing it locally;
VBA code to get source code from Apps Script
This is a generic VBA function to provoke execution of a function on Apps Script to return requested source code.
Private Function getSource(args As cJobject) As cJobject
Dim api As cExecutionApi, execPackage As cJobject
' do the api call
Set api = New cExecutionApi
Set execPackage = api _
.setFunctionName("execGetSource") _
.setProject("MMo4EFhHV6wqa7IdrGew0eiz3TLx7pV4j") _
.setDevMode(True) _
.setArgs(args) _
.execute
Set getSource = execPackage
End Function
Apps Script code to return source code
This function is expecting to be asked to return the source code of an array of modules and optionally, an array of function names within modules. It’s not perfect as it’s clearly not a code parser, but it can find the common function constructs.
If no function names are provided it returns the whole module.
/**
* get any code -- doesnt handle {}() in comments or strings properly yet
* @param {[object]} sourceToGet the source to get [{module:x, functions:[y]}]
* @return {[object]} the result [{source:the source, module:x, functions:[y]}]
*/
function execGetSource (sourceToGet) {
return sourceToGet.map(function(d) {
var module = ScriptApp.getResource(d.module).getDataAsString();
if (d.functions) {
var source = d.functions.reduce(function(p,c) {
var match = new RegExp('\b\(?\s*function\s*' + c + '|var\s+'+c+'\s*=\s*\(?\s*function','gm').exec(module);
if (!match) {
throw 'function ' + c + ' not found in module ' + d.module
}
// now find matching close {
var depth, s = '', bracketStart = '{', bracketEnd = '}' ;
module.slice (match.index).split('').some(function(r) {
if (bracketStart == r) {
depth = (typeof depth === typeof undefined ? 1 : depth +1);
}
if (bracketEnd == r) {
depth = (typeof depth === typeof undefined ? undefined : depth -1);
}
s+=r;
return depth ===0;
});
p += (s + 'n');
return p;
} ,'');
return {module:d.module, functions:d.functions , source: source};
}
else {
return {module:d.module, functions:d.functions , source: module};
}
});
}
Getting the source and testing local execution
This is the code to test local execution. After getting the required source code it has to get the lookup data (still held locally in Excel at this stage), which is going to be an argument to the JavaScript function running locally.
Windows uses a fairly old JavaScript engine so basic features are missing. The cJavaScript class can retrieve shim code to execute locally to modernize the engine, which you can see below.
Finally it adds the source code retrieved by the Execution API from Apps Script, and executes the new function, getRegex, with the argument of the lookup data retrieved from excel.
Private Function testLocalExecution()
Dim js As New cJavaScript, result As Variant, lookup As cJobject, _
execPackage As cJobject, job As cJobject, sheetExec As cSheetExec
' get the source code I need
Set execPackage = getSource( JSONParse( _
"[[{'module':'Settings'},{'module':'Executes','functions':['getRegex']}]]"))
' going to need the lookup data
Set sheetExec = New cSheetExec
' here im passing the data from an excel hosted data sheet
' at a later stage the data could come from sheets instead
Set lookup = sheetExec.sheetOpen(, "dataSheet").getData
With js
' not really necessary first time in
.clear
' here's a couple of polyfills to bring it more or less up to apps-script levels
.addUrl "https://cdnjs.cloudflare.com/ajax/libs/json2/20150503/json2.min.js"
.addUrl "https://cdnjs.cloudflare.com/ajax/libs/es5-shim/4.1.7/es5-shim.min.js"
' my code
For Each job In execPackage.child("response.result").children
.addCode job.toString("source")
Next job
result = .compile.run("getRegex", lookup.stringify)
Debug.Print result
End With
' now we have the regex generated by the apps scrpit code, pulled down to windows, and run locally
' it could be compared against the VBA versino of the same thing to ensure the module is working okay
End Function
The result
b(8p|9r|ac|ay|b6|bs|ch|en|f9|hw|lh|na|on|qf|rj|tx|wy|xj|zb|zb)([a-z]?)(d{1,4}[a-z]?)b
Clearly this technique is not for all code, but it can provide a really useful comparative testing environment for early code conversions. Being able to test logic in the same environment at the same time has huge potential.
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
- Migrating orchestration
- Reading and writing Sheets from VBA
- 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.