Executing Apps Script with VBA

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.

Manifests

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

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'ReillyAmazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.


Comments