cloud based lists and validation with google apps script

Download Cloud based lists and validation with Google Apps Script

Post on 21-Jan-2015




2 download

Embed Size (px)


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 for more information


  • 1. Cloud based lists and validation Google Apps Script lists Excel Liberation

2. 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 3. carrier aa ba ua name American Airlines British Airways United Airlines Heres 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 4. 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 well just replicate the Excel fragility and sprawl Unless.. Sharing blisters from ScriptDB Lists.. .. Shared scriptDBs Local scriptDBs Lists.. .. Lists.. .. Spreadsheets Lookups and validation 5. Vocabulary carrier aa ba ua name American 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 6. Simple functions Finding, listing, sorting,properties 7. 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 8. 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 9. 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.. 10. 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 11. Integration with other functions Complex lists 12. Complex lists So far weve looked at simple lookup lists We can also use data as a blister lookup and to validate other data against Consider an inventory list for a car dealership, loaded as a blister, this time to a scriptdb local to this spreadsheet 13. Simple functions on complex list =blisterData("car_list") sorted by price in descending order =blisterData("car_list","price",true) 14. complex list summarizations 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) 15. 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") 16. filtering 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") 17. validation Applying lists as data entry validation 18. 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 19. Non dependent validation { listName : 'blister.currencies' , blister: [ {listId: 'ISO', sortId: 'ISO' } ] , applies: [ {sheet:'enterCurrencies', range:'a2:a'} ] } ; This definition applied on opening workbook 20. 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()); } } 21. 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 22. More Information For more information, see Excel Liberation website and Blog