While working on Sheets Workbook functions – converted to Apps Script I realized that I’d need to tackle the query language at some point, so I figured I may as well go the whole hog and implement a comprehensive SQL variant for Apps Script. Luckily though, I found alasql so with a few tweaks it was ready to go – it’s available through my library bmAlasql
I also wanted to make it compatible with my fiddler library, as many of you already use it for fiddling with Sheets data. Here’s how it all works and fits together. There’s still a bit of work to do but it’s good enough to get started with.
Getting started
You’ll need
bmAlasql (1OcbRxA2lKJfR49RtAzl_5XN2_jnT6ByzJRKRihv8VXf1leGEGevlLzYf)
and if you’re planning to use fiddler, you’ll need either the vanilla fiddler library
bmFiddler (13EWG4-lPrEf34itxQhAQ7b9JEbmCBfO8uE4Mhr99CHi3Pw65oxXtq-rU)
or the handy wrapper
bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR)
Let’s start with using bmAlasql directly – that will allow you to create sql tables in memory and play with them directly from Apps Script
Functions
These functions are currently exported
const {
exec,
createTable,
createTableFromData,
insert,
insertFromData,
createAndInsertFromData
} = bmAlasql.GasSql
bmAlasql.GasSql exports
Vanilla SQL
Creating an empty table
not all SQL data types are yet supported but I’ll be adding them over time.
// create a table manually
createTable({ name: 'depts3', tableDefinitions: new Map([['dept', 'string'], ['salary', 'number']]) })
creating an empty table
Inserting data
Once you’ve created a table, you can insert data like this.
insert({ name: 'depts3', values: [['a', 100], ['b', 2000]] })
inserting data
Executing
Most SQL statements are available via the exec function – and we’ll look at some examples later. The most basic is just to select what’s in the table
exec({ sql: "select * from depts3" })
// result
// [ { dept: 'a', salary: 100 }, { dept: 'b', salary: 2000 } ]
exec
Deducing the table structure
Since in Apps Script we’ll mainly be dealing with either spreadsheet values or object arrays, we need a way to quickly create a table from either of these without fiddling around with SQL syntax.
Creating table from an array of JSON objects
// you can use a json array to deduce the table structure
const data = [{ dept: 'a', salary: 100 },
{ dept: 'b', salary: 200 },
{ dept: 'b', salary: 300 }]
createTableFromData({
name: 'depts2',
data
})
from array of json
Inserting from an array of JSON objects
We can use the same or different data to insert into the created table
// and then populate it using the same or different data
insertFromData({ name: 'depts2', data })
inserting from array of json objects
Here’s a query on that data
exec({ sql: 'select dept, sum(salary) as total_salary from depts2 group by dept' })
// result
/*
[ { dept: 'a', total_salary: 100 },
{ dept: 'b', total_salary: 500 } ]
*/
example query
Inserting and creating
There’s a short cut to do both of that at the same time – create and populate a table
createAndInsertFromData({name: 'depts1' , data})
create a table and populate it in one shot
Using fiddler
If you’re dealing with spreadsheet data, it may be more convenient just to use fiddler – especially if you’re reading and writing to sheets too. Lets’s start with arrays of spreadsheet values.
// you can use fiddler to get values from a sheet - this example simulates data without a sheet
const fiddler = new bmFiddler.Fiddler().setValues([['dept', 'salary'], ['a', 100], ['b', 200], ['b', 300]])
createAndInsertFromData({ name: 'depts', data: fiddler.getData() })
fiddler sheet simulation
and here’s a query on that
exec({ sql: 'select dept, avg(salary) as average_salary from depts group by dept' })
// result
/*
[ { dept: 'a', average_salary: 100 },
{ dept: 'b', average_salary: 250 } ]
*/
exec({ sql: "select * from depts where salary > 200" })
/**
[ { dept: 'b', salary: 300 } ]
*/
example queries
Joins
SQL joins are supported too. Let’s create another table
createAndInsertFromData({
name: 'descriptions',
data: [{
dept: 'a',
label: 'accounts'
}, {
dept: 'b',
label: 'it'
}, {
dept: 'c',
label: 'engineering'
}]
})
joins
Now we can join that to another table
exec({
sql: "select descriptions.label,sum(depts.salary) as total_salary from depts left join descriptions on descriptions.dept = depts.dept group by descriptions.label"
})
// result
/*
[ { label: 'accounts', total_salary: 100 },
{ label: 'it', total_salary: 500 } ]
*/
join result
Manipulating sheets
Now let’s assume that you want to use sql to do things with sheet data – Again I’ll use fiddler here, pulling in a list of all the airports in the world.
const airports = bmPreFiddler.PreFiddler().getFiddler({
id: '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ',
sheetName: 'airport list'
})
fiddler for airport list
Let’s say we wanted to create a new sheet with just the airports in the world that are in the southern hemisphere and are at an elevation above 6000 ft. With fiddler that’s pretty easy.
const airports = bmPreFiddler.PreFiddler().getFiddler({
id: '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ',
sheetName: 'airport list'
})
const highAirports = bmPreFiddler.PreFiddler().getFiddler({
id: airports.getSheet().getParent().getId(),
sheetName: 'high airports in the south',
createIfMissing: true
})
// filtering using fiddler
airports.filterRows(row => row.elevation_ft > 6000 && row.latitude_deg < 0).dumpValues(highAirports.getSheet())
filtering with fiddler
And here’s the result
We can now do the same thing with SQL
const airports = bmPreFiddler.PreFiddler().getFiddler({
id: '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ',
sheetName: 'airport list'
})
const highAirports = bmPreFiddler.PreFiddler().getFiddler({
id: airports.getSheet().getParent().getId(),
sheetName: 'high airports in the south',
createIfMissing: true
})
// filtering using fiddler
createAndInsertFromData({ name: 'airports', data: airports.getData() })
highAirports.setData(exec({sql: 'select * from airports where elevation_ft > 6000 and latitude_deg < 0'})).dumpValues()
using sql for filtering
That may not seem like a massive improvement over vanilla fiddler, but let’s say we wanted to miss out some fields, do some averaging and grouping – the whole world of sql syntax is available.
const airports = bmPreFiddler.PreFiddler().getFiddler({
id: '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ',
sheetName: 'airport list'
})
const avgElevation = bmPreFiddler.PreFiddler().getFiddler({
id: airports.getSheet().getParent().getId(),
sheetName: 'average elevations by country',
createIfMissing: true
})
createAndInsertFromData({ name: 'airports', data: airports.getData() })
avgElevation.setData(exec({
sql: 'select iso_country,avg(elevation_ft) as average_elevation from airports where latitude_deg < 0 group by iso_country'
})).dumpValues()
sql grouping
Joining
SQL makes it easy to join across tables too. Let’s say we have 2 sheets – one the list of airports and another with the average elevation of all airports in each each country. We want to calculate, and create a sheet, showing for each airport, the percentage of the airport’s average elevation for the country it’s in. So for example if the average airport in a country is 1000 ft, an airport at 2000ft in that country would be 200%.
const airports = bmPreFiddler.PreFiddler().getFiddler({
id: '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ',
sheetName: 'airport list'
})
const avgElevation = bmPreFiddler.PreFiddler().getFiddler({
id: airports.getSheet().getParent().getId(),
sheetName: 'average elevations by country',
})
// create an SQL table for each sheet
createAndInsertFromData({ name: 'airports', data: airports.getData() })
createAndInsertFromData({ name: 'elevations', data: avgElevation.getData() })
// we'll write the result out here
const join = bmPreFiddler.PreFiddler().getFiddler({
id: airports.getSheet().getParent().getId(),
sheetName: 'join',
createIfMissing: true
})
join.setData(exec({
sql:
'select airports.name,airports.iso_country,airports.elevation_ft/elevations.average_elevation as pc_avg_elevation from airports left join elevations on elevations.iso_country = airports.iso_country order by elevations.iso_country'
})).dumpValues()
joining and caculating
result
Summary
These examples just scratch the surface of what you can do using SQL within Apps Script (with or without Sheets being involved), so no doubt there will be more articles on this subject.
Links
bmAlasql (1OcbRxA2lKJfR49RtAzl_5XN2_jnT6ByzJRKRihv8VXf1leGEGevlLzYf)
and if you’re planning to use fiddler, you’ll need either the vanilla fiddler library
bmFiddler (13EWG4-lPrEf34itxQhAQ7b9JEbmCBfO8uE4Mhr99CHi3Pw65oxXtq-rU)
or the handy wrapper
bmPreFiddler (13JUFGY18RHfjjuKmIRRfvmGlCYrEkEtN6uUm-iLUcxOUFRJD-WBX-tkR)
All are also on github under github.com/brucemcpherson
