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.

Page Content hide

2 Functions

2.1 Vanilla SQL

2.1.1 Creating an empty table

2.1.2 Inserting data

2.1.3 Executing

2.2 Deducing the table structure

2.2.1 Inserting from an array of JSON objects

2.2.2 Inserting and creating

2.3 Using fiddler

2.3.1 Joins

2.3.2 Manipulating sheets

2.3.3 Joining

3 Summary

4 Links

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