Sheets Workbook functions in Apps Script – Database functions

This article will cover the translation of the Sheets workbook database type functions for  Apps Script. All of them will be covered.

Page Content hide
1 Motivation
2 Background
3 Inputs and Outputs
3.1 Example database from the sheets documentation
3.2 Example criteria
4 MathJs
5 GasSql
6 Fiddler
7 Accessing functions
8 Examples
9 Functions
9.1 Shared functions
9.2 Database resusable functions
9.3 Workbook functions
10 Testing as worksheet custom functions
11 Links

Motivation

If you are handling data from sheets it might be useful to use the same logic as is available in the worksheet for common tasks rather than reinventing them.  One of the more complex problems to solve with sheets database functions is the freeform selection criteria. I’m dealing with that by using my bmAlasql library described in SQL for Apps Script – it’s here

There’s a couple of things to note and some TODO’s I’ll get to at some point

  • field names are case insensitive, just like the workbook functions, but I haven’t added a workaround for field names with spaces in them yet – so avoid for now
  • Only strings an numeric types are currently supported in this first release – I’ll add the other types later
  • DCOUNT won’t be entirely accurate if there are empty fields – this is a bug to do with automatically deciding what kind of data should be in a field and the strictness implied when using SQL for querying – workaround is in progress but not in this first version

Background

For some general introduction on implementation see Sheets Workbook functions – converted to Apps Script I must admit that after many, many years of using both Excel and Sheets heavily, I’ve never had cause to use any of these functions, which are some of the less used. Before converting them, I first had to figure out what they were for and how to use them! Actually they are pretty useful in this JavaScript incarnation

Inputs and Outputs

All of these functions take the same 3 arguments (taken from the Sheets documentation)

  • database – The 2d array containing the data to be considered, structured in such a way that the first row contains the labels for each column’s values.
  • field – Indicates which column in database contains the values to be extracted and operated on. field may either be a text label corresponding to a column header in the first row of database or a numeric index indicating which column to consider, where the first column has the value 1.
  • criteria – An array containing zero or more criteria to filter the database values by before operating.
The result is always a single value
 

Example database from the sheets documentation

 

A database is a range where the first row are labels

Key Recipe Category Preparation Calories
1 Caprese Salad Vegetarian 5 200
2 Burrito Eroica Epicurean 10 1900
3 Celery Rawshticks Vegan 3 100
4 Linguine al Pesto Vegetarian 15 400
5 Swiss-Mushroom Burger Epicurean 14 700
6 Nutella Sandwich Vegetarian 3 440
7 Paella Valenciana Epicurean 40 650
8 Risotto Milanese Vegetarian 30 600

Example criteria

The first row of the criteria are labels that correspond to the labels in the database to be queries. Note that they can be repeated to allow complex queries. If there is no op (eg >=) specified, = is assumed

KeyRecipeCategoryPreparationPreparationCalories
<> 2  >= 10<= 20 
     < 500

Each item on each row is anded together, and each row result is orred. So an sql statement representing the above criteria would contain something like this

... where ((key <> 2 and preparation >= 10 and preparation <= 20) or (calories < 500))

MathJs

Behind the scenes I’m using mathjs for some of the math, and it’s exposed from the bmSheet library in case you want to use it directly for anything natively. You can get to it via bmSheet.Mathjs. It’s a great resource if you’re doing complex maths.

GasSql

Alasql and its wrapper are also exposed if you want to use it directly. You can find it at bmSheet.GasSql. For more info on that see SQL for Apps Script – it’s here

Fiddler

Fiddler is also exposed if you want to use that directly. You can it at bmSheet.Fiddler. For more info see A functional approach to fiddling with sheet data or search for fiddler on this site – there are many examples and resources which use fiddler.

Accessing functions

All functions are accessible as follows

const result = bmSheet.functions.dget(database,field, criteria)
accessing functions

Examples

Here’s some test data – a database and two different criteria sets

const arr2d = [['Key', 'Recipe', 'Category', 'Preparation', 'Calories'],
[1, 'Caprese Salad', 'Vegetarian', 5, 200],
[2, 'Burrito Eroica', 'Epicurean', 10, 1900],
[3, 'Celery Rawshticks', 'Vegan', 3, 100],
[4, 'Linguine al Pesto', 'Vegetarian', 15, 400],
[5, 'Swiss-Mushroom Burger', 'Epicurean', 14, 700],
[6, 'Nutella Sandwich', 'Vegetarian', 3, 440],
[7, 'Paella Valenciana', 'Epicurean', 40, 650],
[8, 'Risotto Milanese', 'Vegetarian', 30, 600]]

const labs = ['Key', 'Recipe', 'Category', 'Preparation', 'Calories', 'Calories']

const c1 = [labs,
  ['2', '', 'Epicurean', '', '>1000', '<1900'],
  [3]]

const c2 = [labs, ['', ">=Nutella Sandwich"]]
test data

All of the functions

  console.log(bmSheet.functions.dget(arr2d, 'key', c1))
  console.log(bmSheet.functions.dget(arr2d, 'category', c1))
  console.log(bmSheet.functions.daverage(arr2d, 'calories', c2))
  console.log(bmSheet.functions.dcounta(arr2d, 'preparation', c2))
  console.log(bmSheet.functions.dcounta(arr2d, 'recipe', c2))
  console.log(bmSheet.functions.dmax(arr2d, 'calories', c2))
  console.log(bmSheet.functions.dmin(arr2d, 'key', c2))
  console.log(bmSheet.functions.dproduct(arr2d, 'preparation', c2))
  console.log(bmSheet.functions.dstdev(arr2d, 'Preparation', c2))
  console.log(bmSheet.functions.dstdevp(arr2d, 'Preparation', c2))
  console.log(bmSheet.functions.dsum(arr2d, 'Calories', c2))
  console.log(bmSheet.functions.dvar(arr2d, 'Preparation', c2))
  console.log(bmSheet.functions.dvarp(arr2d, 'Preparation', c2))
calling examples from apps script

Functions

Here’s a short explanation of the implementation code which might be of interest.

Shared functions

Most of the work is done in the _handy namespace for this set of functions as it’s reusable across a range of other workbook functions. If you want to see how these are implemented please take a look at Handy.gs on github.

Database resusable functions

The code for all of these functions are rather short – this is because each function shares the same code to manage the criteria and database and fetch matching rows. With calls to the shared functions in the _handy namespace, this is the main code used by all database functions.

  const prep = ({ arr, criteria }) => {
    // first of all make an sql table and fiddler
    // it's a random name
    const name = randomName()
    const { fiddler, db } = makeDatabase({ name, arr })
    const criteriaFilters = getCriteriaFilters({ criteria, tableDefinitions: db.tableDefinitions })

    // now need 
    return {
      fiddler,
      db,
      name,
      criteriaFilters,
      criteriaWhere: ` where (${criteriaFilters.map(c => '('   c.join(" and ")   ')').join(" or ")})`
    }
  }

  const _dget = ({ arr, field, criteria }) => {
    const {
      name,
      criteriaWhere,
      db
    } = prep({ arr, criteria })

    const fieldName = getFieldName({ field, tableDefinitions: db.tableDefinitions })
    const sql = `select ${fieldName} from ${name}${criteriaWhere}`

    const result = exec({ sql })
    return {
      name,
      result,
      fieldName
    }
  }

  // just pluck out the result from the _dget response
  const oVals = ({ result, fieldName }) => result.map(f => f[fieldName])



  const _dall = (database, field, criteria, treatment) => {
    const response = _dget({ arr: database, field, criteria })
    // don't need the table any more
    dropTable(response)
    // the treatment differs
    return treatment(oVals(response))
  }
shared database code

Workbook functions

These functions are exposed and can be used from apps script.

All of these functions take the same 3 arguments (taken from the Sheets documentation)

  • database – The 2d array containing the data to be considered, structured in such a way that the first row contains the labels for each column’s values.
  • field – Indicates which column in database contains the values to be extracted and operated on. field may either be a text label corresponding to a column header in the first row of database or a numeric index indicating which column to consider, where the first column has the value 1.
  • criteria – An array containing zero or more criteria to filter the database values by before operating.
The result is always a single value
  /**
   * DAVERAGE(database, field, criteria)
   */
  const daverage = (database, field, criteria) => _dall(database, field, criteria, fAverage)


  /**
   * DCOUNT (database, field, criteria)
   * TODO - we're treating empty numeric values as zero so nulls will be counted at the moment
   */
  const dcount = (database, field, criteria) => _dall(database, field, criteria, fCount)


  /**
   * DCOUNTA (database, field, criteria)
   */
  const dcounta = (database, field, criteria) => _dall(database, field, criteria, fCountA)

  /**
   * DMAX (database, field, criteria)
   */
  const dmax = (database, field, criteria) => _dall(database, field, criteria, fMax)

  /**
   * DMIN (database, field, criteria)
   */
  const dmin = (database, field, criteria) => _dall(database, field, criteria, fMin)

  /**
   * DProduct (database, field, criteria)
   */
  const dproduct = (database, field, criteria) => _dall(database, field, criteria, fProduct)

  /**
   * DSTDEV (database, field, criteria)
   */
  const dstdev = (database, field, criteria) => _dall(database, field, criteria, fStdev)

  /**
   * DSTDEVP (database, field, criteria)
   */
  const dstdevp = (database, field, criteria) => _dall(database, field, criteria, fStdevp)

  /**
   * DSUM (database, field, criteria)
   */
  const dsum = (database, field, criteria) => _dall(database, field, criteria, fSum)

  /**
   * dvar (database, field, criteria)
   */
  const dvar = (database, field, criteria) => _dall(database, field, criteria, fVar)

  /**
   * dvarp (database, field, criteria)
   */
  const dvarp = (database, field, criteria) => _dall(database, field, criteria, fVarp)

  /**
   * DGET(database, field, criteria) 
   * returns a single value and will error if there's none or more than 1
  */
  const dget = (database, field, criteria) => _dall(database, field, criteria, (ov) => {
    if (!ov.length) throw new Error('no matches found')
    if (ov.length > 1) throw new Error('more than 1 match found')
    return ov[0]
  })
database workbook functions

 

Testing as worksheet custom functions

Normally these functions are going to be called from Apps Script. There’s no real reason to access these functions as custom functions from a spreadsheet, except that it’s useful to check the results by comparing them against the real thing.

If you want to do this, you can add this code to your container bound script to expose all the functions as custom functions in your sheet.

/**
 * all the functions in the library can be accessed as custom functions
 * @customfunction
 * @param {string} name of the function
 * @param {...*} var_args
 * @return {*}
 */
const custom  = (name, ...args) =>  {
  // this is essential if the script is running in a different timezone to the sheets timezone for custom functions to work in the calling sheet
  bmSheet.customFunctions('settimezone', SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone())
  return bmSheet.customFunctions(name.toLowerCase(), ...args)
}
to expose as container bound custom functions

You can then call them like this

=custom("dvarp",$A$1:$E$9, "Calories",$A$12:$F$14)
as custom function

Calling the same thing from apps script would look like this

  bmSheet.functions.daverage(arr2d, 'calories', c2)

// the result will be a single value
from apps script

Links

library: 1B4T8Ec3Mlmqj91vnn_oqhBfjcEiVl3EcRS_1gIkdeJCDEvy5u5x3jRHT

bmSheet: IDE

github: https://github.com/brucemcpherson/bmSheet

scrviz: https://scrviz.web.app?manifest=brucemcpherson%2FbmSheet%2Fappsscript.json

SQL for Apps Script – it’s here