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.

Author: bm082975

Leave a Reply

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