Documentation

Function library

Core functions

These functions are Fusion's default methods.

abs(value: number): number

Computes the absolute value of a number (ie the number without its sign).

For example: abs(-2) will return the value 2.

Arguments

value: NUMBER

acos(value: number): number

Returns the arccosine of a number. The arccosine is the angle whose cosine is a number. The returned angle is given in radians in the range 0 to Π.

For example: acos(1) will return the value 0.

Arguments

value: NUMBER

all_token_match(value: any): same_as_first_argument

Returns a value tagged with a modifier that allows search and lookups to be performed in a tokenized manner, for which all terms appear somewhere in the result.

Arguments

value: ANY

any_token_match(value: any): same_as_first_argument

Returns a value tagged with a modifier that allows search and lookups to be performed in a tokenized manner, for which at least one of the terms appears somewhere in the result.

Arguments

value: ANY

array([arg: any, ...]): string

Creates an array of all input attributes. The resulting array will be surrounded by [square brackets]. All attributes will be listed in a single cell.

For example: array('John', 'Mary', 'Richard') results in the array [ John, Mary, Richard ]

Note: If the attributes are strings, they need to be enclosed in 'single quotations'.

Arguments

arg: ANY

array_concat([array: array, ...]): array

Concatenates all input arrays into one single array.

For example: Let's say you have the array [1,2,3] in cell A1 and array [4,5,6] in cell A2. Array_concat(A1,A2) will return the new array [1,2,3,4,5,6].

Arguments

array: ARRAY

array_contains(array: array, value: any): boolean

Checks whether the array contains value. Function returns true if the array contains the value.

For example: If the array [ John, Mary, Richard ] is in cell E7, implementing array_contains(E7, 'Richard') will check for the value 'Richard' within the array. In this case, the function will return True. If we entered array_contains(E7, 'Louise'), the function will return False.

Arguments

array: ARRAY
value: ANY

array_difference(array: array, [differenceArrays: array, ...]): array

Returns all the unique elements in the first array that are not in any of the other arrays with no guarantee on order.

For example: Given array [ John, Mary, Richard, Richard ] is in cell E7, and array [ John, Mary, Bob] is in cell E8, array_difference(E7, E8) will return [ Richard ].

Arguments

array: ARRAY
differenceArrays: ARRAY

array_distinct([value: any, ...]): array

Returns an array containing only the distinct values of the input arguments.

For example: array_distinct(array(3, 2, 1), 4, array(1, 2)) will return array(3, 1, 4, 2).

Note: The ordering of the values is not preserved. Additionally, any tags (e.g. fuzzy or exact) attached to the values will be dropped.

Arguments

value: ANY

array_flatten([arg: any, ...]): string

Creates an array of all input attributes. The resulting array will be surrounded by [square brackets]. All attributes will be listed in a single cell. This function is similar to array, but will collapse cell ranges and arrays, taking the values left-to-right, row-by-row. Empty values and errors in ranges will be ignored.

For example: array_flatten('John', A1:B2) where A1 = array('Zoe', 'Charles'), A2 = 'Mary' and B2 = 'Richard', results in the array [ John, Zoe, Charles, Mary, Richard ]. Notice that the array in cell A1 was flattened into the output, and that the empty cell B1 was skipped.

Note: If the attributes are strings, they need to be enclosed in 'single quotations'.

Arguments

arg: ANY

array_get_at_index(array: array, index: number): any

Returns the element at position index (1-indexed) from the specified array.

For example: If the array [ John, Mary, Richard ] is in cell E7, and we want to verify which attribute is in position 3, we can use array_get_at_index(E7, 3). This will return Richard, the third attribute in the array.

Arguments

array: ARRAY
index: NUMBER

array_get_first(array: array): any

Gets the first element of array.

Arguments

array: ARRAY

array_get_last(array: array): any

Gets the last element of array.

Arguments

array: ARRAY

array_intersection(array: array, [intersectionArrays: array, ...]): array

Returns all the unique elements that are in all of the given arrays with no guarantee on order.

For example: Given array [ John, Mary, Mary, Richard ] is in cell E7, and array [ John, Mary, John, Bob] is in cell E8, array_intersection(E7, E8) will return [ John, Mary ].

Arguments

array: ARRAY
intersectionArrays: ARRAY

array_length(array: array): number

Returns the length of the given array.

For example: If the array [ John, Mary, Richard ] is in cell E7 and we want to determine the length of the array, we can write: array_length(E7). The function will return the value 3, as there are three attributes in the array.

Arguments

array: ARRAY

array_slice(array: array, start_index: number, [end_index: number]): array

Slices array starting at start_index (inclusive) and ending at end_index (inclusive) and returns an array.

For example: If the array [ John, Mary, Richard ] is in cell E7, array_slice(E7, 1, 2) will return [ John, Mary ]. If start_index is zero or greater than the length of the array, an empty array is returned. e.g. array_slice(E7, 5, 2) → []. If start_index is negative, it is used as an offset from the end of the array. e.g. array_slice(E7, -2, 2) → [ Mary ]. If end_index is zero or greater than the length of the array, a subarray until the end of the array is extracted. e.g. array_slice(E7, 2, 5) → [ Mary, Richard ]. If end_index is negative, it is used as an offset from the end of the array. e.g. array_slice(E7, 1, -2) → [ John, Mary ].

Arguments

array: ARRAY
start_index: NUMBER
end_index: NUMBER

array_sort(array: array, [sort_direction: any]): array

Returns the given array sorted ascending. You can specify FALSE or 'DESC' as the second parameter to sort descending.

For example: If the array [ 3, 4, 1 ] is in cell E7 and we want to sort the array, we can write: array_sort(E7, 'DESC'). The function will return the array [ 4, 3, 1 ].

Arguments

array: ARRAY
sort_direction: ANY

array_zip([array: array, ...]): array

Creates an array of grouped elements, the first of which contains the first elements of the given arrays, the second of which contains the second elements of the given arrays, and so on. The resulting array's length will be equal to the length of the shortest input array.

For example: If you have the array ['a', 'b', 'c'] in cell A1 and array [1, 2, 3] in cell A2. array_zip(A1, A2) will return the new array [ [ 'a', '1' ], [ 'b', '2' ], [ 'c', '3' ] ].

Arguments

array: ARRAY

asin(value: number): number

Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -Π/2 to Π/2.

For example: asin(-1) will return the value -Π/2.

Arguments

value: NUMBER

atan(value: number): number

Returns the arctangent of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -Π/2 to Π/2.

For example: atan(0) will return the value 0.

Arguments

value: NUMBER

atan2(x_num: number, y_num: number): number

Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -Π to Π, excluding -Π.

For example: atan2(1,1) will return the value 0.785398163.

Arguments

x_num: NUMBER
y_num: NUMBER

avg([range: range, ...]): number

Computes the numeric average of a specified range. This range can be entered as a set of values or a range of values.

For example: avg(5, 7, 11) will return 7.66.

Arguments

range: RANGE

binary([value: any, ...]): binary

Create a binary object from numbers. Every number will be considered as an unsigned byte (0-255). Higher order bits will be ignored.

For example: binary(0, 0, 127).

Arguments

value: ANY

branch(dataset_path: string, branch_name: string): same_as_first_argument

Returns dataset_path tagged with branch_name. Apply to the first argument in a lookup to specify the branch to use in the search.

Note: If you are unable to find a dataset which is newly indexed, either refresh the page or navigate to Find and use data, select Indexed datasets, and then select the refresh button.

Arguments

dataset_path: STRING
branch_name: STRING

case_toggle(value: string): string

Changes uppercase to lowercase and vice-versa. Numbers are left unchanged.

Arguments

value: STRING

cbrt(value: number): number

Computes the cubic root of the given value.

For example: cbrt(8) will return 2.

Arguments

value: NUMBER

ceil(value: number): number

Computes the ceiling of the given value by rounding up to the nearest number without decimals.

For example: Ceil(5.2) will return 6.

Arguments

value: NUMBER

checkbox([checked: boolean], [label: string]): boolean

Renders a checkbox that returns true if checked and false otherwise, with an optional label. Defaults to false and unchecked if no checked argument is provided.

Arguments

checked: BOOLEAN
label: STRING

coalesce([arg: any, ...]): any

Returns the first attribute that is not null. Or, if all attributes are null, will return null.

For example: Let's say we have a column A with a mix of names and null values. If we use coalesce(columnA), the function will return the first name available.

Arguments

arg: ANY

color(cell_value: any, [text_color: string], [background_color: string]): any

Renders a cell with specified text and background color.

Arguments

cell_value: ANY
text_color: STRING
background_color: STRING

concat([arg: string, ...]): string

Concatenates multiple input string attributes together into a single string attribute.

For example: Let's say the first name John lives in cell A2, and the last name Smith lives in cell B2. Using the concat function, we can type concat(A2, ' ', B2) in C2 to get the string 'John Smith'.

All collection type arguments are recursively flattened. For example, concat(array(1, 2), array(array(3, 4, 5)), 6) will return the string '123456'.

arg: STRING

concat_ws(separator: string, [arg: string, ...]): string

Concatenates multiple input string attributes together into a single string attribute with a separator between all arguments. All collection type arguments will be recursively flattened.

For example: Let's say that the first name John lives in cell A2, and the last name Smith lives in cell B2. Using the concat_ws function, we can type concat_ws('_',A2, B2) in C2 and get the result 'John_Smith'.

All collection type arguments are recursively flattened like in the concat function.

Arguments

separator: STRING
arg: STRING

cos(value: number): number

Computes the cosine of the given value.

For example: cos(190) will return 0.066.

Arguments

value: NUMBER

count([range: range, ...]): number

Returns the number of items in a group.

For example: Let's say we want to know the count of objects within a particular column. We can select the count function, highlight the column, and retrieve a value.

Arguments

range: RANGE

count_distinct([range: range, ...]): number

Returns the number of distinct items in a group.

For example: count_distinct(columnA) will return the distinct number of objects within this column. This function can also be applied to arrays and will return the distinct number of objects within that array.

Arguments

range: RANGE

count_numeric([value: any, ...]): number

Counts the number of numeric items in a group.

For example: Let's say you have 100 rows containing a variety of names and numbers. To determine only the number of numeric items in this column, use count_numeric(A1:A100) to obtain the value.

Arguments

value: ANY

countif(range: range, criteria: any): number

Returns the number of items in range range that is equal to a specified criteria.

For example: Let's say Column A contains a list of animals and you want to know how many times 'Dog' is listed in the first 100 rows. Use countif(A1:A100, 'Dog') to receive a unique count.

Arguments

range: RANGE
criteria: ANY

countifs([range: range, criteria: any, ...]): number

Returns the number of items in range range that is equal to criteria for all range, criteria pairs.

For example: countifs(A1:A100, 'Red', B1:B100, 2) will return the count of all values that contain the value Red in Column A and the number 2 in Column B.

Arguments

range: RANGE
criteria: ANY

date(year: number, month: number, day: number): date_time

Creates a date with a defined year, month, day in the format yyyy-MM-dd.

For dates where the year is < 1900 (e.g. '97'), the year will be interpreted as an offset from 1900 (e.g. '1997').

Arguments

year: NUMBER
month: NUMBER
day: NUMBER

date_add(dateOrDaysLeft: any, dateOrDaysRight: any): date_time

Returns the date or timestamp that is the result of dateOrDaysLeft plus dateOfDaysRight. Each parameter can either be a number of days or fraction of days or a date. The date must be in the format yyyy-MM-dd.

For example: Let's say that we want to know the date 40 days after 2021-05-06. This date lives in cell D2. Use date_add(D2, 40) to get 2021-06-15. For example: If I want the timestamp for the date 2021-05-06 plus half a day. This date lives in cell D2. Use date_add(D2, 0.5) to get 2021-05-06 12:00.

Arguments

dateOrDaysLeft: ANY
dateOrDaysRight: ANY

date_diff(start: date_time, end: date_time): number

Returns the number of days from start to end. The dates must be in the format yyyy-MM-dd.

For example: Let's say we want to know the number of days between 2021-01-15 and 2021-06-15. These cells live in B2 and B5 respectively. Use date_diff (B2, B5) to get a -151 day difference.

Arguments

start: DATE_TIME
end: DATE_TIME

date_format(date: date_time, format: string): string

Converts a date/timestamp date to a string in the format specified by the string in format. The format can be variations of the string yyyy-MM-dd.

For example: Re-format the date 2021-05-06 to 05-06-21 with date_format('2021-05-06', 'MM-dd-yy').

Arguments

date: DATE_TIME
format: STRING

date_sub(dateOrDaysLeft: any, dateOfDaysRight: any): date_time

Returns the date or timestamp that is the result of dateOrDaysLeft minus dateOfDaysRight. Each parameter can either be a number of days or fraction of days or a date. The date must be in the format yyyy-MM-dd.

For example: Let's say we want to know the date 40 days prior to 2021-05-06. This date lives in cell D2. Use date_sub(D2, 40) to get 2021-03-27. For example: If I want the timestamp for the date 2021-05-06 minus half a day. This date lives in cell D2. Use date_sub(D2, 0.5) to get 2021-05-05 12:00. For example: If I want the difference in days between two dates where one date is in cell D1 and the other in cell D2. Use date_sub(D1, D2).

Arguments

dateOrDaysLeft: ANY
dateOfDaysRight: ANY

datepicker([selectedDateTime: date_time], [timePrecision: string]): date_time

Returns a datepicker with the selected date. The date must be in the format yyyy-MM-dd with optionally HH:mm (time information). Time precision must be one of 'NONE', 'MINUTE', or 'SECOND'.

Arguments

selectedDateTime: DATE_TIME
timePrecision: STRING

day_of_month(date: date_time): number

Extracts the day of the month as an integer from a given date/timestamp/string.

For example: Let's say the date 2021-06-18 lives in cell B2. day_of_month(B2) will return 18.

Arguments

date: DATE_TIME

day_of_year(date: date_time): number

Calculates the day of the year as an integer from a given date/timestamp/string.

For example: Let's say the date 2021-06-18 lives in cell B2. day_of_year(B2) will return 169.

Arguments

date: DATE_TIME

document_metadata(key: string): string

Accesses document metadata. To implement, pass in the key of the metadata field you need.

Supported keys:

creator returns the creator of the document (if known).
document_identifier returns the internal identifier of the document (used, for example, in the submit_to_region_with_key function)

For example: document_metadata('creator') will return the username of the document's creator.

Arguments

key: STRING

dropdown(values: array, [selected_value: string], [allow_invalid: boolean], [placeholder_text: string]): any

Renders a dropdown with values that can be selected from the supplied values array. The values must be unique, as each value is converted to a string label in the dropdown.

For example, =dropdown(array('red', 'blue', 'green')) will create a dropdown with values 'red', 'blue', 'green'.

When a selected_value is supplied, it specifies a default string label for the dropdown. selected_value is optional and can be set to null, which will default the dropdown's selection to an unselected state. If selected_value is set and the selected label is no longer found in the values array (such as if the underlying data changes), the result will be an empty cell.

When allow_invalid is set to true, it will override this behavior and still output the previously saved selection. This result can only be a string and is only recommended when trying to preserve the prior selection from a set of string values.

placeholder_text allows you to show a text in the dropdown when no value is selected (it can be useful as a prompt or instructions)

Arguments

values: ARRAY
selected_value: STRING
allow_invalid: BOOLEAN
placeholder_text: STRING

empty_cell(): any

Return an empty result. Useful when combined with the if or iferror functions (e.g. if(not isnull(A1), A1, empty_cell()))

eq(value: any): same_as_first_argument

Mark the current value as an equality (==) comparison.

Apply to an argument in a lookup function to change the search behavior.

Arguments

value: ANY

exact(value: any): same_as_first_argument

Returns a value tagged with a modifier to make searches and lookups seek exact matches to the value.

Arguments

value: ANY

exp(value: number): number

Returns e raised to the power of value. The constant e equals 2.718, the base of the natural logarithm.

For example: exp(2) returns 7.389. This is the result of the natural logarithm e raised to the power of 2.

Arguments

value: NUMBER

experimental_add_tags(value: any, tags: array): same_as_first_argument [EXPERIMENTAL]

Adds the tags to value and returns the new tagged value.

Arguments

value: ANY
tags: ARRAY

experimental_copy_and_sync_button(label: string, folder: string, name: string, [exports: array], [onCompleteMessage: string], [onFailureMessage: string], [redirectToHomeAfterCompletion: boolean]): same_as_first_argument [EXPERIMENTAL]

Render a button, with label label, that, when clicked, will copy the spreadsheet to folder, with the name name.

Exports will be copied using the following pattern: ‘$name - Export $sheetName'. The exports can be constrained by passing in the sheet names of the desired exports in exports. If any exports are missing the copy will fail. Completion and failure messages can be passed in onCompleteMessage and onFailureMessage respectively. If redirectToHomeAfterCompletion is set to true, the user will be redirected to their home folder after completion.

Arguments

label: STRING
folder: STRING
name: STRING
exports: ARRAY
onCompleteMessage: STRING
onFailureMessage: STRING
redirectToHomeAfterCompletion: BOOLEAN

experimental_error([value: string, ...]): any [EXPERIMENTAL]

Output an error.

Arguments

value: STRING

experimental_get_tags(value: any): array [EXPERIMENTAL]

Returns the array of tags associated with value.

Arguments

value: ANY

experimental_io_blocking_function([extra_delay: number]): string [EXPERIMENTAL]

Test method that blocks I/O until timed out. Optionally can be given an extra delay to wait after being timed out to simulate badly behaving I/O functions.

Arguments

extra_delay: NUMBER

experimental_range(width: number, [value: any, ...]): range [EXPERIMENTAL]

Output a range.

Arguments

width: NUMBER
value: ANY

experimental_remove_tags(value: any, [tags: array]): same_as_first_argument [EXPERIMENTAL]

Removes the tags in array tags from value, or removes all tags from value if the tags argument is absent.

Arguments

value: ANY
tags: ARRAY

factorial(value: number): number

Computes the factorial of the given value.

For example: factorial(3) will return 6.

Arguments

value: NUMBER

find(search_for: string, text_to_search: string, [starting_index: number]): number

Returns the index of the first instance of search_for in the string text_to_search. You can optionally provide a starting_index for the search.

For example: Let's say you have the phrase 'The grey cat chased the grey mouse' in cell A1. To determine the index of the first instance of 'grey', implement find('grey', A1) to get index 5.

Arguments

search_for: STRING
text_to_search: STRING
starting_index: NUMBER

floor(value: number): number

Computes the floor of the given value by rounding down to the nearest number without decimals.

For example: floor(3.2) will round to 3. And, floor (-4.5) will round to -5.

Arguments

value: NUMBER

format_number(value: number, decimalPlaces: number): number

Formats the numeric cell value to a format like #,###.##, rounded to the number of decimals specified by decimalPlaces. The function returns the result as a string.

For example: Let's say you have a column of 4 digit numbers in column A and you want to include thousand separators as well as 2 decimal places. Use format_number(A1, 2) to reformat the first number to #,###.00. Drag down the box to apply to the rest of Column A.

Arguments

value: NUMBER
decimalPlaces: NUMBER

format_string([format: string, ...]): number

Formats arg in printf-style and returns the result as a string attribute using format.

Arguments

format: STRING

fuzzy(value: any, [distance: number]): same_as_first_argument

Returns a value tagged with a modifier that allows search and lookups to be performed in a fuzzy manner, where the searched value is within distance edits of the real value. Only edit distances of 0, 1 and 2 are supported, defaulting to 2.

The distance corresponds to the Levenshtein distance, a measure of the number of single-character edits required to go from one string to another.

Arguments

value: ANY
distance: NUMBER

get_object_rid(object_or_property: any): string

Returns the object RID of the given object or object property.

Arguments

object_or_property: ANY

get_object_type_id(object_or_property: any): string

Returns the object type id of the given object or object property.

Arguments

object_or_property: ANY

get_property(object: object, key: string): object

Returns the given property of the object.

Arguments

object: OBJECT
key: STRING

gt(value: any): same_as_first_argument

Mark the current value as a greater-than (>) comparison.

Apply to an argument in a lookup function to change the search behavior.

Arguments

value: ANY

gte(value: any): same_as_first_argument

Mark the current value as a greater-than or equal to (>=) comparison.

Apply to an argument in a lookup function to change the search behavior.

Arguments

value: ANY

hlookup(value: any, range: range, row: number): any

Lookup value in the first row of range and grab row (starting at 1).

For example: Say Column A is a list of colors. hlookup(A1,A1:A100,5) will return the color in the 5th row of that column.

Arguments

value: ANY
range: RANGE
row: NUMBER

hour(date: date_time): number

Extracts the hour as an integer from a given date/timestamp/string. This function will ignore the dates and minutes within the string.

For example: Let's say you have the timestamp 2021-02-15 08:30:15 in cell A2. hour(A2) will return 8.

Arguments

date: DATE_TIME

if(condition: boolean, value_if_true: any, value_if_false: any): any

If the condition evaluates to true, return the value specified in value_if_true. If the condition evaluates to false, return the value specified in value_if_false.

For example: Say we implement the function if(A1 >=5, 'True', 'False'). If A1 equals 6, the function will return True. If A1 equals 4, the function will return False.

Arguments

condition: BOOLEAN
value_if_true: ANY
value_if_false: ANY

iferror(value: any, value_if_error: any): any

Returns value if no error is detected. Otherwise, throws the error specified by value_if_error. This function provides a more elegant solution to managing error messaging.

For example: Say you have the simple formula A1/B2 in cell C3. If B2 is blank, you want the formula to throw the error 'Enter a value in B2.' Implement iferror(A1/B2, 'Enter a value in B2.').

Arguments

value: ANY
value_if_error: ANY

index(range: range, row_offset: number, column_offset: number): any

Returns the content of a cell within a range, specified by the row_offset and column_offset. The row_offset specifies the row you want to pull data from while the column_offset specifies the column you want to pull data from.

For example: index(A1:C6, 2, 3) means that you want to pull data from the range A1 from row 2, column 3.

Note: If the row offset is set to 0, an entire column will be selected as specified by column_offset. If the column offset is 0, an entire row will be selected as specified by row_offset. If both are zero, the entire range of cells will be selected.

Arguments

range: RANGE
row_offset: NUMBER
column_offset: NUMBER

internal_region_result(region_id: string): any [EXPERIMENTAL]

Internal function.

Arguments

region_id: STRING

isNull(value: any): boolean

Returns true if the attribute is null or an empty cell. Otherwise, the function returns false.

For example: Let's say column A contains a mix of integers and null values. Use isnull(A1) to determine if the first value in the column is true (ie a null value) or an integer. Drag down this formula to apply to the rest of the column.

Arguments

value: ANY

last_day(date: date_time): number

Given a date attribute, the function returns the last day of the specified month in the format yyyy-MM-dd. Note: The date must be entered as a string.

For example: Let's say the current date is 2021-02-01 and you want to compute the last date of the month. Use last_day('2021-02-01') to get 2021-02-28. Note: A date in January will always return 31, while a date in February will return 28 or 29, depending on the year.

Arguments

date: DATE_TIME

left(text: string, [num_chars: number]): string

Returns the number of characters specified in num_chars from the start of string text.

For example: If the string 'John Smith' is in cell A2 , right(A2, 3) will return ith.

Arguments

text: STRING
num_chars: NUMBER

length(value: any): number

Computes the length of a given string or binary attribute based on the number of characters.

For example: length('John Smith') will return 10.

Arguments

value: ANY

ln(value: number): number

Computes the natural logarithm of the given value. If the value is 0 or below, the function will return an error.

For example: ln(7) will return 1.9459.

Arguments

value: NUMBER

log(value: number, base: number): number

Computes the logarithm of value with the base specified in base.

For example: log(8,2) will return 3.

Arguments

value: NUMBER
base: NUMBER

lookup(dataset_path: string, result_column: string, [column: string, value: string, ...]): any

Returns values from the result_column in dataset_path that match the filters defined in the column, value pairs.

For example: lookup('/Users/me/myData', 'my_column', 'first_name', 'John', 'last_name', 'Doe') will search through the dataset '/Users/me/myData' for the row(s) where first_name = 'John' and last_name = 'Doe' in the column 'my_column'. The function will grab the value(s) of my_column that match the filters.

Note:

Value can be wrapped using exact or fuzzy functions to specify if matches should be exact or fuzzy. Dataset_path can be wrapped using the branch function to specify a branch of the dataset to lookup.
If you are unable to find a dataset which is newly indexed, either refresh the page or navigate to Find and use data, select Indexed datasets, and then select the refresh button.

Arguments

dataset_path: STRING
result_column: STRING
column: STRING
value: STRING

lookup_array(dataset_path: string, result_column: string, [column: string, value: string, ...]): any

Returns matching values from result_column in dataset_path as a sorted array based on a global ordering of the rows. Results are filtered using subsequent arguments defined in column, value pairs.

For example: lookup('/Users/me/myData', 'my_column', 'first_name', 'John', 'last_name', 'Doe') will search through the dataset at '/Users/me/myData' for the rows where first_name = 'John' and last_name = 'Doe', and grab the values of my_column in matching rows.

Note:

Value can be wrapped using exact or fuzzy functions to specify if matches should be exact or fuzzy.
If you are unable to find a dataset which is newly indexed, either refresh the page or navigate to Find and use data, select Indexed datasets, and then select the refresh button.

Arguments

dataset_path: STRING
result_column: STRING
column: STRING
value: STRING

lookup_distinct(dataset_path: string, result_column: string, [column: string, value: string, ...]): any

Returns distinct values from the result_column in dataset_path that match the filters defined in the column, value pair.

For example: lookup_distinct('/Users/me/myData', 'my_column', 'first_name', 'John') will search through the dataset '/Users/me/myData' for the row(s) where first_name = 'John' in the column 'my_column'. The function will grab the distinct value(s) of 'my_column' that match the filters.

Note:

Value can be wrapped using exact or fuzzy functions to specify if matches should be exact or fuzzy. Dataset_path can be wrapped using the branch function to specify a branch of the dataset to lookup.
If you are unable to find a dataset which is newly indexed, either refresh the page or navigate to Find and use data, select Indexed datasets, and then select the refresh button.

Arguments

dataset_path: STRING
result_column: STRING
column: STRING
value: STRING

lookup_dropdown(dataset_path: string, result_column: string, [selected_value: string], [column: string, value: string, ...]): any

Returns a dropdown with suggested values from the result_column in dataset_path, selected_value is the current value. selected_value can be set to null, which will default the dropdown's selection to an unselected state.

For example: lookup_dropdown('/Users/me/myData', 'my_column', NULL, 'first_name', 'John') will return a dropdown with options from '/Users/me/myData' for the row(s) where first_name = 'John' in the column 'my_column'. The function will grab the distinct value(s) of 'my_column' that match the filters.

Note: If you are unable to find a dataset which is newly indexed, either refresh the page or navigate to Find and use data, select Indexed datasets, and then select the refresh button.

Arguments

dataset_path: STRING
result_column: STRING
selected_value: STRING
column: STRING
value: STRING

lookup_schema(datasource_path: string, [branch: string]): array

Returns the column names for the datasource specified by datasource_path. You can optionally provide a datasource branch for your search.

Note:

Column names will be returned as an array.
If you are unable to find a dataset which is newly indexed, either refresh the page or navigate to Find and use data, select Indexed datasets, and then select the refresh button.

Arguments

datasource_path: STRING
branch: STRING

[DEPRECATED] lookup_set(dataset_path: string, result_column: string, [column: string, value: string, ...]): any

Returns matching values from result_column in dataset_path as an unordered set. Results are filtered using subsequent arguments defined in column, value pairs.

For example: lookup('/Users/me/myData', 'my_column', 'first_name', 'John', 'last_name', 'Doe') will search through the dataset at '/Users/me/myData' for the rows where first_name = 'John' and last_name = 'Doe', and grab the values of my_column in matching rows.

Note:

Value can be wrapped using exact or fuzzy functions to specify if matches should be exact or fuzzy.
If you are unable to find a dataset which is newly indexed, either refresh the page or navigate to Find and use data, select Indexed datasets, and then select the refresh button.

Arguments

dataset_path: STRING
result_column: STRING
column: STRING
value: STRING

lookup_sorted(dataset_path: string, result_column: string, sort_column: string, sort_direction: string, [column: string, value: string, ...]): any

Returns values from the column result_column in dataset_path, sorted by the sort_column in a specified sort_direction. The data in 'result_column' can be filtered using subsequent arguments defined in column, value pairs.

Note:

The sort_column, result_column, and columns must be column names in the dataset dataset_path. Additionally, the sort_direction can be either 'ASC' or 'DESC', for ascending and descending respectively.
Value can be wrapped using exact or fuzzy functions to specify if matches should be exact or fuzzy.
If you are unable to find a dataset which is newly indexed, either refresh the page or navigate to Find and use data, select Indexed datasets, and then select the refresh button.

Arguments

dataset_path: STRING
result_column: STRING
sort_column: STRING
sort_direction: STRING
column: STRING
value: STRING

lower(value: string): string

Converts a string specified in value to lower case.

For example: Let's say the string 'JANE DOE' is in B2. lower(B2) will return 'jane doe'.

Arguments

value: STRING

lpad(value: string, length: number, pad: string): string

Left-pads the string attribute in value up to length length with the string specified in pad.

For example: Let's say you want to left-pad a string of phone numbers in column A with the string 'NY-'. Each padded phone number will have 13 characters. To implement, use lpad(A1, 13, 'NY-') and get NY-##########.

Arguments

value: STRING
length: NUMBER
pad: STRING

lt(value: any): same_as_first_argument

Mark the current value as a less-than (<) comparison.

Apply to an argument in a lookup function to change the search behavior.

Arguments

value: ANY

lte(value: any): same_as_first_argument

Mark the current value as a less-than or equal to (<=) comparison.

Apply to an argument in a lookup function to change the search behavior.

Arguments

value: ANY

ltrim(value: string): string

Trims the spaces from the left end of the string in value.

For example: If the string 'John Smith' is in cell A2 and has 4 leading spaces, ltrim(A2) will remove the spaces.

Arguments

value: STRING

match(item: any, range: range, criteria: number): same_as_first_argument

Search for an item in range via specified criteria. The function returns the relative position of that item within the range (1-indexed).

Possible codes for criteria are as follows:

1 finds the largest value that is less than or equal to item. The values in the range defined by range must be placed in ascending order.
0 finds the first value that is exactly equal to item. The values in the range defined by range can be in any order.
-1 finds the smallest value that is greater than or equal to item. The values in the range defined by range must be placed in descending order.

For example: If the range A1:A3 contains the values 5, 25, and 38, then the formula match(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

Arguments

item: ANY
range: RANGE
criteria: NUMBER

max([value: any, ...]): any

Returns the maximum value in a group of numbers, dates or timestamps.

For example: max(1,5,23) will return 23. Or, max(A1:A100) will return the greatest value within this range. max(date(2021, 2, 2), date(2021, 2, 1)) will return date(2021, 2, 2). max(parse_timestamp('2021-02-02 00:00:01', 'yyyy-MM-dd HH:mm:ss'), parse_timestamp('2021-02-02 00:00:02', 'yyyy-MM-dd HH:mm:ss')) will return this timestamp '2021-02-02 00:00:02'.

Arguments

value: ANY

md5(value: any): string

Calculates the MD5 digest of a string and returns the value as a 32 character hex string.

For example: Let's say you have the string 'John Smith' in cell A2. md5(A2) will return 6117323d2cabbc17d44c2b44587f682c.

Arguments

value: ANY

mean([value: number, ...]): number

Returns the average of the numeric values in a group.

For example: mean(5,8,12) will result in 8.33.

Arguments

value: NUMBER

median([value: number, ...]): number

Returns the median of the numeric values in a group.

For example: median(10, 11, 19, 20, 21) will return 19. Or, median(A1:A100) will return the median value within this range.

Arguments

value: NUMBER

min([value: any, ...]): any

Returns the minimum value in a group of numbers, dates or timestamps.

For example: min(5,8,12) will return 5. Or, min(A1:A100) will return the smallest value within that range. min(date(2021, 2, 2), date(2021, 2, 1)) will return date(2021, 2, 1) min(parse_timestamp('2021-02-02 00:00:01', 'yyyy-MM-dd HH:mm:ss'), parse_timestamp('2021-02-02 00:00:02', 'yyyy-MM-dd HH:mm:ss')) will return this timestamp '2021-02-02 00:00:01’

Arguments

value: ANY

minute(date: date_time): number

Extracts the minute of date as an integer.

For example: Let's say you have the timestamp 2021-02-15 08:30:15 in cell A2. minute(A2) will return 30.

Arguments

date: DATE_TIME

month(date: date_time): number

Extracts the month of date as an integer.

For example: Let's say you have the timestamp 2021-02-15 08:30:15 in cell A2. month(A2) will return 2.

Arguments

date: DATE_TIME

months_between(start: date_time, end: date_time): number

Returns the number of months between dates start and end.

For example: months_between('2020-02-15', '2022-01-15') will return 23.

Arguments

start: DATE_TIME
end: DATE_TIME

multidropdown(values: array, [selected_value: array], [allow_invalid: boolean], [placeholder_text: string]): any

Renders a dropdown with values selectable from the supplied values array. Unlike the dropdown function, multidropdown allows the user to select more than one value at a time. These values must be unique, as each value will be converted to a string equivalent label.

Note: If selected_value is set and the selected label is no longer found in the values array (such as if the underlying data changes), the result will be an empty cell.

Setting allow_invalid to true will override this behavior and still output the previously saved selection. This result can currently only be a string and this mode is only recommended when trying to preserve the previous selection from a set of string values.

placeholder_text allows a text to be shown in the dropdown when no value is selected (this can be useful as a prompt or instructions).

Arguments

values: ARRAY
selected_value: ARRAY
allow_invalid: BOOLEAN
placeholder_text: STRING

neq(value: any): same_as_first_argument

Mark the current value as an inequality (!=) comparison.

Apply to an argument in a lookup function to change the search behavior.

Arguments

value: ANY

net_workdays(start_date: date_time, end_date: date_time, [holidays: any]): number

Returns the number of whole working days between start_date and end_date. Working days exclude weekends. As of now, this function does not support holidays as an argument.

For example: net_workdays('2021-01-01','2021-02-01') would return 23 since those were the number of work days during the month of January, ignoring holidays.

Arguments

start_date: DATE_TIME
end_date: DATE_TIME
holidays: ANY

object(objectRid: string): object [EXPERIMENTAL]

Loads the object with the given object RID.

Arguments

objectRid: STRING

object_dropdown(object_set: array, [selected_object_rid: string], [placeholder_text: string]): object [EXPERIMENTAL]

Returns a dropdown with suggested values from an object set, selected_object_rid is the RID of the currently selected object from the object set.

Arguments

object_set: ARRAY
selected_object_rid: STRING
placeholder_text: STRING

object_set(objectSetRid: string, [paramKey: string, paramValue: any, ...]): array [EXPERIMENTAL]

Loads the object set with the given object set RID.

Arguments

objectSetRid: STRING
paramKey: STRING
paramValue: ANY

parse_date(date_string: string, [formats: string, ...]): date

Parse a string as a date. If multiple formats are specified, they will be tried in-order until one succeeds.

For example: parse_date('25/01/2022', 'dd/MM/yyyy') will return the date of the 25th of January 2022.

Details on patterns for formatting and parsing dates can be found in the Java DateTimeFormatter documentation ↗.

Arguments

date_string: STRING
formats: STRING

parse_timestamp(timestamp_string: string, [formats: string, ...]): date_time

Parse a string as a timestamp. If multiple formats are specified, they will be tried in-order until one succeeds.

For example: parse_timestamp('25/01/2022 12:03', 'dd/MM/yyyy HH:mm') will return the date of the 25th of January 2022, at 3 minutes past noon.

Arguments

timestamp_string: STRING
formats: STRING

percentile(array: array, pp: number): number

Returns the number for which pp values in the array lie below it.

array: the array of positive/negative numbers not necessarily sorted.
pp: the percentile, must be between 0 and 1 inclusive. pp=0 will return the min value of the array, pp=0.5 the median, and pp=1 the max value.
Examples:
- =percentile(array(7.25, 5.3, 8, 10), 0.25) returns 6.7625, the value for which 25% of the array values lie below it.
- =percentile(array(12, 20, 10, 25, 28, 30, 34, 60), 0) returns 10, the min value in the array.
- =percentile(A2:A9, A13) can also be used with ranges and cell references.
- =percentile(array_flatten(A2:A9), A13)

Arguments

array: ARRAY
pp: NUMBER

pow(value: number, power: number): number

Returns the result of value raised to the power of power.

For example: pow(5, 2) will return 25.

Arguments

value: NUMBER
power: NUMBER

product([value: any, ...]): number

Returns the product of all numeric values in the expression.

For example: product(6*2) will return 12.

Arguments

value: ANY

quarter(date: date_time): number

Extracts the yearly quarter of a date as an integer.

For example: Let's say the date 2021-08-15 is in cell A1. Quarter(A1) will return 3, as this date is in the third quarter of the year.

Arguments

date: DATE_TIME

query_params([key: string, value: string, ...]): string

Encodes key, value pairs to be safe to use in URL query parameters.

For example: =query_params('k1', 'this is long', 'k2', 'v+2') will give you k1=this%20is%20long&k2=v%2B2

Arguments

key: STRING
value: STRING

rank(number: number, collection: array, sortOrder: number): number

Returns the rank of a specified number in a collection of numbers ordered by sort order. The ranking will be the number's value relative to the other values in the collection.

If sort order is equal to 0, the ranking of the number within the collection will be expressed in descending order.

If sort order is equal to a non-zero number, the rankings will be expressed in ascending order.

Note: All non-numeric values in a collection are ignored.

For example: Say you have the values 94, 79, and 83, 96 in cells A1, A2, A3, and A4 respectively. Rank(94, A1:A4, 1) will return 2, as 94 is the second highest ranked value in this collection of values. If we want to determine the rank of the rest of the numbers in this column, we should change the expression of the collection to A$1$4. This way, the range will not change.

Arguments

number: NUMBER
collection: ARRAY
sortOrder: NUMBER

regexp_replace(value: string, search: string, replace: string): string

Replaces all substrings in value that match search with replace.

For example: Let's say Column A is a list of animals and some cells contain the string 'The', such as 'The Dog'. If we want to replace every 'The' with the string 'One', simply implement regexp_replace(A1, 'The', 'One'). The new cell will read 'One Dog' instead of 'The Dog'. Simply drag down the corner of the cell to apply to the rest of Column A.

Arguments

value: STRING
search: STRING
replace: STRING

reverse(value: string): string

Reverses value and returns it as a new string.

For example: Reverse('John Smith') will return htimS nhoJ.

Arguments

value: STRING

right(text: string, [num_chars: number]): string

Returns the number of characters specified in num_chars from the end of string text.

For example: If the string 'John Smith' is in cell A2 , left(A2, 3) will return Joh.

Arguments

text: STRING
num_chars: NUMBER

round(value: number, decimalPlaces: number): number

Rounds the value to the number of decimal places specified by decimalPlaces.

For example: Round(4.56, 1) will return 4.6. Round(4.56, 0) will return 5.

Arguments

value: NUMBER
decimalPlaces: NUMBER

rpad(value: string, length: number, pad: string): string

Right-pads the string attribute in value up to a specified length with the string in pad.

For example: Let's say you want to right-pad a string of phone numbers in column A with the string '-NY'. Each new number will have 13 characters. To implement, use rpad(A1, 13, '-NY') to get ##########-NY.

Arguments

value: STRING
length: NUMBER
pad: STRING

rtrim(value: string): string

Trims the spaces from the right end of a string specified by value.

For example: If the string 'John Smith' in cell A2 has 4 trailing spaces, rtrim(A2) will remove the spaces.

Arguments

value: STRING

second(date: date_time): number

Extracts the seconds in a date as an integer.

For example: Let's say you have the timestamp 2021-02-15 08:30:15 in cell A2. second(A2) will return 15.

Arguments

date: DATE_TIME

[DEPRECATED] set_get_any(set: any): any

Retrieves a random element from a given set.

Arguments

set: ANY

[DEPRECATED] set_to_array(set: any): array

Converts a given set to an array, sorted in ascending order.

For example: Say we have a set of names in A1. set_to_array(A1) will convert these names into an array.

Arguments

set: ANY

sha1(value: any): string

Calculates the SHA-1 digest of an attribute and returns the value as a 40 character hex string.

For example: sha1('The cow jumped over the moon.') will return 6e2780eb20fdaf78f6c8335d0b17526c7ef12a79.

Arguments

value: ANY

sin(value: number): number

Computes the sine of value.

For example: sin(140) will return 0.98.

Arguments

value: NUMBER

split(text: string, delimiter: string): array

Splits text with the specified delimiter and outputs the fragments in a row. Note: Empty fragments are ignored.

For example: Let's say you have the string 'Jane | 24 | F' in cell A1. To split this into an array of fragments you can use split(A1, '|') to get back '[ Jane , 24 , F ]'.

Arguments

text: STRING
delimiter: STRING

split_regex(text: string, delimiter: string): array

Splits text with the specified delimiter using regular expression syntax and outputs the fragments in a row. Note: Empty fragments are ignored.

For example: Let's say you have the string '123ABCDE456FGHIJKL789MNOPQ012' in cell A1. To split this into an array of the non-digit fragments (ie split on any digits) you can use =split_regex(A1, '\d+') to get back '[ ABCDE, FGHIJKL, MNOPQ ]'.

Arguments

text: STRING
delimiter: STRING

sqrt(value: number): number

Computes the square root of value.

For example: sqrt(16) will return 4.

Arguments

value: NUMBER

stddev([value: number, ...]): number

Returns the sample standard deviation of the expression in a group.

For example: Let's say you have the values 23, 45, 32 in cells A1, A2, and A3 respectively. stddev(A1:A3) will return 11.06.

Arguments

value: NUMBER

stddev_p([value: any, ...]): number

Returns the biased standard deviation of the expression in a group assuming that its arguments are the entire population. If they only represent a sample of the population, use STDDEV instead.

Arguments

value: ANY

submit_to_region_with_key(button_label: string, document_identifier: string, region_name: string, should_submit: any, key_column: string, key_value: any, [column: string, value: any, ...]): string

Creates a button that, when clicked, submits data into a region with a specified key.

For example: =submit_to_region_with_key('Submit!', 'ri.fusion.main.document...', 'submit_table', TRUE, 'key_column', A2:A10, 'value_column', B2:B10) will submit A2 and B2:10 to columns 'key_column' and 'value_column' respectively of the table 'submit_table', within the specified sheet.

Note: You can include any number of key column, value pairs into your parameters as you would like to appear in the receiving spreadsheet. However, you must list every parameter that you would like to appear in the exact order that they will be submitted.

Arguments

button_label: STRING
Specifies the string you want to appear on the button.
document_identifier: STRING
Insert the RID of the dataset that your submissions will be written to. The RID can be identified in the document's URL and looks like ri.fusion.main...
region_name: STRING
Specifies the name of the table region that you are writing into. If you have not already specified this table region, go to the receiving spreadsheet, use the 'create table region' widget, and give your table an intuitive name.
should_submit: ANY
Corresponds to a particular column in your submission sheet that contains a Boolean (i.e. true/false). If should_submit is true, then the data will be submitted.
key_column: STRING
Specifies the column header that you would like to submit values into. Each value in the key_column must be unique.
key_value: ANY
Signifies a specific cell or range of cells to be submitted into the specified key_column.
column: STRING
value: ANY

submit_to_region_with_key_and_timestamp(button_label: string, document_identifier: string, region_name: string, should_submit: any, timestamp_column: string, key_column: string, key_value: any, [column: string, value: any, ...]): string

Creates a button that, when clicked, submits data into a region with a specified key.

For example: =submit_to_region_with_key_and_timestamp('Submit!', 'ri.fusion.main.document...', 'submit_table', TRUE, 'time', 'key_column', A2:A10, 'value_column', B2:B10) will submit A2 and B2:10 to columns 'key_column' and 'value_column' respectively of the table 'submit_table', within the specified sheet. The current timestamp will be submitted to the 'time' column.

Arguments

button_label: STRING
Specifies the string you want to appear on the button.
document_identifier: STRING
Insert the RID of the dataset that your submissions will be written to. The RID can be identified in the document's URL and looks like ri.fusion.main...
region_name: STRING
Specifies the name of the table region that you are writing into. If you have not already specified this table region, go to the receiving spreadsheet, use the 'create table region' widget, and give your table an intuitive name.
should_submit: ANY
Corresponds to a particular column in your submission sheet that contains a Boolean (i.e. true/false). If should_submit is true, then the data will be submitted.
timestamp_column: STRING
Specifies the column header that you would like to submit the current timestamp to.
key_column: STRING
Specifies the column header that you would like to submit values into. Each value in the key_column must be unique.
key_value: ANY
Signifies a specific cell or range of cells to be submitted into the specified key_column.
column: STRING
value: ANY

submit_to_region_with_key_and_timestamp_lazy(button_label: string, document_identifier: string, region_name: string, should_submit: any, timestamp_column: string, key_column: string, key_value: any, [column: string, value: any, ...]): string

Behaves exactly like submit_to_region_with_key, except that it computes value to submit on-click, and caches target region information. Use this if you are submitting large ranges, or anticipate multiple submits to the same region in quick succession.

Creates a button that, when clicked, submits data into a region with a specified key.

For example: =submit_to_region_with_key_and_timestamp_lazy('Submit!', 'ri.fusion.main.document...', 'submit_table', TRUE, 'time', 'key_column', A2:A10, 'value_column', B2:B10) will submit A2 and B2:10 to columns 'key_column' and 'value_column' respectively of the table 'submit_table', within the specified sheet. The current timestamp will be submitted to the 'time' column.

Arguments

button_label: STRING
Specifies the string you want to appear on the button.
document_identifier: STRING
Insert the RID of the dataset that your submissions will be written to. The RID can be identified in the document's URL and looks like ri.fusion.main...
region_name: STRING
Specifies the name of the table region that you are writing into. If you have not already specified this table region, go to the receiving spreadsheet, use the 'create table region' widget, and give your table an intuitive name.
should_submit: ANY
Corresponds to a particular column in your submission sheet that contains a Boolean (i.e. true/false). If should_submit is true, then the data will be submitted.
timestamp_column: STRING
Specifies the column header that you would like to submit the current timestamp to.
key_column: STRING
Specifies the column header that you would like to submit values into. Each value in the key_column must be unique.
key_value: ANY
Signifies a specific cell or range of cells to be submitted into the specified key_column.
column: STRING
value: ANY

submit_to_region_with_key_lazy(button_label: string, document_identifier: string, region_name: string, should_submit: any, key_column: string, key_value: any, [column: string, value: any, ...]): string

Creates a button that, when clicked, submits data into a region with a specified key.

For example: =submit_to_region_with_key_lazy('Submit!', 'ri.fusion.main.document...', 'submit_table', TRUE, 'time', 'key_column', A2:A10, 'value_column', B2:B10) will submit A2 and B2:10 to columns 'key_column' and 'value_column' respectively of the table 'submit_table', within the specified sheet.

Arguments

button_label: STRING
Specifies the string you want to appear on the button.
document_identifier: STRING
Insert the RID of the dataset that your submissions will be written to. The RID can be identified in the document's URL and looks like ri.fusion.main...
region_name: STRING
Specifies the name of the table region that you are writing into. If you have not already specified this table region, go to the receiving spreadsheet, use the 'create table region' widget, and give your table an intuitive name.
should_submit: ANY
Corresponds to a particular column in your submission sheet that contains a Boolean (i.e. true/false). If should_submit is true, then the data will be submitted.
key_column: STRING
Specifies the column header that you would like to submit values into. Each value in the key_column must be unique.
key_value: ANY
Signifies a specific cell or range of cells to be submitted into the specified key_column.
column: STRING
value: ANY

substring(value: number, index: number, length: number): string

Extracts a substring of value starting at the index (1-indexed) until length length.

For example: Let's say you have the phrase 'cow jumped over the moon' in cell A1 and you want to extract the substring 'cow'. The substring begins at index 1 and the length is 3. Use substring(A1, 1, 3) to extract just the string 'cow'.

Arguments

value: NUMBER
index: NUMBER
length: NUMBER

subtotal(function_code: number, [range: range, ...]): number

Computes the aggregate specified by the function_code(outlined below) over the ranges specified in subsequent arguments. If there are other subtotals within range s (or nested subtotals), these nested subtotals are ignored to avoid double counting.

Possible codes for function_code are as follows:

1 or 101: AVG
2 or 102: COUNT_NUMERIC
3 or 103: COUNT
4 or 104: MAX
5 or 105: MIN
6 or 106: PRODUCT
7 or 107: STDDEV
8 or 108: STDDEV_P
9 or 109: SUM
10 or 110: VARIANCE
11 or 111: VARIANCE_P

Arguments

function_code: NUMBER
range: RANGE

sum([value: number, ...]): number

Returns the sum of the expression in a group.

For example: sum(23,45,32) will return 100.

Arguments

value: NUMBER

sum_distinct([value: number, ...]): number

Returns the sum of distinct numeric values in the expression (ie will ignore all duplicate values).

For example: sum_distinct(23,45,32,45) will return 100. The function will ignore the second 45 because it does not calculate the duplicate numbers.

Arguments

value: NUMBER

sum_product([value: any, ...]): number

Multiplies corresponding components in the given value and returns the sum of these products. Each value must have the same dimensions and all non-numeric arguments are treated as zero.

For example: sum_product(A1:A3, B1:B3) will result in the sum of A1B1 + A2B2 + A3*B3.

Arguments

value: ANY

sumif(criteria_range: range, condition: any, sum_range: range): number

Returns the sum of all values in sum_range, for which criteria_range is equal to condition.

Note: The range sizes for criteria_range and sum_range must match.

For example: Let's say rows A1 contain a list of first names and C1 contain corresponding ages. If you want to sum the ages of every person with the first name 'John' in A1, use sumif(A1:A50, 'John', C1:C50) to get your total sum.

Arguments

criteria_range: RANGE
condition: ANY
sum_range: RANGE

sumifs(sum_range: range, [criteria_range: range, condition: any, ...]): number

Returns the sum of all values in sum_range that match the criteria given by the subsequent arguments.

For example: sumifs(A1:B5, C1:D5, 10, H4:I8, 'John') returns the sum of all cells in A1, for which the corresponding cell in C1 is equal to 10,and the corresponding cell in H4 is equal to 'John'.

Note: The range sizes for criteria_range and sum_range must match.

Arguments

sum_range: RANGE
criteria_range: RANGE
condition: ANY

tan(value: number): number

Computes the tangent of value.

For example: tan(45) will return 1.6197.

Arguments

value: NUMBER

timestamp(year: number, month: number, day: number, hour: number, minute: number, second: number): date_time

Creates a timestamp with a defined year, month, day, hour, minute, second in the format yyyy-MM-dd HH:mm. For timestamps where the year is < 1900 (e.g. '97'), the year will be interpreted as an offset from 1900 (e.g. '1997').

Arguments

year: NUMBER
month: NUMBER
day: NUMBER
hour: NUMBER
minute: NUMBER
second: NUMBER

to_unix_timestamp(date: string, [pattern: string]): number

Converts a timestamp date with an optional pattern (i.e. date format) to its unix epoch which is the number of milliseconds since January 1, 1970, 00:00:00 GMT. Date can be a date, a timestamp, or a string. If providing a string, you can include a pattern for parsing.

For example: Let's say you have the string 2021-02-15. The pattern of the date is yyyy-MM-dd. To convert this timestamp, use to_unix_timestamp('2021-02-15', 'yyyy-MM-dd').

Arguments

date: STRING
pattern: STRING

Renders a cell with a tooltip appearing on hover.

The tooltip_content argument can be formatted as a markdown string. The open_delay argument can be used to change the delay after which the tooltip appears on hover.

Arguments

cell_value: ANY
tooltip_content: STRING
open_delay: NUMBER

trim(value: string): string

Trims the spaces on both ends of value.

For example: Let's say you have the string 'John Smith' in cell A1 and it has 4 spaces on both ends. trim(A1) will create a new string without the spaces.

Arguments

value: STRING

upper(value: string): string

Converts an entire string to upper case.

For example: Let's say you have the name John Smith in cell A1. Upper(A1) will return JOHN SMITH.

Arguments

value: STRING

url(url: string, [label: string]): any

Renders a hyperlink to a url with an optional label.

For example: url('myblog.com', 'My Blog') will create a hyperlink with the label 'My Blog'.

Arguments

url: STRING
label: STRING

url_encode(input: any): string

Encodes input to be safe to use in URL paths and parameters.

For example: =url(concat('http://example.com/test?param=', url_encode(A5)))

Arguments

input: ANY

variance([value: number, ...]): number

Returns the unbiased variance of the values in a group.

For example: variance(23, 45, 32) will return 122.33.

Arguments

value: NUMBER

variance_p([value: any, ...]): number

Returns the biased variance of the values in a group assuming that value items form the entire population.

Note: If they only represent a sample of the population, use VARIANCE instead.

Arguments

value: ANY

vlookup(value: any, range: range, column: number): any

Lookup value in the first column of range and grab column (starting at 1).

For example: Say Column A is a list of fruit names and Column C is a list of prices. vlookup(A3,A1:C6,3) will grab the fruit name in cell A3 and return the corresponding price in Column 3 of the specified range.

Arguments

value: ANY
range: RANGE
column: NUMBER

week_of_year(date: date_time): number

Calculates the week number of a date as an integer.

For example: Week_of_year('2021-06-18') will return 24.

Arguments

date: DATE_TIME

workday(start_date: date_time, value: number, [holidays: any]): date

Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and holidays.

For example: workday('2010-01-01',10) would return 2010-01-15 because the weekends were ignored. Another example: workday('2010-01-01',10, '2010-01-05') returns 2010-01-18 because the weekends plus a holiday were ignored.

Arguments

start_date: DATE_TIME
value: NUMBER
holidays: ANY

year(date: date_time): number

Extracts the year of date as an integer.

For example: Let's say you have the timestamp 2022-02-15 08:30:15 in cell A2. Year(A2) will return 2022.

Arguments

date: DATE_TIME

Action functions

Fusion's default Action library methods.

compute_on_trigger(actionCell: any): any [EXPERIMENTAL]

Lazily compute the Action in the given actionCell reference passed in as argument when triggered.

This will not count as a dependency on that cell, allowing to avoid circular dependencies in some cases.

Arguments

actionCell: ANY

copy_range(source: any, target: range, [copy_result: boolean]): string

Action that, when triggered, copies the contents from one range to another.

Arguments

source: ANY
Cell range to copy from. Can be NULL if you want to empty out the target
target: RANGE
copy_result: BOOLEAN
If true, copies the computed cell value. If false or absent, copies the cell formula.

dropdown(values: array, [selected_value: string], [actionBeforeChange: any], [allow_invalid: boolean], [placeholder_text: string]): any [EXPERIMENTAL]

Renders a dropdown with values that can be selected from the supplied values array. The values must be unique, as each value is converted to a string label in the dropdown.

This function cannot be nested in an action.serial or action.parallel

For example: =action.dropdown(array('red', 'blue', 'green')) will create a dropdown with values 'red', 'blue', 'green'.

When an actionBeforeChange is supplied, it specifies an Action that will be executed before the newly selected value of the dropdown is saved and taken into account by other formulas.

When allow_invalid is set to true, it will override this behavior and still output the previously saved selection. This result can only be a string and this only recommended when trying to preserve the prior selection from a set of string values.

placeholder_text allows to show a text in the dropdown when no value is selected (it can be useful as a prompt or instructions)

Arguments

values: ARRAY
selected_value: STRING
actionBeforeChange: ANY
allow_invalid: BOOLEAN
placeholder_text: STRING

fail(): any

A no-operation failed Action. Can be combined in a serial for short-circuiting.

label(type: string, label: string, [icon: string], [intent: string]): string

Renders a label. Type can be one of: 'button', 'link', or 'tag'.

For example: action.label('button', 'Submit', 'tick', 'success') would render a green button with a tick and the word 'Submit'.

Arguments

type: STRING
label: STRING
icon: STRING
Review a list of icons can in the Blueprint documentation ↗.
intent: STRING
intent defines the color of the label: 'primary' for blue, 'success' for green, 'warning' for orange and 'danger' for red. Review a full list of intents and their output in the Blueprint documentation ↗.

open_markdown_panel(panel_title: string, markdown_content: string): string

Opens a contextual side panel with the provided title and markdown string.

If using CSS styles for the markdown, you can scope it with the .fusion-markdown-panel class.

Arguments

panel_title: STRING
markdown_content: STRING

open_url(url: string, [redirect: boolean]): string

Opens a URL, defaults to opening in a new tab, set the redirect parameter to true if you want to redirect the existing spreadsheet page.

Arguments

url: STRING
redirect: BOOLEAN

parallel([action: any, ...]): string

Given a list of Actions, trigger them all at once.

action: ANY

plugin(action_name: string, [arg: any, ...]): any

Run a custom Action deployed to this server. Contact your Palantir representative to get a list of available Actions.

Arguments

action_name: STRING
arg: ANY

serial(action: any, actionOnSuccess: any, [actionOnFailure: any]): string

Define Actions that should occur only on the success (or failure) of the previous Action.

Arguments

action: ANY
actionOnSuccess: ANY
actionOnFailure: ANY

submit_options([key: string, value: any, ...]): options [EXPERIMENTAL]

Configurable key-value options for action.submit_to_region_with_options(...).

Available options:

submitEmptyCells: empty or null cells will overwrite data present for this column in the destination table
succeedWhenNoRows: changes the behavior of the Action to succeed when the shouldSubmit is false or false for all rows, instead of failing and showing a toast

Arguments

key: STRING
value: ANY

submit_to_region(document_identifier: string, region_name: string, should_submit: any, timestamp_column: string, key_column: string, key_value: any, [column: string, value: any, ...]): string

Creates a button that submits data into a region, with a specified key.

For example: =action.submit_to_region('ri.fusion.main.document...', 'submit_table', TRUE, 'time', 'first_column', A1:A10, 'second_column', B1:B10) will submit A1 and B1:10 to columns 'first_column' and 'second_column' respectively of the table 'submit_table', within the specified sheet. The current timestamp will be submitted to the 'time' column.

Arguments

document_identifier: STRING
Insert the RID of the dataset that your submissions will be written to. The RID can be identified in the document's URL and looks like ri.fusion.main.... document_metadata('document_identifier') can be used to reference the RID of the current sheet.
region_name: STRING
Specifies the name of the table region that you are writing into. If you have not already specified this table region, go to the receiving spreadsheet, use the 'create table region' widget, and give your table an intuitive name.
should_submit: ANY
Corresponds to a particular column in your submission sheet that contains a Boolean (ie true/false). If should_submit is true, then the data will be submitted.
timestamp_column: STRING
Specifies the column header that you would like to submit the current timestamp to. Use null if no timestamp is needed.
key_column: STRING Specifies the column header that you would like to submit values into.
key_value: ANY
Signifies a specific cell or range of cells to be submitted into the specified key_column.
column: STRING
value: ANY

submit_to_region_with_options(document_identifier: string, region_name: string, submit_options: options, should_submit: any, timestamp_column: string, key_column: string, key_value: any, [column: string, value: any, ...]): string [EXPERIMENTAL]

Creates a button that submits data into a region, with a specified key.

For example: =action.submit_to_region_with_options('ri.fusion.main.document...', 'submit_table', TRUE, 'time', 'first_column', A1:A10, 'second_column', B1:B10) will submit A1 and B1:10 to columns 'first_column' and 'second_column' respectively of the table 'submit_table', within the specified sheet. The current timestamp will be submitted to the 'time' column.

Arguments

document_identifier: STRING
Insert the RID of the dataset that your submissions will be written to. The RID can be identified in the document's URL and looks like ri.fusion.main.... document_metadata('document_identifier') can be used to reference the RID of the current sheet.
region_name: STRING
Specifies the name of the table region that you are writing into. If you have not already specified this table region, go to the receiving spreadsheet, use the 'create table region' widget, and give your table an intuitive name.
submit_options: OPTIONS
Specifies configurable options to use for the submit Action with action.submit_options(...), possible values in the submit_options function documentation
should_submit: ANY
Corresponds to a particular column in your submission sheet that contains a Boolean (i.e. true/false). If should_submit is true, then the data will be submitted.
timestamp_column: STRING
Specifies the column header that you would like to submit the current timestamp to. Use null if no timestamp is needed.
key_column: STRING
Specifies the column header that you would like to submit values into.
key_value: ANY
Signifies a specific cell or range of cells to be submitted into the specified key_column.
column: STRING
value: ANY

success(): any

A no-operation successful Action. Can be combined in a serial for short-circuiting.

toast(message: string, [intent: string], [dismissButton: string]): string

Triggers a toast. ding!.

If dismissButton is defined, the toast will linger till the user clicks on the dismiss button. If the toast is wrapped with an action_serial function, the subsequent Action will then be triggered.

Arguments

message: STRING
intent: STRING
dismissButton: STRING

trigger(label: any, action: any): string

Given a label and an Action, triggers Action when label is clicked.

Configure the label with an action_label function.

Arguments

label: ANY
action: ANY

validate_table(table_range: range, [condition: any, ...]): string

Given a range and a list of conditions, validates that all conditions are met.

Arguments

table_range: RANGE
condition: ANY

Validation functions

Fusion's default validation library methods.

column_enum(column_name: string, allowed_values: array): string

Given a column name and a list of allowed values, validates that all non-null values in that column are in the allowed list.

Arguments

column_name: STRING
allowed_values: ARRAY

column_not_null([column_name: string, ...]): string

Given a list of column names, validates that they are not empty for non-empty rows. ie. These columns can only be empty if the entire row is empty.

Arguments

column_name: STRING

column_numeric([column_name: string, ...]): string

Given a list of column names, validates that each column only contains numeric values.

Arguments

column_name: STRING

column_regex(column_name: string, regex: string): string

Given a column name and a regex string, validates that all values in that column matches the given regex.

Arguments

column_name: STRING
regex: STRING

table_headers([column_name: string, ...]): string

Given a list of column names, validates that they exist in the table defined.

Arguments

column_name: STRING

table_key([column_name: string, ...]): string

Given a list of column names, validates that the combination of them is unique within the table. E.g. Given a range where the first row contains these two column names: Name and Age: If you have two records: [Bob, 20] and [Bobby, 20] then table_key('Name', 'Age') should succeed. However if the two records are: [Bob, 20] and [Bob, 20] then table_key('Name', 'Age') should fail.

Arguments

column_name: STRING

Chart functions

Methods for plotting data.

bar(x_values: any, y_values: any, [options: any, ...]): barplot

Plot a series of xy values on a bar chart

Available options:

drawLabels: boolean
orientation: "horizontal" | "vertical"

Arguments

x_values: ANY
y_values: ANY
options: ANY

chart([Plots-or-options: any, ...]): chart

Plot multiple series on a chart with configurable options

Available options:

showAxes: boolean
showLegend: boolean
showResetZoomButton: boolean
showToolbar: boolean
tooltip: false | "closest" | "aggregate"
yAxisInset: boolean
rangeSelection: "select" | "visual" | false
height: number (px)
width: number (px)
Plots-or-options: ANY

line(x_values: any, y_values: any, [options: any, ...]): lineplot

Plot a series of xy values on a line chart Available options:

dataMarkers: boolean
color: string

Arguments

x_values: ANY
y_values: ANY
options: ANY

options([key: string, value: any, ...]): options

Configurable key-value options charts.

Arguments

key: STRING
value: ANY

Time series functions

Methods for interacting with time series.

count(timeSeries: any): number

Returns the number of points in the series.

Arguments

timeSeries: ANY

derivative(timeSeries: any): any

Take the derivative of a time series (with respect to seconds).

Arguments

timeSeries: ANY

difference(timeSeries: any): number

Returns the difference between the first value in the series and the last value in the series.

Arguments

timeSeries: ANY

first_timestamp(timeSeries: any): time

Returns the timestamp of the first point in the time series.

Arguments

timeSeries: ANY

first_value(timeSeries: any): any

Returns the value of the first point in the time series.

Arguments

timeSeries: ANY

integral(timeSeries: any, method: string): any

For each point in the child series, output the total area under the series up until that point.

Three different methods of integration are supported: linear, which uses the trapezoidal rule for integral approximation, and lhs/lhr which use the left and right Riemann sums respectively.

Arguments

timeSeries: ANY
method: STRING

last(timeSeries: any, timeAmount: number, timeUnit: string): any

Filters the time series to leave the last part of the specified duration.

Supported units:

hours/h
minutes/m
seconds/s
microseconds/us
nanoseconds/ns

Arguments

timeSeries: ANY
timeAmount: NUMBER
timeUnit: STRING

last_timestamp(timeSeries: any): time

Returns the timestamp of the last point in the time series.

Arguments

timeSeries: ANY

last_value(timeSeries: any): any

Returns the value of the last point in the time series.

Arguments

timeSeries: ANY

max(timeSeries: any): number

Returns the maximum value over the entire time series.

Arguments

timeSeries: ANY

mean(timeSeries: any): number

Returns the mean value over the entire time series.

Arguments

timeSeries: ANY

min(timeSeries: any): number

Returns the minimum value over the entire time series.

Arguments

timeSeries: ANY

scale(timeSeries: any, scale: number): any

Take each tick and multiply the value by the specified factor.

That is, for a source time series containing ticks (t, v) upon scaling by x, the resulting scaled time series will have ticks (t, v * x).

Arguments

timeSeries: ANY
scale: NUMBER

shift(timeSeries: any, shift: number): any

Take each point of the time series and shift the value by the specified amount.

That is, for a source time series containing ticks (t, v) upon shifting by x, the resulting value-shifted time series will have ticks (t, v + x).

Arguments

timeSeries: ANY
shift: NUMBER

stddev(timeSeries: any): number

Returns the standard deviation of the entire time series.

Arguments

timeSeries: ANY

time_range(timeSeries: any, startTime: any, endTime: any): any

Selects a particular time range of the time series.

Arguments

timeSeries: ANY
startTime: ANY
endTime: ANY

timeseries(seriesId: string): any

Returns the time series of the given ID.

Arguments

seriesId: STRING