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 ListsI 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
Getting startedYou'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 examplesIn 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 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.
|
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Things that have been deprecated >
GAS lists and validation
Subpages (14):
Accessing gas functions from Excel
Blister custom functions
Blisters from combined sheets
Blisters from socrata open data
Blister validation code
Example blister validation lists
Fusion and blister lists
Getting lists as a restquery
How to apply blister lists as as validation
REST access to blister functions
Setting up blister lists
Using blister custom functions
Using the API to manage lists
xml feeds and blister lists
Comments