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:ss (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:C6 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.

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.

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:A$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:A10 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:A10 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.

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.
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:A10 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.

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

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_lazy('Submit!', 'ri.fusion.main.document...', 'submit_table', TRUE, 'time', 'key_column', A2:A10, 'value_column', B2:B10) will submit A2:A10 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

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:A50 contain a list of first names and C1:C50 contain corresponding ages. If you want to sum the ages of every person with the first name 'John' in A1:A50, 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:B5, for which the corresponding cell in C1:D5 is equal to 10,and the corresponding cell in H4:I8 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:ss. 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 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 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:A10 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:A10 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

中文翻译¶

```markdown

函数库¶

核心函数¶

这些是 Fusion 的默认方法。

abs(value: number): number¶

计算一个数字的绝对值（即不带符号的数字）。

例如：abs(-2) 将返回值 2。

参数

value: NUMBER

acos(value: number): number¶

返回一个数字的反余弦值。反余弦是余弦值为该数字的角度。返回的角度以弧度为单位，范围在 0 到 Π 之间。

例如：acos(1) 将返回值 0。

参数

value: NUMBER

all_token_match(value: any): same_as_first_argument¶

返回一个带有修饰符的 value，允许以分词（tokenized）方式进行搜索和查找，所有词项都必须出现在结果中的某处。

参数

value: ANY

any_token_match(value: any): same_as_first_argument¶

返回一个带有修饰符的 value，允许以分词（tokenized）方式进行搜索和查找，至少有一个词项出现在结果中的某处。

参数

value: ANY

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

创建一个包含所有输入属性的数组。生成的数组将被 [方括号] 包围。所有属性将列在单个单元格中。

例如：array('John', 'Mary', 'Richard') 结果为数组 [ John, Mary, Richard ]

注意：如果属性是字符串，它们需要用'单引号'括起来。

参数

arg: ANY

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

将所有输入的数组连接成一个单一的数组。

例如：假设单元格 A1 中有数组 [1,2,3]，单元格 A2 中有数组 [4,5,6]。Array_concat(A1,A2) 将返回新数组 [1,2,3,4,5,6]。

参数

array: ARRAY

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

检查 array 是否包含 value。如果数组包含该值，函数返回 true。

例如：如果数组 [ John, Mary, Richard ] 在单元格 E7 中，执行 array_contains(E7, 'Richard') 将检查数组中是否存在值 'Richard'。在这种情况下，函数将返回 True。如果我们输入 array_contains(E7, 'Louise')，函数将返回 False。

参数

array: ARRAY
value: ANY

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

返回第一个数组中存在，但不在任何其他数组中的所有唯一元素，不保证顺序。

例如：给定单元格 E7 中的数组 [ John, Mary, Richard, Richard ]，以及单元格 E8 中的数组 [ John, Mary, Bob]，array_difference(E7, E8) 将返回 [ Richard ]。

参数

array: ARRAY
differenceArrays: ARRAY

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

返回一个仅包含输入参数中不重复值的数组。

例如：array_distinct(array(3, 2, 1), 4, array(1, 2)) 将返回 array(3, 1, 4, 2)。

注意：值的顺序不会被保留。此外，附加在值上的任何标签（例如模糊或精确）将被丢弃。

参数

value: ANY

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

创建一个包含所有输入属性的数组。生成的数组将被 [方括号] 包围。所有属性将列在单个单元格中。此函数类似于 array，但会折叠单元格范围和数组，从左到右、逐行取值。范围中的空值和错误将被忽略。

例如：array_flatten('John', A1:B2)，其中 A1 = array('Zoe', 'Charles')，A2 = 'Mary'，B2 = 'Richard'，结果为数组 [ John, Zoe, Charles, Mary, Richard ]。注意，单元格 A1 中的数组被展平到输出中，并且空单元格 B1 被跳过。

注意：如果属性是字符串，它们需要用'单引号'括起来。

参数

arg: ANY

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

从指定的 array 中返回位置 index（从 1 开始）处的元素。

例如：如果数组 [ John, Mary, Richard ] 在单元格 E7 中，我们想验证位置 3 是哪个属性，可以使用 array_get_at_index(E7, 3)。这将返回 Richard，即数组中的第三个属性。

参数

array: ARRAY
index: NUMBER

array_get_first(array: array): any¶

获取 array 的第一个元素。

参数

array: ARRAY

array_get_last(array: array): any¶

获取 array 的最后一个元素。

参数

array: ARRAY

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

返回所有给定数组中都存在的所有唯一元素，不保证顺序。

例如：给定单元格 E7 中的数组 [ John, Mary, Mary, Richard ]，以及单元格 E8 中的数组 [ John, Mary, John, Bob]，array_intersection(E7, E8) 将返回 [ John, Mary ]。

参数

array: ARRAY
intersectionArrays: ARRAY

array_length(array: array): number¶

返回给定数组的长度。

例如：如果数组 [ John, Mary, Richard ] 在单元格 E7 中，我们想确定数组的长度，可以写：array_length(E7)。函数将返回值 3，因为数组中有三个属性。

参数

array: ARRAY

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

从 start_index（包含）开始，到 end_index（包含）结束，对 array 进行切片，并返回一个数组。

例如：如果数组 [ John, Mary, Richard ] 在单元格 E7 中，array_slice(E7, 1, 2) 将返回 [ John, Mary ]。如果 start_index 为零或大于数组长度，则返回空数组。例如 array_slice(E7, 5, 2) → []。如果 start_index 为负数，则将其用作从数组末尾开始的偏移量。例如 array_slice(E7, -2, 2) → [ Mary ]。如果 end_index 为零或大于数组长度，则提取直到数组末尾的子数组。例如 array_slice(E7, 2, 5) → [ Mary, Richard ]。如果 end_index 为负数，则将其用作从数组末尾开始的偏移量。例如 array_slice(E7, 1, -2) → [ John, Mary ]。

参数

array: ARRAY
start_index: NUMBER
end_index: NUMBER

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

返回按升序排序的给定数组。您可以指定 FALSE 或 'DESC' 作为第二个参数以降序排序。

例如：如果数组 [ 3, 4, 1 ] 在单元格 E7 中，我们想对数组进行排序，可以写：array_sort(E7, 'DESC')。函数将返回数组 [ 4, 3, 1 ]。

参数

array: ARRAY
sort_direction: ANY

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

创建一个由分组元素组成的数组，第一个元素包含给定数组的第一个元素，第二个元素包含给定数组的第二个元素，依此类推。结果数组的长度将等于最短输入数组的长度。

例如：如果单元格 A1 中有数组 ['a', 'b', 'c']，单元格 A2 中有数组 [1, 2, 3]。array_zip(A1, A2) 将返回新数组 [ [ 'a', '1' ], [ 'b', '2' ], [ 'c', '3' ] ]。

参数

array: ARRAY

asin(value: number): number¶

返回一个数字的反正弦值。反正弦是正弦值为该数字的角度。返回的角度以弧度为单位，范围在 -Π/2 到 Π/2 之间。

例如：asin(-1) 将返回值 -Π/2。

参数

value: NUMBER

atan(value: number): number¶

返回一个数字的反正切值。反正切是正切值为该数字的角度。返回的角度以弧度为单位，范围在 -Π/2 到 Π/2 之间。

例如：atan(0) 将返回值 0。

参数

value: NUMBER

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

返回指定 x 和 y 坐标的反正切值。反正切是从 x 轴到一条包含原点 (0, 0) 和坐标为 (x_num, y_num) 的点的直线的角度。角度以弧度为单位，范围在 -Π 到 Π 之间，不包括 -Π。

例如：atan2(1,1) 将返回值 0.785398163。

参数

x_num: NUMBER
y_num: NUMBER

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

计算指定 range 的数值平均值。此范围可以输入为一组值或一个值范围。

例如：avg(5, 7, 11) 将返回 7.66。

参数

range: RANGE

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

从数字创建二进制对象。每个数字将被视为一个无符号字节（0-255）。高阶位将被忽略。

例如：binary(0, 0, 127)。

参数

value: ANY

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

返回带有 branch_name 标签的 dataset_path。应用于查找（lookup）中的第一个参数，以指定搜索中使用的分支。

注意：如果您找不到新索引的数据集，请刷新页面或导航至 查找并使用数据，选择 已索引的数据集，然后选择刷新按钮。

参数

dataset_path: STRING
branch_name: STRING

case_toggle(value: string): string¶

将大写字母转换为小写字母，反之亦然。数字保持不变。

参数

value: STRING

cbrt(value: number): number¶

计算给定 value 的立方根。

例如：cbrt(8) 将返回 2。

参数

value: NUMBER

ceil(value: number): number¶

通过向上舍入到最接近的整数来计算给定 value 的向上取整值。

例如：Ceil(5.2) 将返回 6。

参数

value: NUMBER

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

渲染一个复选框，选中时返回 true，否则返回 false，并带有可选标签。如果未提供 checked 参数，则默认为 false 且未选中。

参数

checked: BOOLEAN
label: STRING

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

返回第一个非空的属性。或者，如果所有属性都为空，则返回 null。

例如：假设我们有一个包含名称和空值混合的列 A。如果我们使用 coalesce(columnA)，函数将返回第一个可用的名称。

参数

arg: ANY

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

使用指定的文本和背景颜色渲染一个单元格。

参数

cell_value: ANY
text_color: STRING
background_color: STRING

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

将多个输入字符串属性连接成一个单一的字符串属性。

例如：假设名字 John 在单元格 A2 中，姓氏 Smith 在单元格 B2 中。使用 concat 函数，我们可以在 C2 中输入 concat(A2, ' ', B2) 来得到字符串 'John Smith'。

所有集合类型的参数都会被递归展平。例如，concat(array(1, 2), array(array(3, 4, 5)), 6) 将返回字符串 '123456'。

arg: STRING

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

将多个输入字符串属性连接成一个单一的字符串属性，并在所有参数之间插入 separator。所有集合类型的参数将被递归展平。

例如：假设名字 John 在单元格 A2 中，姓氏 Smith 在单元格 B2 中。使用 concat_ws 函数，我们可以在 C2 中输入 concat_ws('_',A2, B2) 并得到结果 'John_Smith'。

所有集合类型的参数都会像 concat 函数中那样被递归展平。

参数

separator: STRING
arg: STRING

cos(value: number): number¶

计算给定 value 的余弦值。

例如：cos(190) 将返回 0.066。

参数

value: NUMBER

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

返回一个组中的项目数。

例如：假设我们想知道特定列中对象的数量。我们可以选择 count 函数，高亮该列，然后获取一个值。

参数

range: RANGE

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

返回一个组中不同项目的数量。

例如：count_distinct(columnA) 将返回此列中不同对象的数量。此函数也可以应用于数组，并将返回该数组中不同对象的数量。

参数

range: RANGE

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

计算一个组中数值项的数量。

例如：假设您有 100 行包含各种名称和数字。要仅确定此列中数值项的数量，请使用 count_numeric(A1:A100) 来获取该值。

参数

value: ANY

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

返回范围 range 中等于指定 criteria 的项目数。

例如：假设 A 列包含一个动物列表，您想知道前 100 行中 'Dog' 出现了多少次。使用 countif(A1:A100, 'Dog') 来获取唯一计数。

参数

range: RANGE
criteria: ANY

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

返回范围 range 中对于所有 range、criteria 对都等于 criteria 的项目数。

例如：countifs(A1:A100, 'Red', B1:B100, 2) 将返回 A 列中包含值 Red 且 B 列中包含数字 2 的所有值的计数。

参数

range: RANGE
criteria: ANY

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

使用定义的 year、month、day 创建一个日期，格式为 yyyy-MM-dd。

对于年份 < 1900 的日期（例如 '97'），该年份将被解释为从 1900 开始的偏移量（例如 '1997'）。

参数

year: NUMBER
month: NUMBER
day: NUMBER

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

返回 dateOrDaysLeft 加上 dateOfDaysRight 的结果日期或时间戳。每个参数可以是天数或天的小数部分，也可以是一个日期。日期必须采用 yyyy-MM-dd 格式。

例如：假设我们想知道 2021-05-06 之后 40 天的日期。此日期在单元格 D2 中。使用 date_add(D2, 40) 得到 2021-06-15。例如：如果我想要日期 2021-05-06 加上半天的日期时间戳。此日期在单元格 D2 中。使用 date_add(D2, 0.5) 得到 2021-05-06 12:00。

参数

dateOrDaysLeft: ANY
dateOrDaysRight: ANY

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

返回从 start 到 end 的天数。日期必须采用 yyyy-MM-dd 格式。

例如：假设我们想知道 2021-01-15 和 2021-06-15 之间的天数。这些单元格分别位于 B2 和 B5。使用 date_diff (B2, B5) 得到 -151 天的差值。

参数

start: DATE_TIME
end: DATE_TIME

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

将日期/时间戳 date 转换为由 format 中的字符串指定的格式的字符串。格式可以是字符串 yyyy-MM-dd 的变体。

例如：使用 date_format('2021-05-06', 'MM-dd-yy') 将日期 2021-05-06 重新格式化为 05-06-21。

参数

date: DATE_TIME
format: STRING

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

返回 dateOrDaysLeft 减去 dateOfDaysRight 的结果日期或时间戳。每个参数可以是天数或天的小数部分，也可以是一个日期。日期必须采用 yyyy-MM-dd 格式。

例如：假设我们想知道 2021-05-06 之前 40 天的日期。此日期在单元格 D2 中。使用 date_sub(D2, 40) 得到 2021-03-27。例如：如果我想要日期 2021-05-06 减去半天的日期时间戳。此日期在单元格 D2 中。使用 date_sub(D2, 0.5) 得到 2021-05-05 12:00。例如：如果我想知道两个日期之间的天数差，其中一个日期在单元格 D1 中，另一个在单元格 D2 中。使用 date_sub(D1, D2)。

参数

dateOrDaysLeft: ANY
dateOfDaysRight: ANY

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

返回一个带有选定日期的日期选择器。日期必须采用 yyyy-MM-dd 格式，并可选择包含 HH\:mm:ss（时间信息）。时间精度必须是 'NONE'、'MINUTE' 或 'SECOND' 之一。

参数

selectedDateTime: DATE_TIME
timePrecision: STRING

day_of_month(date: date_time): number¶

从给定的日期/时间戳/字符串中提取月份中的第几天，以整数形式返回。

例如：假设日期 2021-06-18 在单元格 B2 中。day_of_month(B2) 将返回 18。

参数

date: DATE_TIME

day_of_year(date: date_time): number¶

从给定的日期/时间戳/字符串中计算年份中的第几天，以整数形式返回。

例如：假设日期 2021-06-18 在单元格 B2 中。day_of_year(B2) 将返回 169。

参数

date: DATE_TIME

document_metadata(key: string): string¶

访问文档元数据。要执行此操作，传入您需要的元数据字段的 key。

支持的键：

creator 返回文档的创建者（如果已知）。
document_identifier 返回文档的内部标识符（例如，在 submit_to_region_with_key 函数中使用）。

例如：document_metadata('creator') 将返回文档创建者的用户名。

参数

key: STRING

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

渲染一个下拉菜单，可以从提供的 values 数组中选择值。values 必须是唯一的，因为每个值都会转换为下拉菜单中的字符串标签。

例如，=dropdown(array('red', 'blue', 'green')) 将创建一个包含值 'red'、'blue'、'green' 的下拉菜单。

当提供了 selected_value 时，它指定了下拉菜单的默认字符串标签。selected_value 是可选的，可以设置为 null，这将使下拉菜单的默认选择处于未选中状态。如果设置了 selected_value，但选定的标签在 values 数组中不再存在（例如，如果底层数据发生变化），结果将是一个空单元格。

当 allow_invalid 设置为 true 时，它将覆盖此行为，并仍然输出先前保存的选择。此结果只能是字符串，仅建议在尝试保留来自一组字符串值的先前选择时使用。

placeholder_text 允许您在下拉菜单未选择任何值时显示文本（作为提示或说明很有用）。

参数

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

empty_cell(): any¶

返回一个空结果。与 if 或 iferror 函数结合使用时很有用（例如 if(not isnull(A1), A1, empty_cell())）。

eq(value: any): same_as_first_argument¶

将当前 value 标记为相等（==）比较。

应用于查找函数中的参数以更改搜索行为。

参数

value: ANY

exact(value: any): same_as_first_argument¶

返回一个带有修饰符的 value，使搜索和查找寻求与 value 的精确匹配。

参数

value: ANY

exp(value: number): number¶

返回 e 的 value 次幂。常数 e 等于 2.718，即自然对数的底数。

例如：exp(2) 返回 7.389。这是自然对数 e 的 2 次幂的结果。

参数

value: NUMBER

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

将 tags 添加到 value 并返回新的带标签的值。

参数

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]¶

渲染一个带有标签 label 的按钮，点击后，会将电子表格复制到 folder，名称为 name。

导出将使用以下模式复制：'$name - Export $sheetName'。可以通过在 exports 中传入所需导出的工作表名称来限制导出。如果缺少任何导出，复制将失败。完成和失败消息可以分别通过 onCompleteMessage 和 onFailureMessage 传入。如果 redirectToHomeAfterCompletion 设置为 true，用户将在完成后被重定向到其主文件夹。

参数

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

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

输出一个错误。

参数

value: STRING

experimental_get_tags(value: any): array [EXPERIMENTAL]¶

返回与 value 关联的标签数组。

参数

value: ANY

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

测试方法，阻塞 I/O 直到超时。可以选择提供一个额外的延迟，在超时后等待，以模拟行为不良的 I/O 函数。

参数

extra_delay: NUMBER

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

输出一个范围。

参数

width: NUMBER
value: ANY

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

从 value 中移除数组 tags 中的标签，或者如果缺少 tags 参数，则移除 value 中的所有标签。

参数

value: ANY
tags: ARRAY

factorial(value: number): number¶

计算给定 value 的阶乘。

例如：factorial(3) 将返回 6。

参数

value: NUMBER

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

返回字符串 text_to_search 中第一次出现 search_for 的索引。您可以选择为搜索提供 starting_index。

例如：假设您在单元格 A1 中有短语 'The grey cat chased the grey mouse'。要确定第一次出现 'grey' 的索引，执行 find('grey', A1) 得到索引 5。

参数

search_for: STRING
text_to_search: STRING
starting_index: NUMBER

floor(value: number): number¶

通过向下舍入到最接近的整数来计算给定 value 的向下取整值。

例如：floor(3.2) 将舍入为 3。并且，floor (-4.5) 将舍入为 -5。

参数

value: NUMBER

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

将数值单元格 value 格式化为类似 #,###.## 的格式，四舍五入到 decimalPlaces 指定的小数位数。该函数将结果作为字符串返回。

例如：假设您在 A 列中有一列 4 位数字，并且您希望包含千位分隔符以及 2 位小数。使用 format_number(A1, 2) 将第一个数字重新格式化为 #,###.00。向下拖动框以应用于 A 列的其余部分。

参数

value: NUMBER
decimalPlaces: NUMBER

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

以 printf 样式格式化 arg，并使用 format 将结果作为字符串属性返回。

参数

format: STRING

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

返回一个带有修饰符的 value，允许以模糊（fuzzy）方式进行搜索和查找，其中搜索的值与真实值的编辑距离在 distance 之内。仅支持编辑距离 0、1 和 2，默认为 2。

距离对应于 Levenshtein 距离，这是一种衡量从一个字符串到另一个字符串所需的单字符编辑次数的方法。

参数

value: ANY
distance: NUMBER

get_object_rid(object_or_property: any): string¶

返回给定对象或对象属性的对象 RID。

参数

object_or_property: ANY

get_object_type_id(object_or_property: any): string¶

返回给定对象或对象属性的对象类型 ID。

参数

object_or_property: ANY

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

返回对象的给定属性。

参数

object: OBJECT
key: STRING

gt(value: any): same_as_first_argument¶

将当前 value 标记为大于（>）比较。

应用于查找函数中的参数以更改搜索行为。

参数

value: ANY

gte(value: any): same_as_first_argument¶

将当前 value 标记为大于或等于（>=）比较。

应用于查找函数中的参数以更改搜索行为。

参数

value: ANY

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

在 range 的第一行中查找 value，并获取 row（从 1 开始）。

例如：假设 A 列是一个颜色列表。hlookup(A1,A1:A100,5) 将返回该列第 5 行中的颜色。

参数

value: ANY
range: RANGE
row: NUMBER

hour(date: date_time): number¶

从给定的日期/时间戳/字符串中提取小时，以整数形式返回。此函数将忽略字符串中的日期和分钟。

例如：假设您在单元格 A2 中有时间戳 2021-02-15 08:30:15。hour(A2) 将返回 8。

参数

date: DATE_TIME

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

如果 condition 评估为 true，则返回 value_if_true 中指定的值。如果 condition 评估为 false，则返回 value_if_false 中指定的值。

例如：假设我们执行函数 if(A1 >=5, 'True', 'False')。如果 A1 等于 6，函数将返回 True。如果 A1 等于 4，函数将返回 False。

参数

condition: BOOLEAN
value_if_true: ANY
value_if_false: ANY

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

如果未检测到错误，则返回 value。否则，抛出由 value_if_error 指定的错误。此函数为管理错误消息提供了更优雅的解决方案。

例如：假设您在单元格 C3 中有简单的公式 A1/B2。如果 B2 为空，您希望该公式抛出错误 'Enter a value in B2.'。执行 iferror(A1/B2, 'Enter a value in B2.')。

参数

value: ANY
value_if_error: ANY

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

返回 range 中由 row_offset 和 column_offset 指定的单元格的内容。row_offset 指定您要从中提取数据的行，而 column_offset 指定您要从中提取数据的列。

例如：index(A1:C6, 2, 3) 表示您要从范围 A1:C6 中提取第 2 行、第 3 列的数据。

注意：如果行偏移设置为 0，则将选择由 column_offset 指定的整列。如果列偏移为 0，则将选择由 row_offset 指定的整行。如果两者都为零，则将选择整个单元格范围。

参数

range: RANGE
row_offset: NUMBER
column_offset: NUMBER

internal_region_result(region_id: string): any [EXPERIMENTAL]¶

内部函数。

参数

region_id: STRING

isNull(value: any): boolean¶

如果属性为 null 或空单元格，则返回 true。否则，函数返回 false。

例如：假设 A 列包含整数和空值的混合。使用 isnull(A1) 确定列中的第一个值是 true（即空值）还是整数。向下拖动此公式以应用于列的其余部分。

参数

value: ANY

last_day(date: date_time): number¶

给定一个 date 属性，该函数以 yyyy-MM-dd 格式返回指定月份的最后一天。注意：日期必须作为字符串输入。

例如：假设当前日期是 2021-02-01，您想计算该月的最后一天。使用 last_day('2021-02-01') 得到 2021-02-28。注意：一月的日期总是返回 31，而二月的日期根据年份返回 28 或 29。

参数

date: DATE_TIME

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

从字符串 text 的开头返回 num_chars 中指定的字符数。

例如：如果字符串 'John Smith' 在单元格 A2 中，right(A2, 3) 将返回 ith。

参数

text: STRING
num_chars: NUMBER

length(value: any): number¶

根据字符数计算给定字符串或二进制属性的长度。

例如：length('John Smith') 将返回 10。

参数

value: ANY

ln(value: number): number¶

计算给定值的自然对数。如果值为 0 或以下，函数将返回错误。

例如：ln(7) 将返回 1.9459。

参数

value: NUMBER

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

计算以 base 为底的 value 的对数。

例如：log(8,2) 将返回 3。

参数

value: NUMBER
base: NUMBER

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

从 dataset_path 中的 result_column 返回与 column、value 对中定义的过滤器匹配的值。

例如：lookup('/Users/me/myData', 'my_column', 'first_name', 'John', 'last_name', 'Doe') 将在数据集 '/Users/me/myData' 中搜索列 'my_column' 中 first_name = 'John' 且 last_name = 'Doe' 的行。该函数将获取与过滤器匹配的 my_column 的值。

注意：

Value 可以使用 exact 或 fuzzy 函数包装，以指定匹配应该是精确的还是模糊的。Dataset_path 可以使用 branch 函数包装，以指定要查找的数据集的分支。
如果您找不到新索引的数据集，请刷新页面或导航至 查找并使用数据，选择 已索引的数据集，然后选择刷新按钮。

参数

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

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

从 dataset_path 中的 result_column 返回匹配的值，作为基于行的全局顺序排序的数组。使用 column、value