In Integrating VBA and Javascript I showed how you could run your JavaScript and Apps Script source locally on your PC from VBA. The example in Local VBA versus JavaScript performance used apps script code hosted on Github. Now let’s go 1 step further and get the code directly off Apps Script and execute it from VBA.
Recap
In this test I’ll be using the ColorMath code from Color Arranger, just as we used in the Github version in Local VBA versus JavaScript performance.
The Apps Script webapp
You’ll need to expose your code from apps script via a Webapp. You’ll find the code for my test on Github. In my project I have a few source scripts I’d like to share with VBA. For this example, we’ll assume that the webapp permissions are set to anybody even anonymous can run. For private permissions. we’d need to use oAuth2 as described in Google Oauth2 VBA authentication. I’ll give a specific example of that using this example in a later post.
I’m leaving control over what to expose in the Apps Script webapp – this is controlled by manifests which list out the source code that can be exposed. In this example I have 3 manifests set up. Each has
- A name – the vba procedure asks for a particular manifest (or multiple manifests) and receives all the code listed
gasCdn.manifests = (gasCdn.manifests || []).concat([ { name:'all', gs:['Utils'],html:['main.js','ColorMath.js'] }, { name:'default', gs:['Utils'],html:[] }, { name:'color', gs:[],html:['ColorMath.js'] } ]); - gs[] – an array of all the Apps Script scripts to be served when that manifest is requested
- html[] – an array of html/js script files to be server with that manifest
For this example, I’m going to request the ‘color’ manifest and be served the code for ColorMath.js, so my webapp will be called like this.
webappurl?manifests=color
I can provide a comma separated list of manifests to get multiple manifest contents. Remember that VBA can only execute vanilla JS. You can’t access any Apps Script specific services.
Code
Here’s the Apps Script code. It shouldn’t need any modification aside from the manifest contents.
function doGet (e) {
// set up default manifest
e = e || {parameter:{manifests:''}};
var code = GasCdn.getManifests( (e.parameter.manifests || 'default').split(',') );
// serve it up
return ContentService
.createTextOutput(code)
.setMimeType(ContentService.MimeType.TEXT);
}
var GasCdn = (function (gasCdn) {
'use strict';
// these control which info can be served up over web service
// should contain lists of script file names against some manifests name
// called with ?manifests= on the url
gasCdn.manifests = (gasCdn.manifests || []).concat([
{ name:'all', gs:['Utils'],html:['main.js','ColorMath.js'] },
{ name:'default', gs:['Utils'],html:[] },
{ name:'color', gs:[],html:['ColorMath.js'] }
]);
/**
* get code for all manifests
* @param {string[]} manifest names to get
* @return {string{ the code
*/
gasCdn.getManifests = function (manifests) {
var code = '//--served up by GasCdn\n';
code += manifests.map(function(d) {
// find requested manifest
var target = gasCdn.manifests.filter (function (m) {
return m.name === d;
});
// can only be 1
if(target.length !== 1) {
throw 'manifest not found or ambiguous ' +d;
}
// get the code
return gasCdn.getCode (target[0]);
}).join('\n');
return code;
}
/**
* serve up contents of manifest
* @param {string} manifest name
* @return {string{ the code
*/
gasCdn.getCode = function (manifest) {
var code = '//--manifest:' + manifest.name + '\n';
// add any apps script code
code += (manifest.gs || []).map ( function (d) {
return '//--apps script:' + d + '\n' + ScriptApp.getResource(d).getDataAsString();
}).join("\n");
// and any html/js code
code += (manifest.html || []).map ( function (d) {
return '//--html/js:' + d + '\n' + HtmlService.createHtmlOutputFromFile(d).getContent();
}).join("\n");
return code;
};
return gasCdn;
})(GasCdn || {});
Once you’ve published this you can access it (almost) as if it were a regular hosted javaScript source.
The VBA end
A couple of notable points on what’s going on at the VBA end.
-
- The Windows JavaScript engine is not at the same level as the Apps Script one, so there’s a good chance you’ll be using stuff in Apps Script that Windows doesn’t support, so I’ll pull in Douglas Crockford’s json2 polyfill. as well as Jordan Harband’s es5-shim . This will gives us polyfills to take use to Emacs-5 JavaScript, which the Apps Script engine supports a subset of.
.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"
- I’m getting the Apps Script source directly from my Apps Script webapp. .addAppsScript is a lightly modified version of .addUrl, as apps script has some formatting quirks that need sorted out. (see). it also removes any script tags you may have had if you’ve served up a hyml/js file.
.addAppsScript "https://script.google.com/macros/s/AKfycbzVhdyNg3-9jBu6KSLkYIwN48vuXCp6moOLQzQa7eXar7HdWe8/exec?manifests=color"
- The test is otherwise the same as the one I used in Local VBA versus JavaScript performance
- The Windows JavaScript engine is not at the same level as the Apps Script one, so there’s a good chance you’ll be using stuff in Apps Script that Windows doesn’t support, so I’ll pull in Douglas Crockford’s json2 polyfill. as well as Jordan Harband’s es5-shim . This will gives us polyfills to take use to Emacs-5 JavaScript, which the Apps Script engine supports a subset of.
The test will do a couple of things to make sure everything is working, then time how long it takes to compare 2 colors a load of times.
Private Function testGas()
Dim js As New cJavaScript, start As Double, numberOfTests As Long, result As Variant
numberOfTests = 10000
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"
' get my code from apps script
.addAppsScript "https://script.google.com/macros/s/AKfycbzVhdyNg3-9jBu6KSLkYIwN48vuXCp6moOLQzQa7eXar7HdWe8/exec?manifests=color"
' my code
.addCode ("function compareColors (rgb1, rgb2) { " & _
" return theColorProp(rgb1).compareColorProps(theColorProp(rgb2).getProperties()) ; " & _
"}" & _
"function compareColorTest (numberOfTests) {" & _
"for (var i = 0 , t = 0 ; i < numberOfTests ; i++ ) { " & _
" t += compareColors ( Math.round(Math.random() * VBCOLORS.vbWhite) , Math.round(Math.random() * VBCOLORS.vbWhite) ); " & _
"}" & _
" return 'average color distance:' + t/i;" & _
"}" & _
"function theColorProp (rgb1) { " & _
" return new ColorMath(rgb1) ; " & _
"}" & _
"function theColorPropStringified (rgb1) { " & _
" return JSON.stringify(theColorProp(rgb1).getProperties()) ; " & _
"}")
'a stringified color properties
Debug.Print .compile.run("theColorPropStringified", vbBlue)
'compare a couple of colors
Debug.Print .compile.run("compareColors", vbBlue, vbRed)
' do a performance test
start = tinyTime
result = .compile.run("compareColorTest", numberOfTests)
Debug.Print "time to complete in JS " & (tinyTime - start)
Debug.Print result
End With
End Function
The result
Here’s the result.
average color distance:50.966386685860705
{"nearestMatch":-1,"rgb":16711680,"red":0,"green":0,"blue":255,"htmlHex":"#0000ff",
"luminance":0.0722,"textColor":"#ffffff","contrastRatio":8.592471358428804,
"black":0,"yellow":0,"magenta":1,"cyan":1,"hsHue":240,"hsSaturation":100,"hsLightness":50,
"hsValue":1,"x":18.05,"y":7.22,"z":95.05,"cieLstar":32.30258666724948,"cieAstar":79.19666178930937,"cieBstar":-107.86368104495167,"cieCstar":133.81586201619493,"hStar":306.2872015643272}
52.8786741404613
time to complete in JS 2.79174763185438
average color distance:44.911496164110424
To recap
-
-
- We got some polyfills to bring Windows JavaScript engine up to par with Apps Script
- Got some source code directly from an Apps Script file
- Ran some tests using some local JavaScript
-
.. all using co-operating Apps Script and VBA.
Code
You can get the VBA test code and class from gitHub. It’s been put there automatically – you can use Getting started with VbaGit to pull it into your workbook or just download it. Remember to look at the dependency file for the Excel references required.
The Apps Script is also on github, and was put there automatically as described in Getting your apps scripts to Github