Skip to content

Only aggragation functions are listed. Click here to list all functions.

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

FALSE()

FALSE()

Returns false.

Return type Usable in formulas Usable in filters
Bool 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

TRUE()

TRUE()

Returns true.

Return type Usable in formulas Usable in filters
Bool 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