In Using blister custom functions I showed examples of how to lookup scriptDB lists as custom functions. Here’s how to create lists.
In each case, to access lists in your sheet you need to add a reference to the blister library, and make a local copy of the custom functions, as described in GAS lists and validation. Once you have the blister reference you can use the library code to make your own blister lists.
Populating from a spreadsheet.
The most likely way of getting data into scriptDB is to populate it from a spreadsheet. Let’s start with a simple example – You’ll find this in the blister test spreadsheet.
It starts like this
Let’s say that we want to load this list to a scriptDB local to the current spreadsheet, and that the list was in a sheet called currencies. This code would load it.
function loadCurrencies () {
new blister.cBlister('currencies', { db: ScriptDb.getMyDb() , sortId:'Country'})
.makeBlisterFromRange( blister.getRangeFromItem({sheet:'Currencies', range:'a:c'}), true);
}
You could now use the custom functions to access data from that list. Test it by entering
=blisterData("currencies")
in an empty sheet. This will bring in the 3 columns of data in this list, sorted in country name order.
Loading from a global list.
Of course the point here is to be able to share data across multiple sheets from a single scriptDB. I’ve decided that this currency list is so common, that everyone in the world might need to use it. So I’ll load it into the blister library, which is one that I’m going to add useful things to for everyone to use.
function loadCurrencies () {
new blister.cBlister('currencies', { db: blister.showMyScriptDb() , sortId:'Country'})
.makeBlisterFromRange( blister.getRangeFromItem({sheet:'Currencies', range:'a:c'}), true);
}
The only difference in the code is that I’m writing to a different scriptDB. So now anybody in the world (who has included the blister library and custom functions), can now access this list in their spreadsheet, as follows, by prefixing the list name with the library name.
=blisterData("blister.currencies")
This means that this list can now be maintained in one place, but be available to everyone.
Group Lists
Lets say that your version of currencies you loaded to your local sheet was needed by some other sheet. Lets say that your sheet and library was called group. Any authorized person could now access this list simply by including the blister library and functions, plus your library in their sheet and accessing it like this
=blisterData("group.currencies")
Of course you can use all the functions described in Using blister custom functions for sorting, filtering, matching, looking up and so on without ever needing to have the list in your sheet.
All comments, suggestions, assistance, good lists are welcome as I develop this capability. You can get me on Google plus, Twitter or this forum.