JavaScript Data GridFormulas

Formula strings can be provided to cells in the grid, allowing for dynamic calculations based on other cell values.

Enabling Formulas Copy Link

To enable formulas, set the column property allowFormula = true on one or more columns and ensure that your rows have Row IDs.

const gridOptions = {
    columnDefs: [
        { field: 'product' },
        { field: 'price' },
        { field: 'quantity' },
        { field: 'subtotal', allowFormula: true },
        { field: 'tax', allowFormula: true },
        { field: 'total', allowFormula: true },
    ],
    getRowId: (params) => String(params.data.rid),

    // other grid options ...
}

When using formulas, the following features are enabled by default:

• Row Numbers

Certain features do not work in conjunction with formulas:

• Cell Expressions
• Tree Data and Row Grouping
• Pivoting and Aggregation
• Master Detail
• Server-Side Row Model, Infinite Row Model, and Viewport Row Model

Formula Syntax Copy Link

Formulas are a text string that starts with an equals sign (=) and can contain references to other cells, functions, operators, and constants.

Examples of valid formula strings:

• Constants (e.g. =3.14 or ="Hello")
• Cell references (e.g. =A1)
• Mathematical operations (e.g. =A1 + 3)
• Functions (e.g. =SUM(A1, 2))
• Cell Ranges (e.g. =SUM(A1:A10))

Constants Copy Link

Constants can be numbers (e.g. 3.14, 42, -7), strings (e.g. "Hello", "World"), or boolean values (TRUE, FALSE).

Cell references Copy Link

Cell references are used to refer to the value of another cell in the grid and are symbolised by an alphabetical column identifier followed by a numerical row identifier. After the 26th column, the columns continue with two letters (e.g. AA, AB, AC, etc.). Rows are numbered starting from 1.

Formulas can reference other cells using their column letter and row number. Columns are labelled alphabetically (A, B, C, ..., Z, AA, AB, etc.), and rows are numbered starting from 1.

Examples of cell references:

• =A1 refers to the cell in column A, row 1.
• =B2 refers to the cell in column B, row 2.

If a cell moves, the formula will always refer to the same cell, not the same position. For example, if a formula in cell C1 is =A1, and a new row is inserted above row 1, the formula will automatically update to =A2 to continue referencing the original cell. To instead always refer to the same position, use absolute references by prefixing the column letter and/or row number with a dollar sign ($):

• =$A$1 always refers to column A, row 1.
• =A$1 always refers to row 1, but the column can change.
• =$A1 always refers to column A, but the row can change.

When saving cell references, the grid converts these into a long hand format using column and row IDs to ensure that changes in the source data allow the grid to continue to refer to the correct cells when the data changes without the grid being open. For example, if the column with ID athlete is in column A, and the row with ID a is in row 1, then the formula =A1 becomes =REF(COLUMN('athlete'), ROW('a')). When this is a static reference, for example $A$1, the long hand format uses A and 1 in place of IDs, but adds a true to indicate the value is absolute (e.g =$A$1 becomes =REF(COLUMN('a', true), ROW('1', true))).

This long hand format can be used directly in your data source or application via a valueGetter, and will be converted to the short hand format when the user opens a cell editor.

Mathematical Operations Copy Link

Formulas can be used to construct mathematical expressions using cell references, constants, functions, and operators. These expressions respect the standard order of operations (PEMDAS/BODMAS).

The full list of available operators can be found below in List of Mathematical Operators.

Functions Copy Link

Formulas can use functions to perform calculations on values. Functions are a string of letters followed by parentheses containing the function arguments. Functions can take multiple arguments, which can be constants, cell references, cell ranges, or other functions.

Examples of functions:

• =SUM(A1, 2) returns the sum of the value in cell A1 and the constant value 2.
• =AVERAGE(A1:A10) returns the average of every cell value in the range A1 to A10. (e.g A1, A2, A3, ..., A10)
• =CONCAT(A1, " ", B1) returns the concatenation of the value in cell A1, a space, and the value in cell B1.
• =IF(A1 > 10, "High", "Low") returns "High" if the value in cell A1 is greater than 10, otherwise returns "Low".
• =SUM(AVERAGE(A1:B1), 2) returns the sum of the average of the values in the range A1 to B1 and the constant value 2.
• =TODAY() returns a date object representing the current date.

The full list of functions that are provided by the grid can be found below in List of Provided Functions.

Cell Ranges Copy Link

Cell ranges are used to refer to a rectangular block of cells in the grid and are symbolised by the top-left cell reference followed by a colon (:) and the bottom-right cell reference. For example, the range A1:B2 refers to the cells in column A and B, and rows 1 and 2 (i.e. A1, A2, B1, B2).

Error Codes Copy Link

If a formula results in an error, users will see the following error codes in the corresponding cell.

Custom Functions Copy Link

Custom functions can be provided to formulas by providing the gridOption property formulaFuncs.

The following example and snippet demonstrates how to write a custom function CUSTOMSUM which adds all of the provided values.

It uses the params.values iterator to simplify navigating all of the values, including those provided in ranges by flattening them into one iterable.

A user could then use this function in a formula such as:

• =CUSTOMSUM(A1:B2, 2) which would add all of the values in the range A1 to B2, and the constant 2.
• =CUSTOMSUM(1, 2, 3, 4) which would add the constant values 1, 2, 3, and 4.

In this snippet below, observe how the function uses the params.values iterator for navigating the values provided to the function, which provides all of the values from the ranges and constants in one simple iterable, meaning any input such as =CUSTOMSUM(A1:B2, 2) will be streamed as 1 (A1) -> 1 (A2) -> 1 (B1) -> 2 (B2) -> 2 for the function to process.

const gridOptions = {
    columnDefs: [
        { field: 'sales' },
        // make all rows draggable
        { field: 'calculated', allowFormula: true }

    ],
    formulaFuncs: {
        CUSTOMSUM: {
            func: (params) => {
                let total = 0;
                for (const value of params.values) {
                    total += value;
                }
                return total;
            },
        },
    },

    // other grid options ...
}

Handling Errors Copy Link

It is the responsibility of the function implementation to throw errors when the arguments are not as expected.

The example below demonstrates a custom function ERRORIFONE which errors when any of the arguments provided are 1.

The following snippet builds on our ADD function to throw an error if any of the provided arguments are not numbers, or when no arguments are provided.

const gridOptions = {
    columnDefs: [
        { field: 'sales' },
        // make all rows draggable
        { field: 'calculated', allowFormula: true }

    ],
    formulaFuncs: {
        ERRORIFONE: {
            func: (params) => {
                for (const value of params.values) {
                    if (String(value) === '1') {
                        throw 'Error, discovered a \'1\' in params';
                    }
                }
                return 'SUCCESS, no \'1\' found.';
            },
        },
    },

    // other grid options ...
}

Complex Custom Functions Copy Link

Some custom functions may need explicit context about the ranges provided to them. For example, the COUNTIF function needs the first argument to be a range, and the second argument to be a criteria to apply to that range. If the params.values iterator was used, the range values would be flattened and hard to distinguish from the criteria.

The grid provides an alternative iterator params.args which provides wrapped arguments as they were provided to the function, without any flattening or transformation. This means that ranges are provided as a RangeParam object, which is iterable for navigating the values in the range.

The following example demonstrates how to implement a custom function COUNTEQ which counts the number of values in a range that match the second parameter.

The following snippet demonstrates providing a custom function COUNTEQ which counts the number of values in a range that match the second parameter.

const gridOptions = {
    columnDefs: [
        { field: 'sales' },
        // make all rows draggable
        { field: 'calculated', allowFormula: true }

    ],
    formulaFuncs: {
        COUNTEQ: {
            func: (params) => {
                const argsArr = Array.from(params.args);
                if (argsArr.length != 2) {
                    throw 'COUNTEQ requires exactly 2 arguments';
                }
                const [range, criteria] = argsArr;
                if (range.kind !== 'range') {
                    throw 'First argument to COUNTEQ must be a range';
                }
                if (criteria.kind !== 'value' || typeof criteria.value === 'object') {
                    throw 'Second argument to COUNTEQ must be a primitive value';
                }
                const isNumCriteria = typeof criteria.value === 'number';
                let count = 0;
                for (const value of range) {
                    const coercedValue = isNumCriteria ? Number(value) : value;
                    if (coercedValue === criteria.value) {
                        count++;
                    }
                }
                return count;
            },
        },
    },

    // other grid options ...
}

Formula Data Source Copy Link

Formulas can be stored outside of the grid's rowData by providing a formulaDataSource. This is useful when you want to keep row data clean, or persist formulas separately (e.g. to local storage or your own API).

The snippet and example below illustrate an example of storing formulae in a Map object, external to the grid.

In the example, the "Total" column contains editable formulas wich are stored in an external Map. The contents of the map and the row data can be visualised with the provided buttons.

const gridOptions = {
    columnDefs: [
        { field: 'product' },
        { field: 'price' },
        { field: 'quantity' },
        { field: 'subtotal', allowFormula: true },
        { field: 'total', allowFormula: true },
    ],
    getRowId: (params) => String(params.data.id),
    formulaDataSource: {
        getFormula: ({ column, rowNode }) => {
            return formulaStore.get(formulaKey(rowNode.id, column.getColId()));
        },
        setFormula: ({ column, rowNode, formula }) => {
            const key = formulaKey(rowNode.id, column.getColId());
            if (formula === undefined) {
                formulaStore.delete(key);
            } else {
                formulaStore.set(key, formula);
            }
        },
    },

    // other grid options ...
}

A user provided datasource should provide the following interface:

Exporting from Grids containing Formulas Copy Link

When performing a CSV Export, the grid will export the evaluated values of any formulas in the grid.

When performing an Excel Export, the grid will export the formulas themselves, which should then be evaluated as normal when opening the exported file in Excel.

List of Mathematical Operators Copy Link

List of Provided Functions Copy Link

Numeric Functions

Date Functions

Text Functions

Logical Functions

Counting Functions