Validation and lookups using shared scriptDB lists

When the scriptable data validation feature in Google Apps Script came out recently, I started thinking about how we don’t really use the cloud based nature of Google Docs to its potential in a number of areas. VLOOKUP, MATCH and INDEX have all been faithfully reproduced from Excel in Google Spreadsheets, but they bring with them a fragility associated with relative positioning as well as the divergent list sprawl characteristic of Excel based lists.

After playing around with it a for a while, I figured that storing lists in shareable scriptDBs would better encourage the single versions of lists. The performance of scriptDB seems to be good enough for real world uses and the library resource management of Google Apps Script makes it pretty easy to share lists amongst workbooks.

With that in mind, I came up with some custom functions that allow you to

  • Store lists/tables in shared scriptDBs and access them from multiple workbooks
  • access cloud based shared lists through familiar vlookup, match and index type custom spreadsheet functions, through scripts or even through a rest type API so that the lists can be exposed outside of Google Apps.
  • apply additional sorting and filtering functionality to those functions
  • apply lists to ranges as data validation rules
  • dynamically apply valid combinations from lists to narrow down options for data entry
Here’s a few examples of the kind of things you can do.

Looking up codes from a shared global list that is common to everyone.

=blisterLookup(“blister.languageCodes”,”Chinese”,”language”,”code”)


Applying a global list as validation for data entry

Using the same list to lookup corresponding text values

=blisterLookup(“blister.currencies”,A2,”ISO”,”Country”)
=blisterLookup(“blister.currencies”,A2,”ISO”,”Currency”)

Dynamic dependent list validation based on valid combinations in a list group

List the 5 most expensive items

=blisterList(“car_list”,”make”,”price”,true,5)
=blisterList(“car_list”,”price”,”price”,true,5)



 

Total Price of all items

=sum(blisterList(“car_list”,”price”))

The entire list filtered by a couple of criteria

=blisterData(“car_list”,”make”,,,”color”,”black”,”make”,”audi”)

For how to use this, the code, and more examples see Excel Liberation.

About brucemcp 224 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.


*