do something useful with Apps Script in 5 minutes

6. Fusion, Crossfilter and SheetsBruce McPhersonwww.mcpher.com

Snippet objectives● Use the lessons learned in ‘using a spreadsheet as a

database’ ● Get data from a Fusion Table● Use Crossfilter to summarize data and write it to a sheet

Libraries used

● database abstraction● driver sheet● crossfilter● driver fusion

Add libraries to script

● create a spreadsheet● get its id● create a script● Open resources● Add references to librariesMHfCjPQlweartW45xYs6hFai_d-phDA33

Mrckbr9_w7PCphJtOzhzA_Cz3TLx7pV4j

MyhWrchJeGiOowTfrMNidiSz3TLx7pV4j

M-tju_1qulZXW63vIuwyLOqi_d-phDA33

Add Fusion service

in advanced services, enable Fusion

Enable Fusion on cloud console

Workaround for fusion issue● When the Fusion API is called from a library, the script

doesn’t know it needs authorization● You need to mention Fusion in your script, even as a

comment to provoke an authorization dialog.● Put this somewhere in your script - amazing but true,

// FusionTables.Table - this will provoke a fusion authorization

layout what you are going to do

function myFunction() {

// open fusion table

// open spreadsheet as database

// get all the data

// load it to crossFilter

// create a states dimension

// create a summary of how many airports in each state

// clear sheet

// write results

}

// FusionTables.Table - this will provoke a fusion authorization

Open Fusion data sourceOpen a Fusion table - this table has a list of all airports in each state

// open fusion table

var fusionHandler = new cDbAbstraction.DbAbstraction (cDriverFusion, {

dbid:'airports',

siloid:'1Ug6IA-L5NKq79I0ioilPXlojEklytFMMtKDNzvA'

});

if (!fusionHandler.isHappy()) throw 'unable to open fusion table';

Open Sheet for resultsOpen a Sheet … we’ll write the results here. Replace the key with the one for the spreadsheet you created

// open spreadsheet as database

var handler = new cDbAbstraction.DbAbstraction (cDriverSheet, {

siloid:'airporcountbystate',

dbid:'1a4oHUWS4sgR_VwjlU6ycyqc0DlUVpKNtEIVXKVwyfLs',

});

if (!handler.isHappy()) throw 'unable to open sheet';

Get all the data from FusionThis will pick up all the airport data from the Fusion Table

// get all the data

var result = fusionHandler.query();

if (result.handleCode < 0) throw result.handleError;

Use crossfilterCrossfilter is cool library for data wrangling. Great for summarizing and grouping.

// load it to crossFilter

var cf = cCrossFilter.crossfilter(result.data);

// create a states dimension

var states = cf.dimension ( function (d) { return d.state });

// write a summary of how many airports in each state

var stateSummary = states.filter(null).group().all();

Write results to a sheetClear any current contents, and write the results out

// clear sheet

var result = handler.remove();

if (handler.handleCode < 0) throw handler.handleError;

var result = handler.save(stateSummary);

if (handler.handleCode < 0) throw handler.handleError;

Homework

Read up about crossfilter and see what other kind of summarization, filtering and analysis you can do with this dataset

Follow up materials

Take a copy of this scriptTake a copy of these slidesJoin me on G+, or the G+ communityMore on desktop liberationMore on database abstractionMore 5 minute things