GAS lists and validation

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).

Using Shared Lists

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();
}

 

More detail


All comments, suggestions, assistance, good lists are welcome as I develop this capability. You can get me on Google plus, Twitter or this forum.


For help and more information join our forum,follow the blog or follow me on twitter .