Let Google Apps Script do the work

For this site, I usually create a Google Apps Script version and a VBA version of the projects and libraries discussed. This is not so hard, since there are utility libraries in both languages, so it’s just a case of tweaking the language syntax. I also played around with calling javaScript from VBA, and passing arguments in VBA using javascript like objects.

But what if there were more than two languages to support? Could we do most of the work once and pass queries and results back and forwards? In this post I showed how to hand off the business of credential storage to ScriptDB and of doing rest queries needing oAuth using google Apps script as a proxy.

Moving on from that, I looked at how we could focus API mashups to one language, and create an entirely new pseudo api based on that mashup, with the results being delivered as a jSon response.

But what else? In Urbarama mashup I showed how to combine 2 APIs to create a simpler UI – but I still had to implement it in both VBA and Google Apps Script. As a new pattern for the future, for these type of applications, why not write only the Google Apps Script version, and return the result (whether to VBA or an entirely different language) as a jSon reponse.

Page Content hide

1 The google Apps Script Content Service

The google Apps Script Content Service

What makes this possible, is the GAS content service, where content can be rendered in various formats, including jSon. Let’s look at how to re-implement the Urbarama mashup in Apps Script as a published web service, and simply call it from VBA , GAS (or elsewhere). We can even add this new pseudo API to the Rest to Excel library as if it was a genuine API.

Walkthrough

Here are the steps

Create a new script, shared with everyone that would need access to it
publish your app as a web app, with appropriate level of access
create a doGet(e) function, calling some function that actually does the work
access the published url with appropriate arguments, and consume the jSon returned

In this case – urbaramaMashup(e) will do the work, returning the content to pass on as a response to the caller

function doGet(e) {
    return ContentService
            .createTextOutput(JSON.stringify (urbaramaMashup(e)))
            .setMimeType(ContentService.MimeType.JSON);     
}

Code

What’s happening here is that we are calling multiple APIs and returning the combined result as if it was a new API. This idea of course can be extended to other , even non-API examples, and we’ll take a look at some examples of this in future pages, including adding a new pseudo API to the Rest to Excel library .

Links

Here is the link to the pseudo API

Arguments:

?address=some place&within=distance in km

Testing

It’s always easier to test in Google Apps Script before using it as a web service. Here’s an example of a testing pattern.

// just a tester
function t() {
  var e = { parameter: 
            {address : "london",
             within  : 10
            }
          };
  Logger.log(JSON.stringify(urbaramaMashup(e)));
}

For more like this see From VBA to Google Apps Script

Much of the code referred to here is the mcpher library. You can find out how to include it in your project here. For more examples of this concept see Proxy jsonp.

Join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.