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.


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.


Get all the data in the airlines list – it starts like this if you enter as an array formula

Get the headers, then the data from the billboardhot100, again both entered as an array formula


Here’s a few more examples, showing how to lookup stuff up and integrate the results with regular Excel functions

All these functions are described here and should behave the same in Excel as in Google Apps script.
For all the details see Excel Liberation, GAS lists and validation, and download the blisterFunctions.xlsm workbook from Excel Liberation downloads.

For more stuff like this see Excel Liberation

About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


3 × 2 =