Testing Javascript on the PC

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.

Set getSource = execPackageEnd 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.

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

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.