Examples
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.
=blisterData(“car_list”)
gain but sorted by price in descending order
=blisterData(“car_list”,”price”,true)
The unique list of colors sorted by color
=blisterUnique(“car_list”,”color”,”color”)
The make and price of the 5 most expensive cars
=blisterList(“car_list”,”make”,”price”,true,5) and
=blisterList(“car_list”,”price”,”price”,true,5)
The total price of all the cars on the lot
=sum(blisterList(“car_list”,”price”))
The color of the cheapest bmw on the lot
=blisterLookup(“car_list”,”bmw”,”make”,”color”,”price”)
The position of the most expensive yellow car in the list
=blisterMatch(“car_list”,”yellow”,”color”,”price”,true)
The make of the 3rd cheapest car
=blisterIndex(“car_list”,3,”make”,”price”)
The total value of all red mercedes
=sum(blisterList(“car_list”,”price”,,,,”make”,”mercedes”,”color”,”red”))
The number of Audi A1
=count(blisterList(“car_list”,”price”,,,,”make”,”audi”,”model”,”a1″))
All data about all black Audis
=blisterData(“car_list”,”make”,,,”color”,”black”,”make”,”audi”)
All red cars sorted by descending price and their prices and makes
=blisterList(“car_list”,”make”,”price”,true,,”color”,”red”)
=blisterList(“car_list”,”price”,”price”,true,,”color”,”red”)
Makes we have in white
=blisterUnique(“car_list”,”make”,,,,”color”,”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.
=blisterLookup(“blister.languageCodes”,”Chinese”,”language”,”code”)
Getting the column headings
It’s often useful to get the column headings – especially if using blisterData() to get a dump of the data
=blisterHeaders(“blister.airlines”)
Or you can get a single heading – in this case the name of the 2nd column
=blisterHeaders(“blister.airlines”,2)
Getting all known blisters in a library
=blisterDirectory(“blister”)
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
=blisterDirectory(“blister.languageCodes”)
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.
