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

Page Content hide

3 Accessing functions

4 Functions

4.1 filter (input, condition1, condition2, ….)

4.2 sort (input, sort_column, is_ascending, [sort_column,is_ascending]…)

4.3 sortn (input, n, display_ties_mode, sort_column, is_ascending, [sort_column,is_ascending]…)

4.4 unique (input)

4.5 Shared functions

5 Testing as worksheet custom functions

6 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. This section will cover the function types known as FILTER functions

Background and notes

For some general introduction on implementation see Sheets Workbook functions – converted to Apps Script There’s a bit of confusion on some of the more esoteric options for SORTN. The documentation describes a duplicate row as meaning the entire row is Identical, but the the workbook function seems to have been implemented to consider only the sort fields for duplication detection. Either the workbook function or documentation is wrong – at this point I don’t know which. When I find out I may need to tweak SORTN a little.

Here’s what the docs say

display_ties_mode – [OPTIONAL – 0 by default] A number representing the way to display ties.
- 0: Show at most the first n rows in the sorted range.
- 1: Show at most the first n rows, plus any additional rows that are identical to the nth row.
- 2: Show at most the first n rows after removing duplicate rows.
- 3: Show at most the first n unique rows, but show every duplicate of these rows.

For now, I’ve implemented according to the documentation – so I get different results to the actual workbook function for display_ties_mode 1-3

Accessing functions

All functions are accessible as per this example

const result = bmSheet.functions.unique(arr)

accessing functions

Functions

These functions are exposed and can be used from apps script. In each case, the implementation code is shown in case it’s of interest and you think it could be improved, or if I’ve missed any edge cases, then let me know.

The documentation below is adapted from the official Sheets functions writeups.

filter (input, condition1, condition2, ….)

Returns a filtered version of the source range, returning only rows or columns that meet the specified conditions

Sheets Docs Link

input – The values array to filter
condition1 – A values array column containg truthy or falsy values for whether to include the corresponding input row
condition[2…n] – optional: values array column containg truthy or falsy values for whether to include the corresponding input row

condition arguments must have exactly the same length as range, and every condition needs to be satified to be included

  const filter = (...args) => {
    // must be a 2d
    const [arr, ...cargs] = args
    check2d(arr)

    // we'll allow single arrays (so that arrayformula() will work for custom)
    const conds = forceArray(cargs).map(f => check2d(f, true))

    if (!conds.every(c => {
      return numRows(arr) === numRows(c) && numCols(c) === 1
    })) {
      throw new Error('number of rows in condition(s) dont match')
    }
    const fatten = conds.map(i => i.flat())
    return arr.filter((_, i) => fatten.every(c => c[i]))

  }

filter

sort (input, sort_column, is_ascending, [sort_column,is_ascending]…)

Sorts the rows of a given array or range by the values in one or more columns.

Sheets Docs Link

input – The values array to sort
sort_column – The values array to sort on
- A sort_column must be a single column with the same number of rows as range, or the column index (starting at 1) of the input array
is_ascending – TRUE or FALSE indicating whether to sort sort_column in ascending order. FALSE sorts in descending order.
sort_column2, is_ascending2 ...
- [ OPTIONAL ] – Additional columns and sort order flags beyond the first, in order of precedence.

  const sort = (arr, ...pairs) => {
    // make sure the arr is correct shape
    check2d(arr)

    // fix up the pairings
    const sortDef = pairs.reduce((p, c, i) => {
      const isOrder = i % 2
      if (!isOrder) p.push({})
      const active = p[p.length - 1]
      if (isOrder) {
        active.isAscending = (i % 2 && (isEmpty(c) || c)) || false
      } else {
        active.sortColumn = typeof c === 'number' ? transpose(check2d(arr)) : check2d(c, true).flat()
      }
      return p
    }, [])

    const boo = (b) => b ? 1 : -1
    const extract = (item, sortColumn) => sortColumn[item.index]
    const compare = (a, b, isAscending = true) => a > b ? boo(isAscending) : (a === b ? 0 : -1 * boo(isAscending))

    // we'll attach a row index so we can use the auxiliary columns for sorting
    return arr.map((row, index) => ({
      row,
      index
    })).sort((a, b) => {

      let c = 0;
      for ({ sortColumn, isAscending } of sortDef) {
        c = compare(extract(a, sortColumn), extract(b, sortColumn), isAscending)
        if (c) break
      }
      return c

    }).map(r => r.row)

  }

sort

sortn (input, n, display_ties_mode, sort_column, is_ascending, [sort_column,is_ascending]…)

Returns the first n items in a data set after performing a sort.

Sheets Docs Link

input – The values array to sort
n – [OPTIONAL – 1 by default] The number of items to return. Must be greater than 0.
display_ties_mode – [OPTIONAL – 0 by default] A number representing the way to display ties.
- 0: Show at most the first n rows in the sorted range.
- 1: Show at most the first n rows, plus any additional rows that are identical to the nth row.
- 2: Show at most the first n rows after removing duplicate rows.
- 3: Show at most the first n unique rows, but show every duplicate of these rows.
sort_column – The values array to sort on
- A sort_column must be a single column with the same number of rows as range, or the column index (starting at 1) of the input array
is_ascending – TRUE or FALSE indicating whether to sort sort_column in ascending order. FALSE sorts in descending order.
sort_column2, is_ascending2 ...
- [ OPTIONAL ] – Additional columns and sort order flags beyond the first, in order of precedence.

See implementation notes for display_ties_mode at the beginning of this article for some confusion between workbook docs and apparent implementation

  const sortn = (arr,n=1,display_ties_mode = 0, ...pairs) => {
    
    if (n<1) throw new Error('must be at least 1 row in request')

    // start by sorting things
    const sorted = sort(arr,...pairs)
    const sliced = sorted.slice(0,n)

    // now we need to apply the various tweaks
    // thats' the easiest - just return the first n rows
    if (!display_ties_mode) return sliced

    // slice the first n rows, plus any others that match the last row
    if(display_ties_mode ===1) {
      const last = dStringify(sliced.slice(-1)[0])
      const extras = sorted.slice(n).filter(row=>dStringify(row) === last)
      return sliced.concat(extras)
    }

    // dedup
    if (display_ties_mode ===2) {
      // we have to remove the dups from the sorted and return up to n of them
      return unique(sorted).slice(0,n)
    }

    // dedup   duplicate of these rows
    if (display_ties_mode ===3) {
      const u = unique(sorted).slice(0,n)
      // then filter to add any dups
      return sorted.filter(row=> {
        const t = dStringify(row)
        return u.find(f=>dStringify(f) === t)
      })
    }
    throw new Error(`unknown display_ties_mode ${display_ties_mode}`)
  }

sortn

unique (input)

Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.

Sheets Docs Link

input – The values array to filter

const unique = (arr) => [...new Map(check2d(arr).map(f => ([dStringify(f), f]))).values()]

unique

Shared functions

Some of the work is done in the _handy namespace if 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.

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