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
Using the same list to lookup corresponding text values
Dynamic dependent list validation based on valid combinations in a list group
List the 5 most expensive items
Total Price of all items
The entire list filtered by a couple of criteria
For how to use this, the code, and more examples see Excel Liberation.