This article will cover the translation of the Sheets workbook Array type functions for  Apps Script. Most of them will be covered, except for a few exotic ones that I might do later if anybody asks, and some are partial implementation omitting some of the edge case options.

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.  Some of the array functions for Matrix handling and forecasting are quite tricky, so if you need to do any of that in Apps Script, these might be handy.

Background

For some general introduction on implementation see Sheets Workbook functions – converted to Apps Script

Inputs and Outputs

All inputs to these formulas should be in spreadsheet values format – ie [[x,x..],[y,y..]]. In some cases, these might be flattened where a single result is expected (eg sumproduct), but any results that are arrays will be in the same 2d array format.

ArrayFormula

The Arrayformula function provides a way to repeat operations across an array from a spreadsheet. It’s esentially just a rows.map(…) type operation so I haven’t implemented anything for that as it would end up being more complex that just doing it in native Apps Script.

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.

Accessing functions

All functions are accessible as follows

const result = bmSheet.functions.sumproduct([[1,2,3],[3,4,5]],[[0,2,1],[99,2,1]], 20)
accessing functions

Functions

I’ll show how to call them, as well as the implementation code which might be of interest.

Handy functions

There are a couple of handy functions that are used throughout.

// some utilities
var _handy = (() => {

  const isUndefined = (a) => typeof a === typeof undefined

  const forceArray = (a) => Array.isArray(a) ? a : [a]

  const flatten = (...args) => forceArray(args).map(f => forceArray(f).flat()).flat()

  const normalizeLengths = (arr) => {
    // normalize the argument array dimensions
    let items = arr.map(f => flatten(f))
    const maxLength = Math.max(...items.map(d => d.length))
    // blow out those of length 1
    items = items.map(f => f.length === 1 ? Array.from({ length: maxLength }).fill(f[0]) : f)
    // check they are all the same length
    if (!items.every(d => d.length === maxLength)) throw new Error("all arrays should be the same length or 1")
    return items
  }



  return {
    forceArray,
    asNumber: (a) => Number(a),
    isUndefined,
    fixOptional: (arg, defaultValue) => isUndefined(arg) ? defaultValue : arg,
    normalizeLengths,
    flatten
  }


})()
handy functions

Workbook functions

These functions are exposed and can be used from apps script. The documentation below is adapted from the official Sheets functions writeups.

array_constrain (input, num_rows, num_cols)

Sheets Docs Link

Constrains and returns an array result to a specified sizeARRAY_CONSTRAIN(input, num_rows, num_cols)

  • input – The values array to constrain
  • num_rows – The number of rows the result should contain.
  • num_cols – The number of columns the result should contain
  const array_constrain = (input_range, num_rows, num_cols) =>
    _handy.forceArray(input_range).slice(0, num_rows).map(r => r.slice(0, num_cols))
array_constrain

frequency (data, classes)

Sheets Docs Link

Calculates the frequency distribution of a one-column array into specified classes.

FREQUENCY(data, classes)

  • data – The array containing the values to be counted.
  • classes – The array or range containing the set of classes.

Notes

  • The output of FREQUENCY will be a vertical range of size one greater than classes as the final value is the number of elements in data greater than any of the class boundaries.
  const frequency = (data, classes) => {

    // the extra one at the end for those that are too big
    const keys = flatten(classes).sort().concat([null])
    const freqs = [...keys].fill(0)
    flatten(data).forEach(d => {
      let i = 0;
      while (i < keys.length - 1 && d > keys[i]) i++;
      freqs[i]++
    })
    return freqs.map(k => [k])
  }
frequency

 

growth (known_data_y, [known_data_x], [new_data_x], [b])

This is a partial implementation – it flattens all arrays, so can only deal with 1 dependent variable at once, unlike the sheet function which has an option to handle multiple dependent variables.

Sheets Docs Link

Given partial data about an exponential growth trend, fits an ideal exponential growth trend and/or predicts further values.

GROWTH(known_data_y, [known_data_x], [new_data_x], [b])

  • known_data_y – The array or range containing dependent (y) values that are already known, used to curve fit an ideal exponential growth curve.
    • If known_data_y is a two-dimensional array or range, known_data_x must have the same dimensions or be omitted.
  • known_data_x – [ OPTIONAL – {1,2,3,...} with same length as known_data_y by default ] – The values of the independent variable(s) corresponding with known_data_y.
  • new_data_x – [ OPTIONAL – same as known_data_x by default ] – The data points to return the y values for on the ideal curve fit.
    • The default behavior is to return the ideal curve fit values for the same x inputs as the existing data for comparison of known y values and their corresponding curve fit estimates.
  • b – [ OPTIONAL – TRUE by default ] – Given a general exponential form of y = b*m^x for a curve fit, calculates b if TRUE or forces b to be 1 and only calculates the m values if FALSE.
  /**
   * note this flattens all arrays, so can only deal with 1 dependent variable at once
   * GROWTH(known_data_y, [known_data_x], [new_data_x], [b])
   */
  const growth = (known_y, known_x, new_x, use_const) =>
    _soGrowth(flatten(known_y), flatten(known_x), flatten(new_x), use_const)
  
  //https://stackoverflow.com/questions/14161990/how-to-implement-growth-function-in-javascript
  function _soGrowth(known_y, known_x, new_x, use_const) {
    // default values for optional parameters:
    if (typeof (known_x) == 'undefined') {
      known_x = [];
      for (var i = 1; i <= known_y.length; i++) known_x.push(i);
    }
    if (typeof (new_x) == 'undefined') {
      new_x = [];
      for (var i = 1; i <= known_y.length; i++) new_x.push(i);
    }
    if (typeof (use_const) == 'undefined') use_const = true;

    // calculate sums over the data:
    var n = known_y.length;
    var avg_x = 0; var avg_y = 0; var avg_xy = 0; var avg_xx = 0;
    for (var i = 0; i < n; i++) {
      var x = known_x[i]; var y = Math.log(known_y[i]);
      avg_x += x; avg_y += y; avg_xy += x * y; avg_xx += x * x;
    }
    avg_x /= n; avg_y /= n; avg_xy /= n; avg_xx /= n;

    // compute linear regression coefficients:
    if (use_const) {
      var beta = (avg_xy - avg_x * avg_y) / (avg_xx - avg_x * avg_x);
      var alpha = avg_y - beta * avg_x;
    } else {
      var beta = avg_xy / avg_xx;
      var alpha = 0;
    }
    // console.log("alpha = " + alpha + ", beta = " +  beta);

    // compute and return result array:
    var new_y = [];
    for (var i = 0; i < new_x.length; i++) {
      new_y.push(Math.exp(alpha + beta * new_x[i]));
    }
    return new_y;
  }
growth

 

linest (known_data_y, [known_data_x], [calculate_b])

This is a partial implementation, only supports 1 dependent variable and has no verbose option.

Sheets Docs Link

LINEST(known_data_y, [known_data_x], [calculate_b])

Given partial data about a linear trend, calculates various parameters about the ideal linear trend using the least-squares method.

  • known_data_y – The array or range containing dependent (y) values that are already known, used to curve fit an ideal linear trend.
    • If known_data_y is a two-dimensional array or range, known_data_x must have the same dimensions or be omitted.
  • known_data_x – [ OPTIONAL – {1,2,3,...} with same length as known_data_y by default ] – The values of the independent variable(s) corresponding with known_data_y.
  • calculate_b – [ OPTIONAL – TRUE by default ] – Given a linear form of y = m*x+b, calculates the y-intercept (b) if TRUE. Otherwise, forces b to be 0 and only calculates the m values if FALSE, i.e. forces the curve fit to pass through the origin.
  const linest = (known_data_y, known_data_x) =>
    _soLeastSquaresFitLinear(flatten(known_data_y), flatten(known_data_x))
  
  
    // https://stackoverflow.com/questions/7437660/how-do-i-recreate-an-excel-formula-which-calls-trend-in-c
  function _soLeastSquaresFitLinear(known_y, known_x, offset_x = 0) {
    if (known_y.length != known_x.length) {
      throw new Error('known y and known x arrays are unequal lengths')
    }
    var numPoints = known_y.length;

    var x1 = 0, y1 = 0, xy = 0, x2 = 0, J, M, B;
    for (var i = 0; i < numPoints; i++) {
      known_x[i] -= offset_x;
      x1 = x1 + known_x[i];
      y1 = y1 + known_y[i];
      xy = xy + known_x[i] * known_y[i];
      x2 = x2 + known_x[i] * known_x[i];
    }

    J = (numPoints * x2) - (x1 * x1);
    if (J != 0.0) {
      M = ((numPoints * xy) - (x1 * y1)) / J;
      B = ((y1 * x2) - (x1 * xy)) / J;
    }
    return [M, B];
  }
linest

 

logest (known_data_y, [known_data_x])

This is a partial implementation, only supports 1 dependent variable and has no verbose option.

Sheets Docs Link

LOGEST(known_data_y, [known_data_x])

Given partial data about an exponential growth curve, calculates various parameters about the best fit ideal exponential growth curve.

  • known_data_y – The array or range containing dependent (y) values that are already known, used to curve fit an ideal linear trend.
    • If known_data_y is a two-dimensional array or range, known_data_x must have the same dimensions or be omitted.
  • known_data_x – [ OPTIONAL – {1,2,3,...} with same length as known_data_y by default ] – The values of the independent variable(s) corresponding with known_data_y.
  const logest = (known_y, known_x) => {

    // get the slope
    const ls = linest(flatten(known_y).map(d => Math.log(d)), known_x)

    return [[
      Math.exp(ls[0]),
      Math.exp(ls[1])
    ]]

  }
logest

 

mdeterm (square_matrix)

Sheets Docs Link

Returns the matrix determinant of a square matrix specified as an array or range.

MDETERM(square_matrix)

  • square_matrix – An array or range with an equal number of rows and columns representing a matrix whose determinant will be calculated.
  const mdeterm = (arr) => {
    if (!arr || !arr[0] || arr.length !== arr[0].length) throw new Error('matrix should be square')
    return Mathjs.det(arr)
  }
mdeterm

minverse (square_matrix)

Sheets Docs Link

Returns the multiplicative inverse of a square matrix specified as an array or range.

MINVERSE(square_matrix)

  • square_matrix – An array or range with an equal number of rows and columns representing a matrix whose multiplicative inverse will be calculated.
  const minverse = (arr) => {
    if (!arr || !arr[0] || arr.length !== arr[0].length) throw new Error('matrix should be square')
    return Mathjs.inv(arr)
  }
minverse

mmult (matrix1, matrix2)

Sheets Docs Link

Calculates the matrix product of two matrices specified as arrays or ranges.

MMULT(matrix1, matrix2)

  • matrix1 – The first matrix in the matrix multiplication operation, represented as an array or range.
  • matrix2 – The second matrix in the matrix multiplication operation, represented as an array or range.

Notes

  • As standard in matrix multiplication, the number of columns for matrix1 must equal the number of rows for matrix2
  const mmult = (a, b) => {
    const ca = a && a[0] && a[0].length
    const rb = b && b.length
    if (ca !== rb) throw new Error('the number of columns for matrix1 must equal the number of rows for matrix2')
    return Mathjs.multiply(a, b)
  }
mmult

sumproduct (array1, [array2, ...])

This function returns a single result. I’ve made it a little more flexible than the sheet function as you can provide a single value rather than an array that each element will be multiplied by, and all the arrays are flattened, so they don’t have to be the same shape – just have the same number of total elements. This will allow it to be used on more than just spreadsheet shaped data.

Sheets Docs Link

The SUMPRODUCT function calculates the sum of the products of corresponding entries in 2 equally sized arrays or ranges.

SUMPRODUCT(array1, [array2, ...])

  • array1 – The first array whose entries will be multiplied with corresponding entries in the second such array or range.
  • array2, ... – [ OPTIONAL – {1,1,1,...} with same length as array1 by default ] – The second array whose entries will be multiplied with corresponding entries in the first such array or range.
  const sumproduct = (...args) => {
    const items = _handy.normalizeLengths(args)
    return Array.from({ length: items[0].length }).map((d, i) =>
      items.reduce((p, c) => p * Number(c[i]), 1)).reduce((p, c) => p + c, 0)
  }
sumproduct

transpose (array)

Sheets Docs Link

Transposes the rows and columns of an array or range of cells.

TRANSPOSE(array)

  • array – The array or range whose rows and columns will be swapped.

Notes

  • Transposition operates such that the value in the nth row and mth column will become the value in the mth row and nth column. E.g. the value in the fourth row and second column will be put into the second row and fourth column. The result of a transposition on a range of size m rows by n columns is therefore n rows by m columns.

  const transpose = (arr2d) => arr2d[0].map((_, col) => arr2d.map(row => row[col]));
hour

trend (known_data_y, [known_data_x], [new_data_x], [b])

This is a partial implementation, only supports 1 dependent variable.

Sheets Docs Link

TREND(known_data_y, [known_data_x], [new_data_x])

  • known_data_y – The array or range containing dependent (y) values that are already known, used to curve fit an ideal linear trend.

    • If known_data_y is a two-dimensional array or range, known_data_x must have the same dimensions or be omitted.
  • known_data_x – [ OPTIONAL – {1,2,3,...} with same length as known_data_y by default ] – The values of the independent variable corresponding with known_data_y.
  • new_data_x – [ OPTIONAL – same as known_data_x by default ] – The data points to return the y values for on the ideal curve fit.
    • The default behavior is to return the ideal curve fit values for the same x inputs as the existing data for comparison of known y values and their corresponding curve fit estimates.
  const trend = (known_y, known_x, new_x) => {

    // get the slope
    const ls = linest(known_y, known_x)

    // apply it
    return (flatten(new_x || known_x)).map((d, _, a) => ls[0] * d + ls[1])

  }
trend

flatten (array)

Sheets Docs Link

Flattens all the values from one or more ranges into a single column.

Parts of a FLATTEN function

=FLATTEN(range1, [range2, …])

Part Description
range1 The first range to flatten.
range2 [optional] repeatable

Additional ranges to flatten.

Notes

  • Values are ordered by argument, then row, then column. So, the entire first row of an input is added before the second row (also known as row-major order).
  • Empty values are not skipped; the FILTER function can be used to remove those.

  // for use inside this namespace
  const flatten = (...args) => _handy.flatten(...args)
 
  // we're putting it into a column so it needs decoration
  // this is the functin that's exposed
  const exportFlatten = (...args) => flatten(...args).map(f=>[f])
flatten

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("flatten",I25:K27,I25:K27)
as custom function

Calling the same thing from apps script would look like this

// assuming that you have already done a getValues() on ranges I25:K27,I25:K27
const result = bmSheet.flatten(a,b)

// the result will be in values format
// [[x],[x],[x],.. etc]
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

Temporal – dates, times and timezones and the proposed new Date system for ECMAScript

Sheets Workbook functions – converted to Apps Script