Table summary
Operators
| Name | Syntax | Example | Description |
== |
5 == 5 ➡️ true |
Checks if two values are equal. | |
!= |
5 != 3 ➡️ true |
Checks if two values are not equal. | |
< |
3 < 5 ➡️ true |
Checks if one value is less than another. | |
> |
5 > 3 ➡️ true |
Checks if one value is greater than another. | |
<= |
3 <= 5 ➡️ true |
Checks if one value is less than or equal to another. | |
>= |
5 >= 5 ➡️ true |
Checks if one value is greater than or equal to another. | |
and |
age > 18 AND city == "Paris" ➡️ the condition will be true only if the person is older than 18 and lives in Paris. |
Logical operator "AND", returns true if all conditions are true. | |
+ |
5 + 3 ➡️ 8 |
Adds two values. | |
- |
5 - 3 ➡️ 2 |
Subtracts one value from another. | |
* |
5 * 3 ➡️ 15 |
Multiplies two values. | |
/ |
15 / 3 ➡️ 5 |
Divides one value by another. | |
% |
10 % 3 ➡️ 1 · Divides 10 by 3 and returns the remainder of the division, so the remainder is 1... Useful to check for even numbers or cycles. |
Returns the remainder of a division. | |
** |
2 ** 3 ➡️ 8 · means 2 raised to the power of 3 · so 2 ** 3 = 2 × 2 × 2 = 8 |
Raises one value to the power of another. | |
or |
age > 18 OR city == "Paris" ➡️ true The condition will be true if either of the two conditions is true. |
Logical operator "OR", returns true if at least one condition is true. | |
condition ? success : failure |
X > 5 ? 'Yes' : 'No' ➡️ If the value of X is greater than 5, the condition returns "Yes", otherwise it returns "No" |
Returns one of the two values depending on the specified condition. |
Date Functions
| Name | Syntax | Example | Description |
MIN_DATE_ELEMENTS (date1, date2, ...) |
MIN_DATE_ELEMENTS('2023-01-01', '2023-06-01') ➡️ Result: '2023-01-01'. |
Displays the earliest date among the selected columns. | |
MAX_DATE_ELEMENTS (date1, date2, ...) |
MAX_DATE_ELEMENTS('2023-01-01', '2023-06-01') ➡️ Result: '2023-06-01'. |
Displays the latest date among the selected columns. | |
DATE_ADD(date, number) |
DATE_ADD(TODAY(), 7) ➡️ Adds 7 days to the current date. |
Displays a date that is the sum of an existing date and a number of added days. | |
DATE_ADDNB(date, number) |
DATE_ADDNB(TODAY(), 10) ➡️ Adds 10 working days to the current date. |
Displays a date that is the sum of a date from a date column and an integer number of working days. | |
DATEHOUR_ADD(date, number) |
DATEHOUR_ADD('2024-09-17 12:00:00', 2) |
Allows adding a number representing the number of hours to a date from a 'date and time' column. |
Text Functions
| Name | Syntax | Example | Description |
UPPER(text) |
UPPER("lorem ipsum") ➡️ Result: "LOREM IPSUM" |
Converts all letters in a text string to uppercase. | |
LOWER(text) |
LOWER("HELLO WORLD") ➡️ Result: "hello world" |
Converts all letters in a text string to lowercase. | |
REGEXEXTRACT(text, regular_expression) |
REGEXEXTRACT("Hello, my order n°24158 has still not been delivered.", "[0-9]+") ➡️ Result: "24158" |
Extracts the first value matching a regular expression from free text. | |
REGEXREPLACE(text, regular_expression, replacement?) |
REGEXREPLACE("Supermarket", "Super", "Hyper") ➡️ Result: "Hypermarket" REGEXREPLACE("Phone: 06 12 34 56 78", "[^0-9]") ➡️ Result: "0612345678" |
Replaces all matches of a regular expression with new text. | |
REGEXMATCH(text, regular_expression) |
REGEXMATCH("reference code T51234", "([A-Z].*([0-9]){5}") ➡️ Result: true REGEXMATCH("reference code T51", "([A-Z].*([0-9]){5}") ➡️ Result: false |
Checks whether a text matches a regular expression. Returns a boolean ( true or false). |
|
TRIM(text) |
TRIM(" Hello world ") ➡️ Result: "Hello world" |
Removes extra spaces while keeping a single space between words. | |
LENGTH(text) |
LENGTH("Bonjour") ➡️ Result: 7 |
Returns the length of a text string. Counts all characters, including spaces. | |
SEARCH(find_text, within_text, start_position) |
SEARCH("ban", "Banana", 1) ➡️ Result: 1 (first "ban") |
Returns the position of a text in a string, ignoring case. | |
FIND(find_text, within_text, start_position) |
FIND("A", "BANANA", 1) ➡️ Result: 2 (first A) |
Returns the exact position of a text in a string, respecting case. | |
LEFT(text, number_of_characters) |
LEFT("ABC1234", 3) ➡️ Result: ABC |
Returns the first n characters of a string. Useful to extract a prefix or a code. | |
RIGHT(text, number_of_characters) |
RIGHT("ABC1234", 4) ➡️ Result: 1234 |
Returns the last n characters of a string. Useful to extract the end of a code or an identifier. | |
REPLACE(text, start_position, number_of_characters, new_text) |
REPLACE("TimeTonic", 1, 4, "Demo") ➡️ Result: DemoTonic |
Replaces part of a string based on a position and a length, to modify a specific segment of text. | |
SUBSTITUTE(original_text, old_text, new_text, instance_number) |
SUBSTITUTE("TT-2026-01", "TT", "TM") ➡️ Result: TM-2026-01
|
Replaces one or more occurrences of a string with another in a text. | |
TEXT_DAY(date) |
TEXT_DAY('2024-09-17') ➡️ Result: 'Tuesday' |
Displays the day of the week for a date (e.g., 'Tuesday'). | |
EXTRACT_ELEMENT(value, number, text) |
EXTRACT_ELEMENT('apple,banana,cherry', 2, ";") ➡️ Result: banana |
Extracts an element contained in a cell based on various parameters. | |
APPLY_MASK(text, number) |
APPLY_MASK('1234567890', XXXXXX@@@@) ➡️ Result: 7890 |
Masks characters with X or reveals characters with @ in a selected column (or only the first two with @@). | |
HTML_TO_TEXT(text) |
HTML_TO_TEXT('<p>Hello world!</p>') ➡️ Result: Hello world! |
Converts HTML text into readable plain text for your collaborators. | |
SUBSTR(text, number1, number2) |
SUBSTR('Hello world', 0, 5) ➡️ Result: Hello · 0 defines the start position and 5 indicates the number of characters to extract. |
Extracts a specified number of characters from a string starting at a position you provide. | |
FORMAT_NUMBER(value, format) |
FORMAT_NUMBER(12345.678, '0,0.00') |
Formats a number with a thousands separator and decimals. | |
FORMAT_DATE(value, format) |
FORMAT_DATE(TODAY(), 'DD/MM/YYYY') |
Displays the date in day/month/year format. | |
READ_DATE(text_value, format, "classic|extended") |
READ_DATE("25-12-2024", "DD-MM-YYYY", "classic") ➡️ Result: Converts the text "25-12-2024" into a date. |
Converts text into a date. |
Numeric Functions
| Name | Syntax | Example | Description |
INT(value) |
INT(3.7) ➡️ Result: 3 INT(-3.7) ➡️ Result: -4 |
Returns the integer part of a number by rounding down. | |
AVERAGE(value1, value2, ...) |
AVERAGE(10, 15, 20) ➡️ Result: 15 |
Calculates the average of a set of numbers. | |
MEDIAN(value1, value2, ...) |
MEDIAN(1, 2, 3, 4) ➡️ Result: 2.5 |
Returns the median value of a set of numbers. | |
STDEV(value1, value2, ...) |
STDEV(4, 5, 8, 10) ➡️ Result: 2.75 |
Calculates the standard deviation of a set of numbers by measuring how values spread around the average. | |
CEILING(value) |
CEILING(4.3) ➡️ Result: 5 |
Rounds a number up to the nearest integer. | |
FLOOR(value) |
FLOOR(4.3) ➡️ Result: 4 FLOOR(-2.1) ➡️ Result: -3 |
Rounds a number down to the nearest integer (or nearest lower/equal multiple). | |
ROUND(value, precision) |
ROUND(3.14159, 2) ➡️ Result: 3.14 ROUND(1234, -2) ➡️ Result: 1200 |
Rounds a numeric value to the desired number of decimal places. | |
SUM(value1, value2, ...) |
SUM(10, 20, 30) ➡️ Result: 60 |
Adds the values of multiple columns together. | |
SUBSTRACT( number_column1, number_column2, ...) |
SUBSTRACT(100, 45) ➡️ Result: 55 |
Subtracts the content of multiple columns from each other. | |
MIN(number_column1, number_column2, ...) |
MIN(10, 25, 5) ➡️ Result: 5 |
Displays the smallest numeric value among the selected columns. | |
MAX(number_column1, number_column2, ...) |
MAX(10, 25, 5) ➡️ Result: 25 |
Displays the largest numeric value among the selected columns. | |
NB_ELEMENTS (value1, value2, ...) |
NB_ELEMENTS('apple', 'banana', 'cherry') ➡️ Result: 3 |
Displays the number of elements contained in the selected columns. | |
SUM_ELEMENTS (value1, value2, ...) |
SUM_ELEMENTS(10, 20, 30) ➡️ Result: 60 |
Sums the numeric values of the selected columns, including columns containing multiple values separated by commas. | |
MIN_NUM_ELEMENTS (value1, value2, ...) |
MIN_NUM_ELEMENTS(10, 20, 5) ➡️ Result: 5 |
Displays the smallest value among the selected columns. | |
MAX_NUM_ELEMENTS (value1, value2, ...) |
MAX_NUM_ELEMENTS(10, 20, 5) ➡️ Result: 20 |
Displays the largest value among the selected columns. |
Other Functions
| Name | Syntax | Example | Description |
|---|---|---|---|
ROWID() |
ROWID() ➡️ Example: "jklm1234" |
Returns the unique technical identifier of a row. This stable ID allows you to reference a record reliably. | |
SWITCH(source, value1, result1, value2, result2, ... , default_value) |
SWITCH(Status, "New","Urgent", "In Progress","Ongoing", "Completed","Closed", "Unknown") ➡️ Result: "New" if status is "Urgent" |
Compares an expression against multiple cases and returns the first matching result. It replaces nested IF() statements. | |
ISO_WEEKNUM(date) |
ISO_WEEKNUM(2026-01-01) ➡️ Result: 1 |
Returns the ISO week number of a given date (weeks start on Monday and week 1 contains the first Thursday of the year). | |
WEEKNUM(date) |
WEEKNUM(2026-01-10) ➡️ Result: 2 |
Returns the week number of a date (weeks start on Sunday and week 1 contains January 1st). | |
COUNTIF(value, criteria, separator) |
COUNTIF("101 205 301 50", ">100", " ") ➡️ Result: 3 |
Counts the number of elements in a text or relational field that meet a given condition after splitting using a separator. | |
IF(condition, value_if_true, value_if_false) |
IF(age > 18, "adult", "minor") |
Executes a condition and returns a value depending on whether the condition is true or false. | |
CONCAT(param1, param2, ...) |
CONCAT("Martin"," ","Simon") ➡️ "Martin Simon" |
Concatenates multiple fields together and allows adding custom text. | |
CONCAT_URL(param1, param2, ...) |
CONCAT_URL( "https://example.com/","user/123") ➡️"https://example.com/user/123" |
Dynamically builds a URL to generate a personalized link. | |
URL_TRIGGER(text) |
URL_TRIGGER("https://example.com") |
Triggers the specified URL whenever a change occurs in the table. Table and row IDs are passed as parameters. | |
GEOLOC(address) |
GEOLOC("12 Rue de Rivoli, Paris") ➡️ (lat; long) |
Returns the geographic coordinates (latitude and longitude) of a full address. | |
DISTANCE(coordA, coordB) |
DISTANCE([A], [B]) ➡️ Returns distance in meters |
Calculates the straight-line distance between two coordinates. | |
IS_EMPTY(value) |
IF(IS_EMPTY(Name), "Missing","Defined") |
Checks whether a value is empty. | |
EMPTY(value) |
EMPTY(Name) ➡️ Returns true or false |
Checks whether a value is empty and returns true or false. |