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.

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.

You can try this - using google apps script to run a couple of apis by proxy and returning combined results.

Walkthrough

Here are the steps
  1. Create a new script, shared with everyone that would need access to it
  2. publish your app as a web app, with appropriate level of access
  3. create a doGet(e) function, calling some function that actually does the work
  4. 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

Here is the entire code.  

pseudo API

The gadget spec URL could not be found

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
https://script.google.com/a/macros/mcpher.com/s/AKfycbxsqeuAjem3sq35UH27XPn7nHRJdjw2HZ5t2Pv2GWzyFOJRNt4/exec

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.   join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. 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 and ScriptDB as image store

For help and more information join our forum, follow the blog, follow me on twitter

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'Reilly, Amazon 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