Built-in Functions

These built-in functions can be used inside text elements by enclosing them in square brackets beginning with an equals sign, which signals that the expression is a formula.

For example:

Grand total: [=SUM(total)]

The formula above uses the built-in SUM function to add up the values in the total data field.

---

Aggregates

SUM

=SUM(fieldName)

Returns the sum of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

COUNT

=COUNT(fieldName)

Returns the count of non-null values, including duplicates, in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

If fieldName is a calculation, returns the number of rows for which the calculation returns true.

COUNTDISTINCT

=COUNTDISTINCT(fieldName)

Works the same way as COUNT, but only one occurrence of each distinct value will be counted. For example, the COUNTDISTINCT of a list (A, B, A, B, C, C, C) is 3 (A, B, and C).

AVERAGE

=AVERAGE(fieldName)

Returns the average of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

MEDIAN

=MEDIAN(fieldName)

Returns the median of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

MIN

=MIN(fieldName)

Returns the minimum of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

MAX

=MAX(fieldName)

Returns the maximum of the values in the fieldName field in all rows inside the current group, if any, or all rows in the current data source if not currently inside a group.

---

Other Functions

DATE

=DATE()
=DATE(dateStr)
=DATE(dateStr, formatStr)

If no arguments are provided, returns today's date as a JavaScript date. If a dateStr argument is provided, parses the string to return the corresponding date. By default, the date format 'YYYY-MM-DD' is used for parsing. If the date string is in a different format, the format string may be provided as a second argument.

Examples

=DATE()
// Returns today's date as a JavaScript date object

=DATE('2017-02-21')
// Returns the given date, using the default format YYYY-MM-DD

=DATE('2/21/2017', 'M/D/YYYY')
// Returns the given date, parsed using the specified custom format

---

FORMAT

=FORMAT(value, formatStr)

Formats the given number or date into a string, using the specified format string.

Examples

=FORMAT(DATE(), 'M/D/YY')
// Returns e.g. "2/21/17"

=FORMAT(1234.5678, '$#,##0.00')
// Returns "$1,234.57"

---

LOOKUP

=LOOKUP(valueToFind, dataSourceId, fieldNameToSearch, fieldNameToReturn)

Find the row containing a specific value in a specific field in a data source, and return the value from another field in the same row.

Examples

Given a data source with id main_table containing the following data:

id	name	price
2	Apples	12.99
3	Oranges	15.95
4	Bananas	87.40

Then the following expression:

=LOOKUP(3, 'main_table', 'id', 'price')

returns:

15.95

The formula means: find the row that has value 3 in the id field of the main_table data source, and return the value from the price field of the same row.