Excel custom functions that can lookup data in Google Apps Script ScriptDB

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 all the data in the airlines list – it starts like this if you enter as an array formula
=blisterData(“blister.airlines”)

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

All these function are described here, and should behave the same in Excel as in Google Apps Script.

For more stuff like this see Excel Liberation

Author: brucemcp

Leave a Reply

Your email address will not be published. Required fields are marked *