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

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

- 0: Show at most the first

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

# 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

`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

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

`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

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

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

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

- 0: Show at most the first
`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

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

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

`input`

– The values array to filter

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.

You can then call them like this

Calling the same thing from apps script would look like this

# Links

library: 1B4T8Ec3Mlmqj91vnn_oqhBfjcEiVl3EcRS_1gIkdeJCDEvy5u5x3jRHT

bmSheet: IDE

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

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

- Currency exchange rates library for Apps Script – optimized for the parsimonious – swop.cx
- Sheets Workbook functions in Apps Script – Date and Time and Temporal primer
- Sheets Workbook functions in Apps Script – Array functions
- Sheets Workbook functions in Apps Script – Database functions
- Sheets Workbook functions in Apps Script – Filter functions
- SQL for Apps Script – it’s here