number
Converts argument to a number. If the argument doesn't parse as a number, returns NaN.
string
Converts argument to a string.
coalesce
Takes any number of arguments. Return first argument that is not NaN. Useful for providing default values when there are unexpected values in the inputs to the formula.
not
Logical not of one scalar argument
or
Logical or of two scalar arguments
and
Logical and of two scalar arguments
xor
Logical xor of two arguments
if
Takes three arguments. Tests first argument, returning second argument if true and third argument if false.
ifs
Evaluates multiple conditions and returns a value that corresponds to the first true condition (more info).
isBlank
Evaluates if a cell has no value. In conditional formatting, would be used as "isBlank(cellValue)", and will format true if the cell is blank.
conditional
First argument is value to be tested. It is compared to the even arguments (second argument, fourth argument, etc.). Returns the argument following the first match. If there is no match and it was called with an even number of arguments, returns the last argument. If there is no match and it was called with an odd number of arguments, returns NaN.
includes
Takes two arguments. First argument is array. Compares second argument against each element of array. If second argument equals any element of array, then returns true.
startsWith
Used to filter to results that start with the specified value.
Example: startsWith(lookup("productProductId"), "40")
This will return all products on the dataset that start with "40"
contains
Used to filter to results that contain the specified value.
Example: contains(lookup("productProductId"), "40")
This will return all products on the dataset that contain "40"
endsWith
Used to filter to results that ends with the specified value.
Example: endsWith(lookup("productProductId"), "40")
This will return all products on the dataset that end with "40"
min
Compute minimum value of any number of arguments
max
Compute maximum value of any number of arguments
ceil
Round first argument towards plus infinity
floor
Round first argument towards minus infinity
round
Round the first argument towards nearest integer. Optionally take second argument specifying number of decimal places.
split
Splits a string in an array. Similar to split method of string object in JavaScript.
compact
Removes all zero, NaN, or empty strings from an array.
join
Concatenates an array of string into a single string. Similar to join method of array object in JavaScript.
concatenate
Takes any number of arguments and joins them into a string. Similar to the CONCATENATE function in Excel.
slice
Create a new array as a sub-section of an existing array. Similar to slice method of array object in JavaScript.
replace
First argument is the source string, second argument is a regular expression (as defined in JavaScript), and third argument is replacement string. Searches source string for all matches to regular expression and replaces each match with the replacement string.
htmlEntitiesEncode
Substitutes HTML special characters with the equivalent entity. Replace ampersand, double quote, single quote, less than, and greater than characters.
quoted
Wraps first argument in double quotes after escaping all double quotes and backslashes within the string.
First argument is a name and second argument an email address. Returns address properly quoted for including in an email header, handling case where name or email address is missing.
dateToIso: (dateFormatted, format)
Converts dateFormatted that's being passed in format, to the ISO format.
Ex: dateToIso("09/25/2024", "MM/DD/YYYY") returns '2024-09-25T07:00:00.000Z'
dateDiff: (date1, date2, unit)
Returns the difference between date1 and date2 in unit. First date minus second date. Ex:
dateDiff("9/25/2024", "11/27/2024", "months") returns -2
DateDiff("9/27/2024", "09/25/2024", "days") returns 2
today
Returns the current date in MM/DD/YYYY. You can use it with dateDiff().
Ex (suppose today is 26/09/2023):
dateDiff(today(), "11/26/2023", "months") returns -2