Functions
Functions can be used in cell formulas and filters. They offer a wide variety of possibilities, like aggregating records or ranges. This page documents them all.
Filters need to be computed before formulas. Therefore, filters cannot reference cells or records. This is why each function is documented as "usable in a formula" and "usable in a filter".
Each function belongs to a category from the following:
| Category | Description |
|---|---|
| Aggregation functions | Functions that group multiple values into a significant meaning. |
| Datetime functions | Functions that allow to manipulate dates and time. |
| JSON functions | Functions dealing with JSON. |
| Logic functions | Functions dealing with logic. |
| Math functions | Mathematical functions. |
| String manipulation functions | Functions that allow to manipulate strings. |
Types
Both arguments and return values are typed. The possible types are:
| Type | Description | Example(s) |
|---|---|---|
| Any | Matches any type. | |
| Bool | A boolean value, either true or false. | Boolean values cannot be expressed directly. They can be returned by some functions and by expressions. |
| Number | A floating point number, the decimal can be omitted. | -1, 0, 3.14, 42,
1337, etc |
| Range | A range of cells (same row or same column).
It is composed of two coordinates
separated by a colon :, both cells are included in the range. A range can use the A1 or x,y notation. |
2,3:7,3, A5:A11, etc |
| RecordKey | The value specified in the field key of a record. |
monitoring, deal, event, etc |
| RecordVal | It indicates a specific value in a record. A RecordVal is composed of the record key plus the path to the value. | With the following record:
{"key":"monitoring", "data": {"load":0.01}}
The RecordVal for the field load is
monitoring.load. |
| String | A string, surrounded by quotes. | "foo", "bar", etc |
Examples
Some of the spreadsheets linked bellow aggregate records. To make examples more significant, some records have already been inserted in the database:
[
{
"key": "example",
"data": {
"n": 4
}
},
{
"key": "example",
"data": {
"n": 8
}
},
{
"key": "example",
"data": {
"n": 15
}
},
{
"key": "example",
"data": {
"n": 16
}
},
{
"key": "example",
"data": {
"n": 23
}
},
{
"key": "example",
"data": {
"n": 42
}
}
]AVG()
AVG(Range)
The average of the range. If the range includes non-numerical values or empty cells, the behavior is undefined.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
AVG(RecordVal)
The average of all the record path values.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
CHAR()
CHAR(Number)
Returns an ASCII character as a string from its value.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| String | Example |
CONCATENATE()
CONCATENATE(Any, ...)
Concatenates all the parameters into one string.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| String | Example |
COUNT()
COUNT(Range)
Counts the number of cells in the range.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
COUNT(RecordVal)
Counts the number of records having this RecordVal defined.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
COUNT(RecordKey)
Counts the number of records having this RecordKey.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
DAY()
DAY(Number)
Returns the UTC day offset of the date specified in parameter.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
DAY(Number, String)
Same as DAY(Number) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
DAY(RecordVal)
Returns the day UTC offset of miliseconds Unix time RecordVal.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
DAY(RecordVal, String)
Same as DAY(RecordVal) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
HOUR()
HOUR(Number)
Returns the UTC hour offset of the date specified in parameter.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
HOUR(Number, String)
Same as HOUR(Number) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
HOUR(RecordVal)
Returns the hour UTC offset of miliseconds Unix time RecordVal.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
HOUR(RecordVal, String)
Same as HOUR(RecordVal) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
IF()
IF(Bool, Any, Any)
If the first argument is true, the second argument is returned. Otherwise, the third argument is returned.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Any | Example |
JSONCANLOOKUP()
JSONCANLOOKUP(String, String | Number...)
Parses the JSON given as first parameter and returns wether or not the field indicated by the other parameters can be looked up.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Bool | Example |
JSONGET()
JSONGET(String)
Performs an HTTP GET request at the URL given in parameter and returns the JSON body. JSONGET() expects an "application/json" Content-Type, times out after 10 seconds and does not handle bodies over 512K.
JSONGET() blocks the execution while waiting for the response. This can slow down the computation of the spreadsheet, especially if the URL is slow to respond.
The JSON returned can then be parsed with JSONLOOKUP().
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| String | Example |
JSONLOOKUP()
JSONLOOKUP(String, String | Number...)
Parses the JSON given in first parameter and returns the field indicated by the other parameters. It is recommended to check first with JSONCANLOOKUP() if the lookup is doable. A lookup fail will make the whole spreadsheet computation fail. To avoid that, see JSONLOOKUP2().
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number or String | Example |
JSONLOOKUP2()
JSONLOOKUP2(String, String | Number..., String | Number)
Parses the JSON given in first parameter and returns the field indicated by the other parameters. The last parameter is the default value, that is returned if the lookup fails.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number or String | Example |
LAST()
LAST(RecordVal)
Returns the most recent element.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number or String | Example |
LOWER()
LOWER(String)
Returns the string passed in argument in lowercase.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| String | Example |
MAX()
MAX(Range)
Returns the maximum value of the range. If the range includes non-numerical values or empty cells, the behavior is undefined.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MAX(RecordVal)
Returns the maximum value of this RecordVal.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MEDIAN()
MEDIAN(Range)
Returns the median value of the range. If the range contains an odd number of values, the return value is the average of the two middle values. If the range includes non-numerical values or empty cells, the behavior is undefined.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MIN()
MIN(Range)
Returns the minimum value of the range. If the range includes non-numerical values or empty cells, the behavior is undefined.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MIN(RecordVal)
Returns the minimum value of this RecordVal.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MINUTE()
MINUTE(Number)
Returns the UTC minute offset of the date specified in parameter.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MINUTE(Number, String)
Same as MINUTE(Number) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MINUTE(RecordVal)
Returns the minute UTC offset of miliseconds Unix time RecordVal.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MINUTE(RecordVal, String)
Same as MINUTE(RecordVal) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MONTH()
MONTH(Number)
Returns the UTC month offset of the date specified in parameter.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MONTH(Number, String)
Same as MONTH(Number) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MONTH(RecordVal)
Returns the month UTC offset of miliseconds Unix time RecordVal.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
MONTH(RecordVal, String)
Same as MONTH(RecordVal) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
NOT()
NOT()
Reverses the boolean argument.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Bool | Example |
NOW()
NOW()
Returns the current date (Unix time in miliseconds).
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
RAND()
RAND()
Returns a random number in [0.0,1.0).
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
ROUND()
ROUND(Number)
Returns the first argument rounded to the second argument number of decimals.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
SECOND()
SECOND(Number)
Returns the UTC second offset of the date specified in parameter.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
SECOND(Number, String)
Same as SECOND(Number) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
SECOND(RecordVal)
Returns the second UTC offset of miliseconds Unix time RecordVal.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
SECOND(RecordVal, String)
Same as SECOND(RecordVal) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
SUM()
SUM(Range)
Returns the sum of all the values within the range. If the range includes non-numerical values or empty cells, the behavior is undefined.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
SUM(RecordVal)
Returns the sum of all this record path values.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
UPPER()
UPPER(String)
Returns the string passed in argument in uppercase.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| String | Example |
YEAR()
YEAR(Number)
Returns the year UTC offset of the miliseconds Unix time specified in parameter.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
YEAR(Number, String)
Same as YEAR(Number) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
YEAR(RecordVal)
Returns the UTC year offset of the RecordVal.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |
YEAR(RecordVal, String)
Same as YEAR(RecordVal) with a specific timezone.
| Return type | Usable in formulas | Usable in filters | |
|---|---|---|---|
| Number | Example |