Using blister custom functions


Let's take an example. Say you are a small garage, with a stock list of cars and you work exclusively with spreadsheets. You don't want to take copies of the list, but you want everyone to use the same list across multiple spreadsheets. 

Here's your list


Loading list to scriptDB

Once loaded to a library, it will be available to all collaborators who need this list. The load operation is a script that looks something like this. This will create the list 'car_list' in the local scriptDB where I have the master list. To access this, other collaborating spreadsheets simply need to reference this master sheet as a library reference. The resultant 'blister' will contain 4 lists, each named after their column heading, and sorted by 'make'

  new blister.cBlister('car_list', { db: showMyScriptDb() , sortId:'make'})
                      .makeBlisterFromRange( getRangeFromItem({sheet:'Sheet1', range:'a:d'}), true);

Accessing the lists with custom functions

This is where the equivalents for VLOOKUP, MATCH and INDEX come in. But first, some additional capabilities. 
Dump the whole list columnwise in the current sheet. Note it is sorted by default since we specified sortId:'make'  when creating the blister initially.


Again but sorted by price in descending order


The unique list of colors sorted by color


The make and price of the 5 most expensive cars

=blisterList("car_list","make","price",true,5) and

The total price of all the cars on the lot


The color of the cheapest bmw on the lot


The position of the most expensive yellow car in the list


The make of the 3rd cheapest car


The total value of all red mercedes


The number of Audi A1


All data about all black Audis 


All red cars sorted by descending price and their prices and makes 


Makes we have in white


That's a few examples of the kind of thing you can do without even needing to have the list in your workbook. Naturally all these (and others) are available through script too. That (and validation) will be the subject of future articles as I finalize what this thing is going to look at. 

Using a library

Of course the point here is to make the same list available to multiple workbooks.  To be able to reference the scriptDB of another library is just a matter of adding a reference to the library in the script. These functions will then reference the library's scriptDB rather than its own by prefixing the list ID with the library name. 

Looking up the language code in the global blister library would look like this.


Getting the column headings

It's often useful to get the column headings - especially if using blisterData() to get a dump of the data

Or you can get a single heading - in this case the name of the 2nd column

Getting all known blisters in a library

will list all known listIds and descriptions in the shared blister library. No argument will give you known blisters associated with your local sheet's scriptDb.

Getting description

will list  description stored when list was created.

Getting update Date

=blisterUpdateDate ("blister.languageCodes")
will show date of last update to the list

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 .