|
_CONFIG |
Holds configuration values |
{
"Version","0.0.2";
"Datetime.Workweek.DayNumbers","2,3,4,5,6"
} |
|
Protected |
Main |
|
_ITERATE_REVERSE |
PRIVATE Iterator for REVERSE(). Do not use this directly. |
IF(
IS_RANGE(origin),
LET(
head, HEAD(origin),
rest, TAIL(origin),
new_target, PUSH(head, target),
_ITERATE_REVERSE(rest, new_target)
),
PUSH(origin, target)
) |
origin:
target: |
Private |
Enum |
|
_ITERATE_UPTO |
PRIVATE Recursive incrementing iterator for RANGE(). Do not use this directly. |
IF(
current <= limit,
LET(
new_set, PUSH(set, current),
new_current, current + 1,
_ITERATE_UPTO(new_set, new_current, start, limit)
),
set
) |
set:
current:
start:
limit: |
Private |
Enum |
|
_MAP_WITH_INDEX |
PRIVATE Recursive iterator for MAP_WITH_INDEX(). Do not use this directly. |
IF(
index_int > size,
output,
LET(
element, INDEX(input, index_int),
mapped, block(element, index_int),
output, PUSH(output, mapped),
_MAP_WITH_INDEX(index_int + 1, size, input, output, block)
)
) |
index_int:
size:
input:
output:
block: |
Private |
Enum |
|
ALL |
Checks whether every element meets a given criterion.
Examples:
ALL({1,2,3}, lambda(x, x > 0)) => TRUE
ALL({1,2,3}, lambda(x, x > 1)) => FALSE |
LET(
mapped, MAP(array, block),
count, COUNTALL(array),
true_count, COUNTIF(mapped, TRUE),
count = true_count
) |
array:
type: range (1D)
description: A list whose elements will all be run against the condition
example: {2, 4, 6, 8}
block:
type: lambda
description: A lambda containing the criterion
example: lambda(x, IS_EVEN(x)) |
Public |
Enum |
|
ANY |
Checks if any element in an array matches a given criterion.
Example:
ANY({1,2,3}, lambda(x, x > 2)) => TRUE |
IS_IN(TRUE, MAP(array, block)) |
array:
type: range (1D)
description:
example: {3, 2, 2, 2}
block:
type: lambda
description: A lambda containing the criterion
example: lambda(x, x > 2) |
Public |
Enum |
|
BLANK |
Returns a blank value. Useful as a default/fallback value in lookups, etc.
Example: ISBLANK(BLANK()) => TRUE |
IF(true, , ) |
|
Public |
|
|
CAMELIZE |
Converts snake_case (underscored) text to PascalCase (similar to camel case).
Example:
CAMELIZE("civic_tech") => CivicTech |
JOIN("", MAP(SPLIT(text,"_"), lambda(word, CAPITALIZE(word)))) |
text:
type: text
description: Snake case text
example: "this_will_be_camel_case" |
Public |
Text |
|
CAPITALIZE |
Capitalizes the first letter of a word.
Example:
CAPITALIZE("hello") => "Hello" |
LET(
first_letter, UPPER(FIRST(text)),
rest, MID(text, 2, LEN(text)),
CONCAT(first_letter, rest)
) |
text:
type: text
description: A word to capitalize
example: "cher" |
Public |
Text |
|
CATCH |
Catches any error, and returns the error handler. The error handler can be a scalar value (`0` in the first example below) or a lambda function as in the second example.
Examples:
CATCH(1/0, 0) => 0
CATCH(1/0, lambda(err, 0)) => 0 |
LET(
true_error_handler, IF(
ISNA(error_handler),
error_handler(maybe_error),
error_handler
),
IF(
IS_ERROR(maybe_error),
true_error_handler,
maybe_error
)
) |
maybe_error:
type: any
description: A reference or formula that might result in an error
example: 1/0
error_handler:
type: any | lambda
description: The error handler. Either a value, or a lambda which takes one argument, the error
example: 0 |
Public |
Error |
|
CATCH_IF |
Catches an error, but only if the error is the specified type. Returns the fallback value (as in the first two examples) or a lambda function that takes the error as an argument (as in the second two examples).
Examples:
With a fallback value:
CATCH(1/0,"DIV/0", 0) => 0
CATCH(1/0,"REF", 0) => #DIV/0!
With an error handler lambda:
CATCH(1/0,"DIV/0", lambda(err, 0)) => 0
CATCH(1/0,"REF", lambda(err, 0)) => #DIV/0!
For example, if you catch a divide-by-zero error (#DIV/0!) and set an error type of "DIV/0" (or 2), the error will be caught. If you set the error type to "REF" (or 4), the handler won't run and you'll see the original error. |
IF(IS_ERROR(maybe_error),
IF(ERROR.TYPE(maybe_error)=ERROR.TYPE(RAISE(error_type)),
CATCH(maybe_error, error_handler),
maybe_error
),
maybe_error
) |
maybe_error:
type: any
description: Reference or formula that might be an error
example: 1/0
error_type:
type: integer | text (error nickname or code)
description: The type of error, specified by code (number) or name (text). See RAISE documentation for codes and names.
example: "DIV/0"
error_handler:
type: any | lambda
description: The error handler value or lambda which takes one argument
example: 0 |
Public |
Error |
|
CHAR_TO_NUM |
Converts a single letter to a number. Useful when doing "column math". For two-letter column references, see COL_TO_NUM.
Examples:
CHAR_TO_NUM("A") => 1
CHAR_TO_NUM("Z") => 26 |
IF(
LENGTH(TO_S(letter)) > 1,
RAISE("N/A"),
MATCH(letter, RANGE("A","Z"))
) |
letter:
type: character
description: A letter to convert to a number
example: "A" |
Public |
|
|
CHARS |
Turn a string of text into an array of characters.
Example:
CHARS("hello") => {"h","e","l","l","o"} |
LET(
just_string, TO_S(string),
MAKEARRAY(
LENGTH(just_string),
1,
lambda(x,y, MID(just_string, x, 1))
)
) |
string:
type: text
description: A string of text
example: "hello" |
Public |
Text |
|
CLIP |
Returns a string that with characters clipped off the start and end.
Examples:
CLIP("world", 1, 2) => "or"
CLIP("world", 0, 1) => "worl"
CLIP("world", 0, 0) => "world" |
REGEXEXTRACT(text,".{" & start & "}(.*).{" & end & "}$") |
text:
type: text
description: Text to clip
example: "world"
start:
type: integer
description: Number of characters to clip off the start of the string. Use 0 for none.
example: 1
end:
type: integer
description: Number of characters to clip off the end of the string. Use 0 for none.
example: 2 |
Public |
Text |
|
COL_TO_NUM |
Converts up to two letters to a number. Useful when doing "column math". Supports columns "A" through "ZZ".
Examples:
COL_TO_NUM("A") => 1
COL_TO_NUM("AA") => 27 |
IFS(
LENGTH(column) = 1,
CHAR_TO_NUM(column),
LENGTH(column) = 2,
LET(
first, FIRST(column),
last, LAST(column),
(CHAR_TO_NUM(first) * 26) + CHAR_TO_NUM(last)
),
ELSE(), RAISE("N/A")
) |
column:
type: text ("A" through "ZZ")
description: A string representing a column reference
example: "AA" |
Public |
|
|
COMMENT |
Add a comment to a formula or function.
Example:
COMMENT("This is one plus one", 1+1) => 2 |
formula |
comment:
type: text
description: A text description of what your code is doing.
example: "This function just outputs 2"
formula:
type: any
description: A formula you want to comment
example: 1 + 1 |
Public |
|
|
COMPACT |
Returns non-blank elements in a range, or rather, returns only present elements in a range.
Example:
COMPACT({0, 1, , BLANK(), 2, 3}) => {0,1,2,3} |
SELECT(range, lambda(i, IS_PRESENT(i))) |
range:
type: range
description: The range to compact, potentially containing blank/non-present elements
example: {1, 2, BLANK(), 3} |
Public |
Enum |
|
CONFIG_GET |
Get a value from _CONFIG(). If the value is stored as comma-delimited text (since you can't nest arrays), it splits by comma into an array. If there is no value, it returns a blank value.
Examples:
CONFIG_GET("Version") => "0.0.1"
CONFIG_GET("Datetime.Workweek.DayNumbers") => {2, 3, 4, 5, 6}
CONFIG_GET("this key doesn't exist") => #N/A |
LET(
value_or_error, VLOOKUP(key, _CONFIG(), 2, FALSE),
IF(
ISNA(value_or_error),
COMMENT("If N/A, raise the error", value_or_error),
IF(
REGEXMATCH(TO_S(value_or_error),","),
SPLIT(value_or_error,","),
value_or_error
)
)
) |
key:
type: text
description: A key of the key-value pairs stored in _CONFIG()
example: "Version" |
Public |
|
|
COUNTALL |
Counts all values and blanks in a range.
Example: COUNTALL({0, 1, BLANK()}) => 3 |
COUNTA(array) + COUNTBLANK(array) |
array:
type: range
description: An array or range, potentially containing values and/or blanks
example: A1:1 |
Public |
Enum |
|
DASHERIZE |
Replaces underscores with dashes.
Example:
DASHERIZE("civic_tech") => "civic-tech" |
REGEXREPLACE(string,"_","-") |
string:
type: text
description: A string containing underscores
example: "hello-world" |
Public |
Text |
|
DAYS_ |
Returns a given number of days as a number, where 1 day = 1. Useful when doing time math. The function name has a _ to differentiate it from DAYS, a Sheets-native formula which calculates the number of days between dates.
Examples:
DAYS_(1) => 1
DAYS_(7) => 7 |
number |
number:
type: number
description: Number of days
example: 1 |
Public |
Date and time |
|
DETECT |
Get the first element that matches the given criterion.
Example:
DETECT({2, 1, 3, 0}, lambda(x, x < 2)) => 1 |
INDEX(SELECT(array, block), 1) |
array:
type: range (1D)
description: An array
example: {1, 2, 3}
block:
type: lambda
description: A lambda containing the criterion
example: lambda(x, x > 2) |
Public |
Enum |
|
DIGITS |
Turns an integer, or a string containing an integer, into an array of single digits. Decimals are not permitted.
Examples:
DIGITS(100) => {1,0,0}
DIGITS("100") => {1,0,0}
DIGITS("hello") => #VALUE!
DIGITS(1.0) => #VALUE! |
IF(
IS_DECIMAL(number), RAISE("VALUE"),
LET(
just_number, TO_I(number),
number_str, TO_S(just_number),
number_chars, CHARS(number_str),
MAP(number_chars, lambda(char, TO_I(char)))
)
) |
number:
type: integer | text
description: An integer to split into digits
example: 100 |
Public |
Number |
|
ELSE |
To be used as the last condition in IFS(), which expects pairs so cannot take an else condition. Easier to interpret in context than TRUE, this formula is just syntactic sugar.
Example:
LET(
x, 3,
IFS(
x > 1,"one",
x > 2,"two",
ELSE(),"fallback"
)
)
=> "fallback" |
TRUE |
|
Public |
|
|
FIFTH |
Returns the first element in an array.
Examples:
FIFTH("hello") => 0
FIFTH({1,2,3,4,5}) => 5 |
IF(ISTEXT(input), MID(input, 5, 1), INDEX(input, 5)) |
input:
type: range | text
description: An array or text to get the 5th element of
example: "hello" |
Public |
Enum |
|
FIRST |
Gets the first element from any array or text. Alias of FIRST1.
Examples:
FIRST("hello") => "h"
FIRST({1,2,3}) => 1 |
FIRST1(input) |
input:
type: range | text
description: An array or text to get the first element of
example: "hello" |
Public |
Enum |
|
FIRST1 |
Gets the first element from any array or text.
Examples:
FIRST("hello") => "h"
FIRST({1,2,3}) => 1 |
LET(
safe_input,
CATCH_IF(
input,
"REF",
lambda(e, { BLANK() })
),
IF(
IS_TEXT(safe_input),
LEFT(safe_input, 1),
{INDEX(safe_input, 1)}
)
) |
input:
type: range | text
description: An array or text to get the first element of
example: "hello" |
Public |
Enum |
|
FIRST2 |
Gets the first two elements from any array or text.
Examples:
FIRST("hello") => "he"
FIRST({1,2,3}) => {1,2} |
LET(
safe_input,
CATCH_IF(
input,
"REF",
lambda(e, { BLANK(), BLANK() } )
),
IF(
IS_TEXT(safe_input),
LEFT(safe_input, 2),
{INDEX(safe_input, 1), INDEX(safe_input, 2)}
)
) |
input:
type: range | text
description: An array or text to get the first 2 elements of
example: "hello" |
Public |
Enum |
|
FIRST3 |
Gets the first three elements from any array or text.
Examples:
FIRST("hello") => "hel"
FIRST({1,2,3}) => {1,2,3} |
LET(
safe_input,
CATCH_IF(
input,
"REF",
lambda(e, { BLANK(), BLANK(), BLANK() } )
),
IF(
IS_TEXT(safe_input),
LEFT(safe_input, 3),
{INDEX(safe_input, 1), INDEX(safe_input, 2), INDEX(safe_input, 3)}
)
) |
input:
type: range | text
description: An array or text to get the first 3 elements of
example: "hello" |
Public |
Enum |
|
FORTNIGHTS |
Returns a given number of fortnights as a number, where 1 day = 1. Useful when doing time math.
Examples:
FORTNIGHTS(2) => 28
FORTNIGHTS(1/14) => 1 |
number * 14 |
number:
type: number
description: Number of fortnights
example: 2 |
Public |
Date and time |
|
FOURTH |
Returns the fourth element of an array or text. Example: FOURTH("fourth") => "r" |
IF(ISTEXT(input), MID(input, 4, 1), INDEX(input, 4)) |
input:
type: range | text
description: An array or text to get the 4th element of
example: "hello" |
Public |
Enum |
|
GREP |
Returns elements that match the given pattern. The name "grep" comes from the Unix tool `grep`, which itself comes from the command g/re/p (global / regular expression / print).
Examples:
GREP("ic", {"civic","public","gov"}) => {"civic","public"}
GREP("\d", {"a1","b2","cc"}) => {"a1","b2"} |
SELECT(array, lambda(i, REGEXMATCH(i, pattern))) |
pattern:
type: text
description: A regular expression pattern or snippet of text
example: ".*"
array:
type: range
description: An array to search through
example: {"civic","public","gov"} |
Public |
Enum |
|
HEAD |
Gets the first element of any enumerable, like the first digit of a number, the first character of a string, or the first element of an array.
Examples:
HEAD({"a","b","c"}) => "a"
HEAD("hello") => "h"
HEAD(100) => 1 |
IFS(
IS_TEXT(input), FIRST(input),
IS_NUMBER(input), FIRST(DIGITS(input)),
IS_RANGE(input), FIRST(input)
) |
input:
type: any
description: Number, string, or array
example: "hello" |
Public |
Enum |
|
HOURS |
Returns a given number of hours as a number, where 1 day = 1. Useful when doing time math.
Examples:
HOURS(24) => 1
HOURS(1) => (1/24) |
number / 24 |
number:
type: number
description: Number of hours
example: 1 |
Public |
Date and time |
|
INJECT |
Takes an initial value and a lambda function, and calls the lambda on both the intermediate value and each element.
Similar to SCAN.
Examples:
INJECT({2,3,4}, 1, lambda(memo, element, memo + element)) => 10
INJECT(CHARS("username"),"", lambda(memo, char, IF(MOD(LEN(memo), 2)=0, memo & UPPER(char), memo & char))) => "UsErNaMe" |
LAST1(SCAN(initial_value, array, block)) |
array:
type: range (1D)
description: An array to iterate over. Each item will be passed to the lambda as the second argument.
example: {1,2,3,4}
initial_value:
type: any
description: The value to start with. This value will be passed to the lambda function as the first argument in the first iteration.
example: 0
block:
type: lambda
description: Lambda function defining the operation. It takes two parameters, the intermediate value and the array element.
example: lambda(memo, element, memo + element) |
Public |
Enum |
|
IS_BLANK |
DEPRECATED (see note)
Returns TRUE for blank, false, and empty values, such as strings only containing whitespace and empty arrays. Opposite of IS_PRESENT.
Examples:
IS_BLANK(BLANK()) => TRUE
IS_BLANK(FALSE) => TRUE
IS_BLANK(" ") => TRUE
IS_BLANK(0) => FALSE
Deprecation note: This function creates some confusion around "blank" versus "empty" values. It's likely that IS_BLANK() will be differentiated from an IS_EMPTY() function in future versions. |
OR(
ISBLANK(any),
any = FALSE,
IFERROR(REGEXMATCH(any,"^\s+$"), ),
LEN(any) = 0
) |
any:
type: any
description: Anything, including strings, numbers, arrays, and blank values
example: " " |
Public |
|
|
IS_DECIMAL |
Returns TRUE if a number is a decimal or float. Note: numbers with only zeroes after the decimal point is considered by this formula and Google Sheets to be an integer.
Examples:
IS_DECIMAL(102) => FALSE
IS_DECIMAL(1.2) => TRUE
IS_DECIMAL(1.0) => FALSE (Google Sheets considers this is an integer)
IS_DECIMAL("1.2") => FALSE |
AND(
IS_NUMBER(number),
NOT(TO_I(number)=number)
) |
number:
type: number
description: Number to check to see if it's a decimal
example: 1.2 |
Public |
|
|
IS_DEFINED |
Checks if a function name is defined.
Technically, checks whether there is a #NAME error for the given function. You do not have to pass any arguments to the function, however, you must add parentheses at the end of the function name to have Google Sheets recognize it as a function referernce and not another type of reference (e.g. range, named range, variable, etc).
Examples:
IS_DEFINED(ISBLANK()) => TRUE (Sheets native function)
IS_DEFINED(ISBLANK) => FALSE (Doesn't work without parentheses at the end)
IS_DEFINED(UNLIKELY_FUNCTION_NAME()) => FALSE (unless you define this in your sheet)
IS_DEFINED(IS_DEFINED()) => TRUE (so meta!) |
IF(
ISERROR(function),
NOT(ERROR.TYPE(function) = 5),
TRUE
) |
function:
type: function invocation
description: The function name. Remember to invoke the function with ending parentheses.
example: ISBLANK() |
Public |
|
|
IS_ERROR |
Returns TRUE if the given formula is an error. Alias for ISERROR.
See CATCH or CATCH_IF for more error handling functions. |
ISERROR(formula) |
formula:
type: any
description: Formula to check for error
example: 1/0 |
Public |
Error |
|
IS_EVEN |
Returns TRUE if the number is even, FALSE if odd |
IF(
IS_INTEGER(number),
EVEN(number)=number,
RAISE_VALUE()
) |
number:
type: integer
description:
example: |
Public |
Number |
|
IS_IN |
Returns TRUE if the element is in the list.
Examples:
IS_IN(1, {1, 2, 3}) => TRUE
IS_IN("D", {"A","B","C"}) => FALSE |
IS_PRESENT(IFERROR(MATCH(needle, haystack, 0), FALSE)) |
needle:
type: number | text
description: Item to search for
example: 1
haystack:
type: range
description: List to search in
example: {1, 2, 3} |
Public |
Enum |
|
IS_INTEGER |
Returns TRUE if a number is an integer.
Examples:
IS_INTEGER(12) => TRUE
IS_INTEGER(1.2) => FALSE |
AND(
COMMENT("Ensure it's not a range", IS_NUMBER(number)),
MOD(number, 1) = 0
) |
number:
type: number
description: Number to check to see if it's an integer
example: 12 |
Public |
Number |
|
IS_MULTIPLE_OF |
Determines if the number is a multiple of another number
Example:
IS_MULTIPLE_OF(20, 2) => TRUE
IS_MULTIPLE_OF(12, 3) => TRUE
IS_MULTIPLE_OF(11, 5) => FALSE |
MOD(number_to_check, multiplication_factor) = 0 |
number_to_check:
type: number
description: The number you're checking (Is *2* a multiple of 4?)
example: 4
multiplication_factor:
type: number
description: The multiple factor (Is 2 a multiple of *4*?)
example: 2 |
Public |
Number |
|
IS_NUMBER |
Checks if the input is a number, and not a range with a number in the first element. (Google Sheets native function ISNUMBER has strange behavior here: ISNUMBER({1,"a"}) => TRUE)
Examples:
IS_NUMBER(1) => TRUE
IS_NUMBER(1.0) => TRUE
IS_NUMBER({1}) => TRUE (Google Sheets interprets 1-element arrays as single values, so this is an edge case to be aware of)
IS_NUMBER({1,"a"}) => FALSE
IS_NUMBER({1,2}) => FALSE |
AND(
NOT(IS_RANGE(any)),
ISNUMBER(any)
) |
any:
type: any
description: An input to check to see if it's a number
example: {1,"a", 2} |
Public |
Number |
|
IS_ODD |
Returns TRUE if the integer is odd, FALSE if it's even. Raises an error if given a decimal. |
IF(
IS_INTEGER(number),
ODD(number)=number,
RAISE_VALUE()
) |
number:
type: integer
description:
example: |
Public |
Number |
|
IS_PRESENT |
DEPRECATED (see note)
Returns TRUE for present values, that is, non-blank values. Opposite of IS_BLANK.
Examples:
IS_PRESENT(0) => TRUE
IS_PRESENT(BLANK()) => FALSE
IS_PRESENT(FALSE) => FALSE
IS_PRESENT(" ") => FALSE
Deprecation note: This function creates some confusion around "blank" versus "empty" values. It's likely that the behavior of this will change in future versions. |
NOT(IS_BLANK(any)) |
any:
type: any
description: Any object, such as a cell, string, or list
example: {1,2,3} |
Public |
|
|
IS_RANGE |
Returns true if the input has more than one element. Google Sheets interprets 1-element arrays/ranges as single values, so a single-element range will return FALSE.
IS_RANGE({"a","b","c"}) = TRUE
IS_RANGE(1) = FALSE
IS_RANGE({1}) = FALSE |
COUNTALL(input) > 1 |
input:
type: any
description:
example: |
Public |
Enum |
|
IS_TEXT |
Returns true if the input is only text, and not a range with text in at least the first element. (Google Sheets native function ISTEXT() has some strange behavior here: ISTEXT({"a", 1, 2, 3}) => TRUE)
Examples:
IS_TEXT("a"} => TRUE
IS_TEXT({"a"}} => TRUE (Google Sheets interprets 1-element arrays as a single value, so this is an edge case to be aware of.)
IS_TEXT({"a", 1, 2, 3}) => FALSE
IS_TEXT(123) => FALSE |
IF(
IS_RANGE(input),
FALSE,
ISTEXT(input)
) |
input:
type: any
description:
example: |
Public |
Text |
|
IS_WEEKDAY |
Returns TRUE if the given date is during the work week, FALSE if weekend.
Uses a default five-day workweek of Monday through Friday. Alternative work schedules and four-day workweeks can be set by giving the _CONFIG() formula a different value for "Datetime.Workweek.DayNumbers". |
LET(
default_weekdays, {2,3,4,5,6},
weekdays, CATCH(CONFIG_GET("Datetime.Workweek.DayNumbers"), default_weekdays),
IS_IN(WEEKDAY(date), weekdays)
) |
date:
type: date
description:
example: |
Public |
Date and time |
|
LAST |
Returns the last character of a string or the last element of an array. Alias for LAST1.
Examples:
LAST("hello") => "o"
LAST({1,2,3}) => 3 |
LAST1(enumerable) |
enumerable:
type: range | text
description:
example: |
Public |
Enum |
|
LAST1 |
Returns the last character from the end of a string or array.
Examples:
LAST1("hello") => "o"
LAST1({1,2,3}) => 3 |
LET(
safe_get_position, lambda(pos,
CATCH_IF(INDEX(enumerable, LENGTH(enumerable) - pos),"VALUE", BLANK())
),
IF(
IS_TEXT(enumerable),
RIGHT(enumerable),
safe_get_position(0)
)
) |
enumerable:
type: range | text
description: String or array
example: "hello" |
Public |
Enum |
|
LAST2 |
Returns the last two characters of a string the or last two elements of an array
Examples:
LAST2("hello") => "lo"
LAST2({1,2,3}) => {2, 3} |
LET(
safe_get_position, lambda(pos,
CATCH_IF(INDEX(enumerable, LENGTH(enumerable) - pos),"VALUE", BLANK())
),
IF(
IS_TEXT(enumerable),
RIGHT(enumerable, 2),
COMPACT({
safe_get_position(1),
safe_get_position(0)
})
)
) |
enumerable:
type: range | text
description:
example: |
Public |
Enum |
|
LAST3 |
Returns the last three characters of a string the or last three elements of an array
Examples:
LAST3("hello") => "llo"
LAST3({1,2,3}) => {1, 2, 3} |
LET(
safe_get_position, lambda(pos,
CATCH_IF(INDEX(enumerable, LENGTH(enumerable) - pos),"VALUE", BLANK())
),
IF(
IS_TEXT(enumerable),
RIGHT(enumerable, 3),
COMPACT({
safe_get_position(2),
safe_get_position(1),
safe_get_position(0)
})
)
) |
enumerable:
type: range | text
description:
example: |
Public |
Enum |
|
LAST4 |
Returns the last four characters of a string or the last four elements of an array.
Examples:
LAST4("hello") => "ello"
LAST4({1,2,3,4,5}) => {2,3,4,5} |
LET(
safe_get_position, lambda(pos,
CATCH_IF(INDEX(enumerable, LENGTH(enumerable) - pos),"VALUE", BLANK())
),
IF(
IS_TEXT(enumerable),
RIGHT(enumerable, 4),
COMPACT({
safe_get_position(3),
safe_get_position(2),
safe_get_position(1),
safe_get_position(0)
})
)
) |
enumerable:
type: range | text
description:
example: |
Public |
Enum |
|
LENGTH |
Gets the length of a string or array
Examples:
LENGTH("hello") => 5
LENGTH({1,2,3}) => 3 |
IF(
IS_TEXT(string_or_array),
LEN(string_or_array),
COUNTALL(string_or_array)
) |
string_or_array:
type: range | text
description:
example: |
Public |
Enum |
|
LJUST |
Left justify or pad a string to reach the desired length.
Examples:
LJUST("1", 4,"0") => "1000"
LJUST("10001", 4,"0") => "10001" |
IF(
LEN(pad_with) > 1,
RAISE_VALUE(),
LET(
input_length, LEN(string),
IF(
input_length < LENGTH,
CONCAT(string, REPT(pad_with, LENGTH - input_length)),
string
)
)
) |
string:
type: text
description: Initial text
string: "1"
length:
type: integer
description: Desired total length of string
example: 4
pad_with:
type: character
description: Character to pad the string with
example: "0" |
Public |
Text |
|
MAP_WITH_INDEX |
Like MAP, but calls the lambda function with an extra parameter: the index of the element.
Examples:
MAP_WITH_INDEX({"a","b","c"}, lambda(elem, index, elem & index)) => {"a1","b2","c3"}
MAP_WITH_INDEX({3,2,1}, lambda(elem, index, elem + index)) => {4, 4, 4} |
LET(
size, LENGTH(array),
last_col, size + 1,
col_range, RANGE(2, last_col),
starting_index, 1,
placeholder, 0,
results, _MAP_WITH_INDEX(starting_index, size, array, placeholder, block),
CHOOSECOLS(results, col_range)
) |
array:
type: range (1D)
description: Array to iterate over
example: {1, 2, 3}
block:
type: lambda
description: Lambda function that takes two arguments, the array element and the index (1-indexed position) of the array element
example: lambda(element, index, element * index) |
Public |
Enum |
|
MINUTES |
Returns a given number of minutes as a number, where 1 day = 1. Useful when doing time math.
Examples:
MINUTES(24 * 60) => 1
MINUTES(1) => (1/24/60) |
number / 24 / 60 |
number:
type: number
description: Number of minutes
example: 60 |
Public |
Date and time |
|
NEWLINE |
Returns a return or newline character, equivalent to "\n" in many languages. Useful for arranging input by line or formatting text.
Example:
JOIN(NEWLINE(), {1,2,3}) =>
"1
2
3" |
"
" |
|
Public |
Text |
|
NL |
Returns a return or newline character, equivalent to "\n" in many languages. Useful for arranging input by line or formatting text. Alias of NEWLINE, made shorter for convenience in longer formulas.
Example:
JOIN(NEWLINE(), {1,2,3}) =>
"1
2
3" |
NEWLINE() |
|
Public |
Text |
|
NUM_TO_CHAR |
Converts a number to a character ("A" to "Z"). For converting column references to make column math easier, see NUM_TO_COL.
Examples:
NUM_TO_CHAR(1) => "A"
NUM_TO_CHAR(26) => "Z"
NUM_TO_CHAR(27) => #N/A |
IF(
OR(number <= 0, number > 27),
RAISE_VALUE(),
INDEX(RANGE("A","Z"), number)
) |
number:
type: integer (1–26)
description: Number to convert
example: 1 |
Public |
|
|
NUM_TO_COL |
Converts a number to a column reference. Use in concert with COL_TO_NUM to convert back and forth and do column math. Supports columns 1 ("A") to 702 ("ZZ").
Examples:
NUM_TO_COL(27) => "AA"
NUM_TO_COL(154) => "FX" |
IFS(
column_number < 1, RAISE("N/A"),
column_number > 702, RAISE("N/A"),
column_number <= 26, NUM_TO_CHAR(column_number),
ELSE(),
LET(
first, ROUND(DIVIDE(column_number, 26)),
last, MOD(column_number, 26),
NUM_TO_CHAR(first) & NUM_TO_CHAR(last)
)
) |
column_number:
type: integer (1–702)
description: The number for a column
example: COL_TO_NUM("AA") |
Public |
|
|
ORDINAL |
Returns the ordinal suffix for a number (e.g. 1-"st", 2-"nd", 3-"rd", 4-"th"). To turn the number into the ordinal, use ORDINALIZE.
Examples:
ORDINAL(4) => "th"
ORDINAL(101) => "st" |
IF(
IS_IN(number, {11, 12, 13}),"th",
LET(
last_digit, LAST(DIGITS(number)),
IF(
IS_IN(last_digit, {1, 2, 3}),
INDEX({"st","nd","rd"}, last_digit),
"th"
)
)
) |
number:
type: integer
description: Number to get the ordinal of
example: 12 |
Public |
Number |
|
ORDINALIZE |
Returns the ordinal form of a given number.
Examples:
ORDINALIZE(4) => "4th"
ORDINALIZE(101) => "101st" |
CONCATENATE(TO_S(number), ORDINAL(number)) |
number:
type: integer
description: Number to ordinalize
example: 12 |
Public |
Number |
|
PAD_LEFT |
Left justify or pad a string to reach the desired length. Alias for LJUST.
Examples:
PAD_LEFT("1", 4,"0") => "1000"
PAD_LEFT("10001", 4,"0") => "10001" |
LJUST(string, LENGTH, pad_with) |
string:
type: text
description: Initial text
string: "1"
length:
type: integer
description: Desired total length of string
example: 4
pad_with:
type: character
description: Character to pad the string with
example: "0" |
Public |
Text |
|
PAD_RIGHT |
Right justify or pad a string to reach the desired length. Alias for LJUST.
Examples:
PAD_RIGHT("1", 4,"0") => "0001"
PAD_RIGHT("10001", 4,"0") => "10001" |
RJUST(string, length, pad_with) |
string:
type: text
description: Initial text
string: "1"
length:
type: integer
description: Desired total length of string
example: 4
pad_with:
type: character
description: Character to pad the string with
example: "0" |
Public |
Text |
|
PRESENCE |
DEPRECATED (see note)
Returns the given object if present (non-blank), otherwise returns a blank value.
Examples:
PRESENCE(0) => 0
PRESENCE(BLANK()) => BLANK()
PRESENCE(FALSE) => BLANK()
PRESENCE(" ") => BLANK()
Deprecation note: This function creates some confusion around "blank" versus "empty" values. It's likely that the behavior of this will change in future versions. |
IF(IS_PRESENT(any), any, BLANK()) |
any:
type: any
description: Any object, such as a range or text
example: {1,2,3}
|
Public |
|
|
PUSH |
Returns an array of the given value appended to the given array.
Example:
PUSH({1,2}, 3) => {1, 2, 3} |
{array, element} |
array:
type: range (1D)
description: An array to push an element onto
example: {1,2}
element:
type: any
description: An element to add to the end (right side) of the array
example: 3 |
Public |
Enum |
|
RAISE |
Raises an error based on the given shorthand name or error type code number.
Examples:
RAISE("NULL") or RAISE(1) => #NULL!
RAISE("DIV/0") or RAISE(2) => #DIV/0!
RAISE("VALUE") or RAISE(3) => #VALUE!
RAISE("REF") or RAISE(4) => #REF!
RAISE("NAME") or RAISE(5) => #NAME!
RAISE("NUM") or RAISE(6) => #NUM!
RAISE("N/A") or RAISE(7) => #N/A!
RAISE("ERROR") or RAISE(8) => #ERROR! |
IF(
IS_IN(error_type, {1, 2, 3, 4, 5, 6, 7, 8,"NULL","DIV/0","VALUE","REF","NAME","NUM","N/A","ERROR"}),
IFS(
error_type = 1, RAISE_NULL(),
error_type = "NULL", RAISE_NULL(),
error_type = 2, RAISE_DIV0(),
error_type = "DIV/0", RAISE_DIV0(),
error_type = 3, RAISE_VALUE(),
error_type = "VALUE", RAISE_VALUE(),
error_type = 4, RAISE_REF(),
error_type = "REF", RAISE_REF(),
error_type = 5, RAISE_NAME(),
error_type = "NAME", RAISE_NAME(),
error_type = 6, RAISE_NUM(),
error_type = "NUM", RAISE_NUM(),
error_type = 7, RAISE_NA(),
error_type = "N/A", RAISE_NA(),
error_type = 8, RAISE_ERROR(),
error_type = "ERROR", RAISE_ERROR()
),
RAISE_ERROR()
) |
error_type:
type: number | text
description: Number or string that describes the error type to raise
example: "N/A" |
Public |
Error |
|
RAISE_DIV0 |
Raises a zero division error (#DIV/0!) |
IF(true, #DIV/0!, #DIV/0!) |
|
Public |
Error |
|
RAISE_ERROR |
Raises an error (#ERROR!) |
IF(true, #ERROR!, #ERROR!) |
|
Public |
Error |
|
RAISE_NA |
Raises an N/A error (#N/A) |
IF(true, #N/A, #N/A) |
|
Public |
Error |
|
RAISE_NAME |
Raises a name error (#NAME?) |
IF(true, #NAME?, #NAME?) |
|
Public |
Error |
|
RAISE_NULL |
Raises a null error (#NULL!) |
IF(true, #NULL!, #NULL!) |
|
Public |
Error |
|
RAISE_NUM |
Raises a num error (#NUM!) |
IF(true, #NUM!, #NUM!) |
|
Public |
Error |
|
RAISE_REF |
Raises a reference error (#REF!) |
IF(true, #REF!, #REF!) |
|
Public |
Error |
|
RAISE_VALUE |
Raises a value error (#VALUE!) |
IF(true, #VALUE!, #VALUE!) |
|
Public |
Error |
|
RANGE |
Returns a range of integers or characters
Examples:
RANGE(1, 5) => {1, 2, 3, 4, 5}
RANGE(3, 1) => {3, 2, 1}
RANGE("a","f") => {"a","b","c","d","e","f"} |
LET(
all_numbers, ALL({start, end}, lambda(x, IS_NUMBER(x))),
all_chars, ALL({start, end}, lambda(x, IS_TEXT(x))),
number_range, lambda(a, b, IF(a < b, RANGE_INCR(a, b), REVERSE(RANGE_INCR(b, a)))),
char_range, lambda(a, b,
LET(
num_a, CODE(a),
num_b, CODE(b),
format, lambda(r, MAP(r, lambda(elem, CHAR(elem)))),
IF(num_a < num_b, format(RANGE_INCR(num_a, num_b)), format(REVERSE(RANGE_INCR(num_b, num_a))))
)
),
IF(all_numbers, number_range(start, end),
IF(all_chars, char_range(start, end), RAISE_NA()))
) |
start:
type: integer | character
description: Starting integer or character
example: "A"
end:
type: integer | character
description: Ending integer or character
example: "Z" |
Public |
Enum |
|
RANGE_INCR |
Returns an increasing range of integers. The start number must be smaller than the ending number.
Examples:
RANGE(1, 5) => {1, 2, 3, 4, 5}
RANGE(5, 1) => #N/A |
IF(
start > end,
RAISE("N/A"),
COMPACT(_ITERATE_UPTO({BLANK()},start,start,end))
) |
start:
type: integer
description: Starting integer
example: 1
end:
type: integer
description: Ending integer
example: 5 |
Public |
Enum |
|
REMOVE |
Removes all instances of the pattern from the string.
Example: REMOVE("Hello World","Hello ") => "World" |
REGEXREPLACE(string, pattern, BLANK()) |
string:
type: text
description: The string to remove the pattern from
example: "Hello World"
pattern:
type: text | regex
description: The pattern to remove from the string
example: "Hello " |
Public |
Text |
|
REVERSE |
Returns the reverse of an array
Example: REVERSE({3, 2, 1}) => {1, 2, 3} |
COMMENT(
"To get this to work, we need to start with an extra element — that's the 0 here. CHOOSECOLS is what we use to delete that.",
CHOOSECOLS(_ITERATE_REVERSE(array, 0), RANGE_INCR(1, LENGTH(array)))
) |
array:
type: range (1D)
description: The array to reverse
example: {3, 2, 1} |
Public |
Enum |
|
RJUST |
Right justify or pad a string to reach the desired length.
Examples:
RJUST("1", 4,"0") => "0001"
RJUST("10001", 4,"0") => "10001" |
IF(
LEN(pad_with) > 1,
RAISE_VALUE(),
LET(
input_length, LEN(string),
IF(
input_length < length,
CONCAT(REPT(pad_with, length - input_length), string),
string
)
)
) |
string:
type: text
description: Initial text
string: "1"
length:
type: integer
description: Desired total length of string
example: 4
pad_with:
type: character
description: Character to pad the string with
example: "0" |
Public |
Text |
|
SECOND_ |
Returns the second element in a list. The name has an underscore to differentiate it from a different function native to Google Sheets.
Example: SECOND_({1, 2, 3}) => 2 |
CATCH_IF(
IF(ISTEXT(input), MID(input, 2, 1), INDEX(input, 2)),
"REF",
lambda(e, BLANK())
) |
input:
type: range (1D) | text
description: An array or string
example: "hello" |
Public |
Enum |
|
SECONDS |
Returns a given number of seconds as a number, where 1 day = 1. Useful when doing time math.
Examples:
SECONDS(86400) => 1
SECONDS(1) => (1/24/60/60) |
number / 24 / 60 / 60 |
number:
type: number
description: Number of seconds
example: 60 |
Public |
Date and time |
|
SELECT |
Returns elements of the array that match the given lambda function
Example: SELECT({-1,0,1,2,3}, lambda(i, i>1)) => {2, 3} |
CATCH_IF(
FILTER(array, MAP(array, block)),
"N/A",
lambda(e, {BLANK()})
) |
array:
type: range (1D)
description: The array to select from
example: {1, 2, 3}
block:
type: lambda
description: Lambda function to run against each element
example: lambda(x, x > 2) |
Public |
Enum |
|
SQUISH |
Strips leading and trailing whitespace, and substitutes runs of whitespace with a single space each
Example: SQUISH(" civic tech ") => "civic tech" |
LET(
no_returns_or_tabs, REGEXREPLACE(string,"\n|\t|\r"," "),
single_spaced, REGEXREPLACE(no_returns_or_tabs,"\s{2,}"," "),
REGEXREPLACE(single_spaced,"^\s+|\s+$","")
) |
string:
type: text
description: The string to be cleaned up
example: " civic tech " |
Public |
Text |
|
TAIL |
Returns the trailing elements (everything past the first) of an array
Examples:
TAIL({1, 2, 3, 4}) => {2, 3, 4}
TAIL(5) => BLANK() |
IF(
IS_RANGE(array),
LET(
true_count, LENGTH(array) - 1,
range, PUSH({FALSE}, MAKEARRAY(1, true_count, lambda(_row,_col,"TRUE"))),
FILTER(array, range)
),
BLANK()
) |
array:
type: range (1D)
description: An array to get the tail of
example: {1, 2, 3, 4}
|
Public |
Enum |
|
TALLY |
Tallies a list
Example:
TALLY({"a"; "a"; "b"; "b"; "c"}) #=> {"a", 2 ; "b", 2 ; "c", 1} |
LET(
keys, UNIQUE(range),
MAP(keys, lambda(k, {k, COUNTIF(range, k)})
)) |
range:
type: range
description: Data range to tally
example: {"a"; "a"; "b"; "b"; "c"} |
Public |
Enum |
|
THIRD |
Returns the third element of a string or array
Examples:
THIRD("world") => "r"
THIRD({1, 2, 3, 4, 5}) => 3 |
IF(ISTEXT(input), MID(input, 3, 1), INDEX(input, 3)) |
input:
type: range (1D) | text
description: String or array to get the third element of
example: "world" |
Public |
Enum |
|
TO_I |
Converts a number or text to integer
Examples:
TO_I(1.0) => 1
TO_I("1.0") => 1
TO_I(1) => 1 |
INT(TO_PURE_NUMBER(input)) |
input:
type: any
description: Number or string to convert
example: "1.0" |
Public |
|
|
TO_S |
Converts a value to text. Alias of TO_TEXT.
Examples:
TO_S(1) => "1"
TO_S(1.0) => "1" (Not sure why it does this) |
TO_TEXT(input) |
input:
type: any |
Public |
|
|
TOMORROW |
Returns tomorrow as a number where 1 day = 1.
Examples:
(If today is Jan 1 2023) TOMORROW() => Jan 2 2023
TOMORROW() - TODAY() => 1 |
TODAY() + DAYS_(1) |
|
Public |
Date and time |
|
TRUNCATE |
Truncates a string to a given length and adds ellipses. The length value incorporates the size of the ellipses, that is, a length of 20 will clip the original string to 17 characters, then add the ellipsis ("...") of 3 characters, for a total of 20.
Examples:
TRUNCATE("Oh dear! Oh dear! I shall be late!", 20) => "Oh dear! Oh dear!..."
TRUNCATE("wow", 3) => "..." |
CONCATENATE(LEFT(string, length - 3),"...") |
string:
type: text
description: The string to truncate
example: "Oh dear! Oh dear! I shall be late!"
length:
type: integer
description: Number of characters to truncate to
example: 20 |
Public |
Text |
|
TRY |
Attempts the given formula, and returns blank if there's an error
Examples:
TRY(1 + 0) => 1
TRY(1 / 0) => (blank) |
IF(ISERROR(formula), BLANK(), formula) |
formula:
type: expression
description: Any formula or expression
example: 1/0 |
Public |
Error |
|
UNLESS |
Reverse of IF.
Example: UNLESS(x > 0, RAISE_NA, 1 / x) |
IF(NOT(condition), value_if_false, value_if_true) |
condition:
type: expression
description: An expression or reference to a cell containing an expression that represents some logical value, i.e. TRUE or FALSE.
example: x > 0
value_if_false:
type: expression
description: The value the function returns if condition is FALSE.
example: RAISE("N/A")
value_if_true:
type: expression
description: The value the function returns if condition is TRUE.
example: x / 0 |
Public |
|
|
WEEKS |
Returns a given number of weeks as a number, where 1 day = 1. Useful when doing time math.
Examples:
WEEKS(1) => 7
WEEKS(1/7) => 1 |
number * 7 |
number:
type: number
description: Number of weeks
example: 1 |
Public |
Date and time |
|
YESTERDAY |
Returns yesterday as a number where 1 day = 1.
Examples:
(If today is Jan 1 2023) YESTERDAY() => Dec 31 2022
TODAY() - YESTERDAY() = 1 |
TODAY() - DAYS_(1) |
name:
type:
description:
example:
name:
type:
description:
example: |
Public |
Date and time |