cloud based lists and validation with google apps script
DESCRIPTION
Managing shared Lists for lookups and validation in Excel can be problematic across workbooks. Google Docs gives us the opportunity to work from single shared lists, immediately available, and current. Here's how. See http://ramblings.mcpher.com/Home/excelquirks/listsandvalidation for more informationTRANSCRIPT
Cloud based lists and validation
Google Apps Script lists Excel Liberation
Sharing lists in the cloud Work from shared copies of a list Use familiar functions like vLookup, Index,
Match to access shared lists Apply lists to validate data entry Sort and filter lists Manipulate lists as data
Sharing lists in Excel is problematic
carrieraa
ba
ua
nameAmeric
an Airlines
British Airways
United Airlines
Here’s a 2 column list of the type often used in spreadsheets to validate data input, or used as a lookup
Using it in more than one Excel workbook leads to•list sprawl/divergence•failures if a shared list is moved
Using scriptDB as list store Google
spreadsheets, in the cloud, should solve these excel sharing problems
But Vlookup, Index, Match and Validation techniques are all copied faithfully from Excel
So we’ll just replicate the Excel fragility and sprawl
Unless..
Sharing blisters from ScriptDB
Lists....
Shared scriptDBs
Local scriptDBs
Lists....
Lists....
Spreadsheets
Lookups and validation
Vocabulary
carrieraa
ba
ua
nameAmerica
n Airlines
British Airways
United Airlines
listName: ‘airlines’
library: ‘blister’
Headers: 1,2 or
‘carrier’,’name’
Lists are references as ‘library.listName’ – for example, ‘blister.airlines’ Columns are referenced by number or name – (‘blister.airlines’,2) is the same as (‘blister.airlines’,’name’) The library is the name of the scriptdb resource in which the list is stored
Simple functions
Finding, listing, sorting,properties
Finding Looking things up (vlookup)=blisterLookup("blister.airlines","AA","carrier","name
")American Airlines
Finding position of (match)=blisterMatch("blister.airlines","UA","carrier")200
Get value at (index)=blisterIndex("blister.airlines",120,"name")Lan Peru
Listing The list headers=blisterHeaders("blister.airlines") carrier name link The list data=blisterData("blister.airlines") 2F Frontier Flying Service
2J Air Burkina
2N Nextjet
3M Silver Airways
3X Japan Air Commuter
4Z Airlink
... etc
List just one column=blisterList("blister.airlines","name") Frontier Flying Service
Air Burkina
Nextjet
Silver Airways
Japan Air Commuter
Airlink
... etc
Sorting list data sorted by name=blisterData("blister.airlines","name") JP Adria Airways
RE Aer Arann Express
EI Aer Lingus
SU Aeroflot Russian Airlines
AR Aerolineas Argentinas
AM Aeromexico
...etc..
List just one column sorted by name=blisterList("blister.airlines","name","name") Adria Airways
Aer Arann Express
Aer Lingus
Aeroflot Russian Airlines
Aerolineas Argentinas
Aeromexico
...etc..
Properties Directory of blisters in a library=blisterdirectory("blister") currencies List of currencies and exchange rates by country
and ISO code
languageCodes list of language codes by country
champagne volume of champagne shipped
Billboardhot100 billboard top 100
airlines list of airlines and their flight codes
Date that the list was last updated=blisterUpdateDate("blister.airlines") 24/09/2013 17:33:43
Integration with other functions
Complex lists
Complex lists So far we’ve looked at simple lookup lists We can also use data as a blister lookup and
to validate other data againstConsider an inventory list for a car dealership, loaded as a
blister, this time to a scriptdb local to this spreadsheet
Simple functions on complex list
=blisterData("car_list")
=blisterData("car_list") sorted by price in descending order=blisterData("car_list","price",true)
complex list summarizations
=blisterData("car_list")
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)
complex list summarizations
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 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")
filteringThe 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")
validation
Applying lists as data entry validation
dataValidation You can apply data validation
programmatically in Google Apps Script Any blister can be applied to a range. Data
entered in that range will be validated against the data in the list
Validation can be dependent (dynamically applied according to values already entered in other columns), or non dependent – list values are applied independently to each column
Non dependent validation
{ listName : 'blister.currencies' , blister: [ {listId: 'ISO', sortId: 'ISO' } ] , applies: [ {sheet:'enterCurrencies',
range:'a2:a'} ] } ;
This definition applied on opening workbook
dependent validation
{ listName : 'car_list' , applies: [ {sheet:'enterCars', range:'a2:c'} ] };
This definition applied on opening workbook
On every cell change, the validation is re-evaluated
function onEdit(e) { if(e.range.getDataValidations()) { reApplyIfNeeded (e,generateValidations()); }}
Summary Centralizing lists in shared scriptDB leverages
the availability of cloud based spreadsheets Blisters can be easily created from many
sources – on Excel Liberation website, you can see examples of loads from XML feeds, spreadsheets, REST queries, Fusion tables and other places
API available for customized capabilities Using triggers, you can schedule blister
updates in the cloud – for example currency exchange rates
All source is free and open under creative commons license
More Information For more information, see Excel Liberation
website and Blog