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)

Constrains and returns an array result to a specified size`ARRAY_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)

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.

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.

`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.

`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`)

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`)

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`)

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.

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`)

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.

`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`)

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
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

library: 1B4T8Ec3Mlmqj91vnn_oqhBfjcEiVl3EcRS_1gIkdeJCDEvy5u5x3jRHT

bmSheet: IDE

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

Sheets Workbook functions – converted to Apps Script