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
You'll need 2 things to get started.
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.
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
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
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation >