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

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.

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.
    • 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.
    • The test is otherwise the same as the one I used in Local VBA versus JavaScript performance

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.

The result

Here’s the result.

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

For more like this, see Google Apps Scripts snippets, Integrating VBA and Javascript , Unit testing Apps Script ports from VBA and Integrating VBA and Javascript Why not join our community , follow the blog or follow me on Twitter.