In REST access to Google Apps Script list management functions we looked at accessing the Google Apps ScriptDB and some custom functions using the rest to Excel library
In that post, I mentioned that it would even be possible to create custom function that could be called from an Excel spreadsheet to access these Google Apps Script functions. Here’s the concept
- Google Apps Script stores lists in noSQL database(s)
- Custom functions, normally used in Google Apps Script, are directly queried by Excel functions, and return the same data as they would in GAS. This time though, their data is exposed through a GAS webapp which shoots over some Json to Excel.
- The Excel versions of these functions only need to construct an appropriate query, and interpret the JSON
- Like this, there only needs to be one cloud based copy of each list accessible from any Google or Excel Spreadsheet.
Performance
In theory, this is not a great idea – recalculating Excel spreadhseets could take a while if the functions have to go off to the web. In practice though, it seems to work pretty well with a small amount of formulas. If you just want to do a few lookups it seems to perform just fine.
Note that all these functions return arrays. That way you can use array formulas if you like, to minimize number of queries.
Examples
Get the headers, then the data from the billboardhot100, again both entered as an array formula
=blisterHeaders(“blister.billboardhot100”)
=blisterData(“blister.billboardhot100”)
Here’s a few more examples, showing how to lookup stuff up and integrate the results with regular Excel functions
For more stuff like this see Excel Liberation
