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
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'
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
The total price of all the cars on the lot
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
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.
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.
It's often useful to get the column headings - especially if using blisterData() to get a dump of the data
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.
will list description stored when list was created.
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.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Things that have been deprecated > GAS lists and validation >