Transcript
Page 1: Cloud based lists and validation with Google Apps Script

Cloud based lists and validation

Google Apps Script lists Excel Liberation

Page 2: Cloud based lists and validation with Google Apps Script

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

Page 3: Cloud based lists and validation with Google Apps Script

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

Page 4: Cloud based lists and validation with Google Apps Script

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

Page 5: Cloud based lists and validation with Google Apps Script

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

Page 6: Cloud based lists and validation with Google Apps Script

Simple functions

Finding, listing, sorting,properties

Page 7: Cloud based lists and validation with Google Apps Script

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

Page 8: Cloud based lists and validation with Google Apps Script

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

Page 9: Cloud based lists and validation with Google Apps Script

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..

Page 10: Cloud based lists and validation with Google Apps Script

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

Page 11: Cloud based lists and validation with Google Apps Script

Integration with other functions

Complex lists

Page 12: Cloud based lists and validation with Google Apps Script

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

Page 13: Cloud based lists and validation with Google Apps Script

Simple functions on complex list

=blisterData("car_list")

=blisterData("car_list") sorted by price in descending order=blisterData("car_list","price",true)

Page 15: Cloud based lists and validation with Google Apps Script

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")

Page 16: Cloud based lists and validation with Google Apps Script

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") 

Page 17: Cloud based lists and validation with Google Apps Script

validation

Applying lists as data entry validation

Page 18: Cloud based lists and validation with Google Apps Script

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

Page 19: Cloud based lists and validation with Google Apps Script

Non dependent validation

{ listName : 'blister.currencies' , blister: [ {listId: 'ISO', sortId: 'ISO' } ] , applies: [ {sheet:'enterCurrencies',

range:'a2:a'} ] } ;

This definition applied on opening workbook

Page 20: Cloud based lists and validation with Google Apps Script

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()); }}

Page 21: Cloud based lists and validation with Google Apps Script

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


Top Related