Page Content hide
The custom functions
These are the custom functions you can call directly from your sheets. The function documentation is here and you can find examples of use in Using blister custom functions and you’ll need the code described in Blister validation code in your local workbook and a reference to the blister library. The majority of these functions can return a multidimensional array when there are multiple results.
These functions are for accessing lists of data held in scriptDB, either associated with the current sheet or from some other script or sheet. The purpose is to be able to have lookup, validation and filtering capability without the need to copy lists between workbooks. A blister list is a table of one or more columns of data. Each column can be accessed by name, or by position (starting at 1).
When the list is originally created (see Setting up blister lists) yiu can specify sort order (which column the list is naturally sorted on and descending/ascending). Everywhere you see the argument ‘optSortId’ below is an opportunity to modify that sort order. So for example you could sort on price/descending to lookup the most expensive item.
An optListId argument is where you specify which column of the table/list to operate on. It can be a name (if it was given one at load time) or a column number (starting at 1).
optFilterId and optFilterValue refer to filters that can be applied to the list to only include items where a column(optFilterId) has a particular value (optFilterValue)
listName always refers to the name of the list to look in. By default this will be in your current workbooks scriptDB. To look in other scriptDBs you just add the project name in front of the list name. I reserve the library blister for global lists I may make available from time to time, so the listname ‘blister.currencies’ refers to a list named currencies in the scriptdb belonging to library ‘blister’, whereas ‘currencies’ refers to the list name currencies in the current script/workbook scriptDB.
You can create your own shared scriptDBs and refer to them the same way, through library names. If you want to do this, you need to create a reference to any shared projects in ‘manage resources’ in your script for it to be accessible, and each shared project must contain a function that exposes its scriptDB like this.
|function||arguments||what it does|
|blisterLookup|| ( listName, value, optListId, optReturnListId ,
optSortId , optSortDescending, optMaxMatch )
|Somewhat like VLOOKUP. Returns values(s) from rows matching ‘value’, from blister list listName. By default it will match the value against the first column in listName. You can ask for a different match by specifying the column number or name to match against in optListId. By default the value returned will be the value found in the optListId but you can modify that with optReturnListId. The list will be searched in the same order as the key specified when it was initially created, but you can modify that with optSortID and optSortDescending. Finally optMaxMatch (default 1) , allows you to return an array of matching values if there is more than one match. 0 is all values, any other value returns up to that number.|
|blisterMatch|| (listName,value, optListId,
optSortId, optSortDescending, optMaxMatch )
|Somewhat like MATCH, this returns the index(es) (starting at one) as opposed to the value returned by blisterLookup. The parameters have the same meaning as blisterLookup.|
|blisterIndex|| ( listName, idx , optListId,
optSortId, optSortDescending )
|Somewhat like INDEX, this takes an index return by blisterMatch and returns the corresponding value. Under the covers, blisterLookup is actually a blisterMatch followed by a blisterIndex.|
|blisterUnique|| ( listName, optListId, optSortId, optSortDescending,
optMaxMatch ,optFilterId, optFilterValue )
|This returns an array of unique values for the given criteria. The parameters have the same meaning as for blisterLookup, with the addition of optFilterId which is a column number or name from the target list and optFilterValue which is a value that optFilterId needs to be equal to in order to be included in the list. This pair of parameters can be repeated multiple times with different optFilterID in order to do complex filtering. If there are no filter parameters, the unique values in the entire column indicated by optListId are returned.|
|blisterList|| ( listName, optListId, optSortId, optSortDescending,
optMaxMatch , optFilterId, optFilterValue )
|This is similar to blisterUnique, except that it returns all values in the lists, duplicated where necessary. All the parameters mean the same as blisterUnique|
|blisterData|| ( listName, optSortId, optSortDescending,
optMaxMatch ,optFilterId, optFilterValue)
|This is similar to blisterList, except it returns values from all columns in the stored list.|
|blisterHeaders||(optListId)||A list of column headings. Default is all columns|
|blisterDescription||(listId)||The description that was added when the list was created|
|blisterUpdateDate||(listId)||The last time the list was updated|
|blisterDirectory||(optLibrary)||A list of the listID and description of all known blisters in a library. If library is not specified, then you get any local lists.|
All comments, suggestions, assistance, good lists are welcome as I develop this capability. You can get me on Google plus, Twitter or this forum.