It’s very common to want to lookup some list, or apply them as validation to data entry when playing around with Spreadsheets. Between them, a search for Spreadsheet related VLOOKUP, INDEX and MATCH return 30 million results.
For something so common, we spend a huge amount of time copying lists between spreadsheets, or figuring out ways of getting them from databases or shared workbooks. These lists could be pretty stable – used by the whole world (for example lists of countries), within a company (eg. branch addresses), within a department (eg stock list), or belonging to an individual (eg list of telephone numbers).
I figured it would be nice if I could share out global lists from a central place to Google spreadsheet users (and use the opportunity to provide the list as a web service so that the same list could be used by Excel and other apps that can consume web services). Not only that, but with the new datavalidation service in Google Apps Script, these same lists can be automatically applied to data entry without needing to make a copy of them in your sheet.
Here’s a slideshare primer
Here’s what’s needed
- Somewhere to store lists at each of global, group, personal or workbook level. I’m using ScriptDB for this.
- A way to populate them from multiple sources.
- Equivalents for VLOOKUP, INDEX and MATCH that can be called from a spreadsheet, a script or a web service, and a way to specify libraries from which to get the list.
- Filtering to modify lists to contain only items with particular values.
- Additional features such as sorting, getting unique values, named list columns and so on.
- The ability to apply validation to ranges from lists in Google Apps Script, and perhaps Excel too. Since these lists can be multi-column, validation based on other answers in the same row will also be applied dynamically.
Getting started
You’ll need 2 things to get started.
The blister library.
Include this in your sheet. You can find it here and include it in your project using reference MTP3ATeczKthcdvb_u278yiz3TLx7pV4j. The majority of code is in this shared library.
Custom function code and examples
In GAS you need the code for custom functions to be in your project rather than in a library. You should take a copy of the code in this sheet. In addition, there is something else that is held in the context of your sheet that need some local code
- Your scriptDB. Although blister is often about using scriptDB from shared libraries, it can also be useful to use it with a local scriptDB. In order to keep your scriptDB private access to the local version is kept within your script also.
The code you need to include is in this sheet – you’ll need the custom module. The examples module shows how to populate scriptdb , apply validations and store the rules in the your script properties. Typically you’ll only need to change a couple of parameters.
The sheet used to exercise these functions is also still in progress, but it can be found here. The functions are defined in this sheet in the custom.gs script.
This whole thing relies on ScriptDB, which Google deprecated in Nov 2014 so it was out of commission for a while. However, it is now using RipDB – scriptDB emulator instead with a Driver MongoLab backend so it all works again!
If you are building your own blisters, when you see reference to the showMyScriptDB() function, it now needs to get a DbAbstraction handler along with your selected back end. Nothing else changes. A typical showMyScriptDB() would look like this (in this example I’m using DriverScratch as my database platform
function getDefaultDB (optSilo) {
return new cRipDB.RipDB (new cDbAbstraction.DbAbstraction ( cDriverScratch , {
siloid:optSilo || 'blistertest',
dbid:'ripdb',
cachecommunity:"blistertest"
}));
}
function showMyScriptDb() {
return getDefaultDB();
}
All comments, suggestions, assistance, good lists are welcome as I develop this capability. You can get me on gitter, Twitter or the forum.