Custom formula functions let you extend the engine with domain-specific logic and reusable calculations.
Formula Functions API Copy Link
Custom functions are provided through the formulaFuncs grid option.
A map of 'function name' to 'function' for custom functions that are used for formulas. |
Simple Example Copy Link
The example below registers CUSTOMSUM, which iterates over all values passed to the function (including ranges) and returns their sum.
"use client";
import React, {
useCallback,
useMemo,
useRef,
useState,
StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
CellSelectionOptions,
ClientSideRowModelModule,
ColDef,
ColGroupDef,
FormulaFuncs,
FormulaFunctionParams,
GetRowIdFunc,
GetRowIdParams,
GridApi,
GridOptions,
ModuleRegistry,
NumberEditorModule,
TextEditorModule,
TooltipModule,
enableDevValidations,
} from "ag-grid-community";
import { CellSelectionModule, FormulaModule } from "ag-grid-enterprise";
if (process.env.NODE_ENV !== "production") {
enableDevValidations();
}
const modules = [
CellSelectionModule,
ClientSideRowModelModule,
NumberEditorModule,
TextEditorModule,
TooltipModule,
FormulaModule,
];
const GridExample = () => {
const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);
const [rowData, setRowData] = useState<any[]>([
{ rid: "1", gold: 1, silver: 1, totals: "=CUSTOMSUM(A1:B1)" },
{ rid: "2", gold: 1, silver: 2, totals: "=CUSTOMSUM(A2:B2)" },
{ rid: "3", gold: 4, silver: 0, totals: "=CUSTOMSUM(A3:B3)" },
{ rid: "4", gold: 0, silver: 0, totals: "=CUSTOMSUM(A4:B4)" },
{ rid: "5", gold: 2, silver: 13, totals: "=CUSTOMSUM(A5:B5)" },
{ rid: "6", gold: 0, silver: 1, totals: "=CUSTOMSUM(A6:B6)" },
{ rid: "7", gold: 9, silver: 6, totals: "=CUSTOMSUM(A7:B7)" },
{ rid: "8", gold: 0, silver: 11, totals: "=CUSTOMSUM(A1:B8, B1)" },
]);
const [columnDefs, setColumnDefs] = useState<ColDef[]>([
{ field: "gold", colId: "c0" },
{ field: "silver", colId: "c1" },
{ field: "totals", colId: "c2", cellDataType: "text", allowFormula: true },
]);
const getRowId = useCallback(
(params: GetRowIdParams) => String(params.data.rid),
[],
);
const cellSelection = useMemo<boolean | CellSelectionOptions>(() => {
return {
handle: {
mode: "fill",
},
};
}, []);
const defaultColDef = useMemo<ColDef>(() => {
return {
flex: 1,
editable: true,
};
}, []);
const formulaFuncs = useMemo<FormulaFuncs>(() => {
return {
CUSTOMSUM: {
func: (params: FormulaFunctionParams) => {
let total = 0;
for (const value of params.values) {
const num = Number(value);
if (Number.isFinite(num)) {
total += num;
}
}
return total;
},
},
};
}, []);
return (
<AgGridProvider modules={modules}>
<div style={containerStyle}>
<div style={gridStyle}>
<AgGridReact
rowData={rowData}
columnDefs={columnDefs}
getRowId={getRowId}
cellSelection={cellSelection}
defaultColDef={defaultColDef}
formulaFuncs={formulaFuncs}
/>
</div>
</div>
</AgGridProvider>
);
};
const root = createRoot(document.getElementById("root")!);
root.render(
<StrictMode>
<GridExample />
</StrictMode>,
);
const [columnDefs, setColumnDefs] = useState([
{ field: 'sales' },
{ field: 'calculated', allowFormula: true },
]);
const formulaFuncs = {
CUSTOMSUM: {
func: (params) => {
let total = 0;
for (const value of params.values) {
total += value;
}
return total;
},
},
};
<AgGridReact
columnDefs={columnDefs}
formulaFuncs={formulaFuncs}
/> Error Handling Copy Link
Your function should throw when arguments are invalid. Errors are surfaced in the grid and propagate through dependent formulas.
"use client";
import React, {
useCallback,
useMemo,
useRef,
useState,
StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
CellSelectionOptions,
ClientSideRowModelModule,
ColDef,
ColGroupDef,
FormulaFuncs,
FormulaFunctionParams,
GetRowIdFunc,
GetRowIdParams,
GridApi,
GridOptions,
ModuleRegistry,
TextEditorModule,
TooltipModule,
enableDevValidations,
} from "ag-grid-community";
import { CellSelectionModule, FormulaModule } from "ag-grid-enterprise";
if (process.env.NODE_ENV !== "production") {
enableDevValidations();
}
const modules = [
CellSelectionModule,
ClientSideRowModelModule,
FormulaModule,
TextEditorModule,
TooltipModule,
];
const GridExample = () => {
const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);
const [rowData, setRowData] = useState<any[]>([
{ rid: 1, A: 1, B: 2, C: 3 },
{ rid: 2, A: 4, B: 5, C: 6 },
{ rid: 3, A: 2, B: 5, C: 2 },
{ rid: 4, A: 7, B: 8, C: 9 },
{ rid: 5, A: 0, B: 80, C: 10 },
{ rid: 6, A: 0, B: 4, C: 7 },
{ rid: 7, A: 7, B: 2, C: 2 },
{ rid: 8, A: 1, B: 0, C: 2 },
{
rid: 9,
A: '=ERRORIFONE(REF(COLUMN("0"),ROW("1"),COLUMN("0"),ROW("8")))',
B: '=ERRORIFONE(REF(COLUMN("1"),ROW("1"),COLUMN("1"),ROW("8")))',
C: '=ERRORIFONE(REF(COLUMN("2"),ROW("1"),COLUMN("2"),ROW("8")))',
D: '=CONCAT(REF(COLUMN("0"),ROW("9"),COLUMN("2"),ROW("9")))',
},
]);
const [columnDefs, setColumnDefs] = useState<ColDef[]>([
{ field: "A", colId: "0", headerName: "Gold" },
{ field: "B", colId: "1", headerName: "Silver" },
{ field: "C", colId: "2", headerName: "Bronze" },
{ field: "D", colId: "3", headerName: "Check Error Propagation" },
]);
const getRowId = useCallback(
(params: GetRowIdParams) => String(params.data.rid),
[],
);
const cellSelection = useMemo<boolean | CellSelectionOptions>(() => {
return {
handle: {
mode: "fill",
},
};
}, []);
const defaultColDef = useMemo<ColDef>(() => {
return {
cellDataType: "text",
allowFormula: true,
editable: true,
flex: 1,
};
}, []);
const formulaFuncs = useMemo<FormulaFuncs>(() => {
return {
ERRORIFONE: {
func: (params: FormulaFunctionParams) => {
for (const value of params.values) {
if (String(value) === "1") {
throw "Error, discovered a '1' in params";
}
}
return "SUCCESS, no '1' found.";
},
},
};
}, []);
return (
<AgGridProvider modules={modules}>
<div style={containerStyle}>
<div style={gridStyle}>
<AgGridReact
rowData={rowData}
columnDefs={columnDefs}
getRowId={getRowId}
cellSelection={cellSelection}
defaultColDef={defaultColDef}
formulaFuncs={formulaFuncs}
/>
</div>
</div>
</AgGridProvider>
);
};
const root = createRoot(document.getElementById("root")!);
root.render(
<StrictMode>
<GridExample />
</StrictMode>,
);
When a function (or a referenced cell) throws an error, the cell displays #ERROR! and hovering over the cell displays the thrown error message. Errors also propagate to dependent formula cells.
Complex Example Copy Link
This example shows COUNTEQ, which receives a range and a value and counts matches. It uses params.args to validate argument types and handle ranges explicitly.
"use client";
import React, {
useCallback,
useMemo,
useRef,
useState,
StrictMode,
} from "react";
import { createRoot } from "react-dom/client";
import { AgGridReact, AgGridProvider } from "ag-grid-react";
import {
CellApiModule,
CellSelectionOptions,
ClientSideRowModelModule,
ColDef,
ColGroupDef,
FormulaFuncs,
FormulaFunctionParams,
GetRowIdFunc,
GetRowIdParams,
GridApi,
GridOptions,
ModuleRegistry,
NumberEditorModule,
TextEditorModule,
TooltipModule,
enableDevValidations,
} from "ag-grid-community";
import { CellSelectionModule, FormulaModule } from "ag-grid-enterprise";
if (process.env.NODE_ENV !== "production") {
enableDevValidations();
}
const modules = [
CellSelectionModule,
ClientSideRowModelModule,
FormulaModule,
CellApiModule,
NumberEditorModule,
TextEditorModule,
TooltipModule,
];
const GridExample = () => {
const containerStyle = useMemo(() => ({ width: "100%", height: "100%" }), []);
const gridStyle = useMemo(() => ({ height: "100%", width: "100%" }), []);
const [rowData, setRowData] = useState<any[]>([
{ rid: "r1", gold: 1, silver: 2 },
{ rid: "r2", gold: 2, silver: 2 },
{ rid: "r3", gold: 1, silver: 20 },
{ rid: "r4", gold: 3, silver: 2 },
{ rid: "r5", gold: 5, silver: 7 },
{ rid: "r6", gold: 2, silver: 2 },
{ rid: "r7", gold: 1, silver: 2 },
{
rid: "r8",
gold: 1,
silver: 2,
result: "=COUNTEQ($A$1:$B$8,2)",
},
]);
const [columnDefs, setColumnDefs] = useState<ColDef[]>([
{ field: "gold", colId: "c0" },
{ field: "silver", colId: "c1" },
{ field: "result", colId: "c2", allowFormula: true },
]);
const getRowId = useCallback(
(params: GetRowIdParams) => String(params.data.rid),
[],
);
const cellSelection = useMemo<boolean | CellSelectionOptions>(() => {
return {
handle: {
mode: "fill",
},
};
}, []);
const defaultColDef = useMemo<ColDef>(() => {
return {
editable: true,
flex: 1,
};
}, []);
const formulaFuncs = useMemo<FormulaFuncs>(() => {
return {
COUNTEQ: {
func: (params: FormulaFunctionParams) => {
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;
},
},
};
}, []);
return (
<AgGridProvider modules={modules}>
<div style={containerStyle}>
<div style={gridStyle}>
<AgGridReact
rowData={rowData}
columnDefs={columnDefs}
getRowId={getRowId}
cellSelection={cellSelection}
defaultColDef={defaultColDef}
formulaFuncs={formulaFuncs}
/>
</div>
</div>
</AgGridProvider>
);
};
const root = createRoot(document.getElementById("root")!);
root.render(
<StrictMode>
<GridExample />
</StrictMode>,
);
Best Practices Copy Link
- Validate argument counts and types early.
- Prefer iterators (
params.values) for large ranges to avoid unnecessary allocations. - Keep functions pure and fast to avoid performance issues on large grids.
See Formula Reference for built-in functions that can inspire custom implementations.