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. 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 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 ServiceWhat 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. WalkthroughHere are the steps
In this case - urbaramaMashup(e) will do the work, returning the content to pass on as a response to the caller
CodeHere is the entire 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 LinksHere 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 TestingIt'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 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. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Integrating Apps with other platforms >