Host functions need the full engine. Select it with initSheetwrite(undefined, formulas), where formulas is import * as formulas from "@sheetwrite/formulas", before you create the store. On the default engine, registerFunction throws a SheetwriteError with code optional-backend-unavailable, so the default engine binary does not grow.
Import the functions from @sheetwrite/core/functions. An application that does not import this entry carries none of its JavaScript.
registerFunction(store, metadata, implementation) adds a function to one store and recalculates every formula. Registering the same name again replaces the function. unregisterFunction(store, name) removes it, and formulas that call it show #NAME?. Names are not case-sensitive.
A name must start with a letter or _ and contain only letters, digits, _ and .. The store refuses a name that is a built-in function of the active engine or a cell reference, such as A1 or R1C1. A refused name or malformed metadata throws a SheetwriteError with code invalid-function.
Arguments and results
The function is synchronous. The engine calls it during recalculation.
A scalar argument or a single cell arrives as a number, text, boolean, or null for an empty cell.
A range or an array of more than one cell arrives as an array of rows.
An omitted argument arrives as null.
An argument that is an error, or a range that contains an error, skips the call. The formula shows that error.
The function returns a number, text, boolean, or null for an empty result. A thrown error, a promise, or any other value shows #VALUE!. A number that is not finite shows #NUM!. A failure never stops recalculation.
The function must not read calculated cells or change the store. The store is busy while it calculates. Reads that need the engine, such as getCell, and changes such as applyTransaction throw a SheetwriteError with code blocked before they reach the engine. If the function does not catch it, the formula shows #VALUE!. The document stays as it was. Use the arguments instead.
Volatility
Set volatile: true in the metadata for a function whose result can change without a change in its arguments. A volatile function runs again on every edit and on store.recalculateVolatile(), as RAND does. Other functions run again only when their arguments change.
Snapshots and collaboration
Snapshots, history and collaboration keep only the formula text. A store loaded from a snapshot shows #NAME? for a host function until the host registers it again. Each client calculates host functions locally, so each client must register the same functions to show the same values.
In the ribbon and toolbar
While the store has a registered function, the ribbon shows a Formulas tab with Insert Function, and the toolbar shows the same command. It lists the host functions with their signature and description; a pick starts =NAME( in the focus cell. See Toolbar and ribbon.
Function metadata
await listFunctions(store) returns one list of FormulaFunctionMetadata: the built-in functions of the active engine, then the host functions of the store in registration order. The table of built-in functions loads on the first call, so an application that never asks for it does not download it. Built-in and host functions use the same fields:
Field
Meaning
name
Upper-case function name.
description
One sentence about the function. For a built-in function, it names the function family.
arguments
name, types, optional and repeating for each argument.
returnKind
The kind of result, for example number or text.
examples
Optional example formulas.
volatile
Whether the function runs again on every edit.
function registerFunction(store: Store, metadata: FormulaFunctionMetadata, implementation: FormulaFunction): voidfunctionregisterFunction(store:Store,metadata:FormulaFunctionMetadata,implementation:FormulaFunction,):voidfunctionregisterFunction(store:Store,metadata:FormulaFunctionMetadata,implementation:FormulaFunction,):voidRegisters a synchronous JavaScript function that formulas in store can call, then recalculates every formula. Registering a name again replaces the function. The function receives each argument as a scalar, or as row arrays for a block of more than one cell, and returns a number, text, boolean, or null for an empty result. A thrown error or any other result shows #VALUE!. The function must not read calculated cells or change the store: calls that need the engine throw a blocked error. Snapshots keep only formula text, so register the function after loading.@throws {SheetwriteError} invalid-function when the store is not a SheetwriteStore, when the name is a built-in function, a cell reference, or not a function name, or when the metadata is malformed; blocked when a host function calls it; optional-backend-unavailable on the default engine, because only the full engine (@sheetwrite/formulas) has host functions.Exampleimporttype { Store } from"@sheetwrite/core";import { registerFunction } from"@sheetwrite/core/functions";functionaddTaxRate(store:Store) {registerFunction(store,{name: "TAXRATE",description: "Sales tax rate of a region.",arguments: [{ name: "region", types: ["text"] }],returnKind: "number",},(region) => (region ==="EU"?0.2:0.07),);}See also/docs/features/formulas/custom-functions/API reference →const grid: Gridconstgrid:Gridconstgrid:GridThe imperative core grid this controller owns.API reference →Grid.store: StoreinterfaceGrid {store:Store;}interfaceGrid {store:Store;}FormulaFunctionMetadata.name: stringinterfaceFormulaFunctionMetadata {name:string;}interfaceFormulaFunctionMetadata {name:string;}User-facing caption.API reference →FormulaFunctionMetadata.description: stringinterfaceFormulaFunctionMetadata {description:string;}interfaceFormulaFunctionMetadata {description:string;}API reference →FormulaFunctionMetadata.arguments: readonly FormulaArgumentMetadata[]interfaceFormulaFunctionMetadata {arguments:readonlyFormulaArgumentMetadata[];}interfaceFormulaFunctionMetadata {arguments:readonlyFormulaArgumentMetadata[];}FormulaArgumentMetadata.name: stringinterfaceFormulaArgumentMetadata {name:string;}interfaceFormulaArgumentMetadata {name:string;}User-facing caption.API reference →FormulaArgumentMetadata.types: readonly string[]interfaceFormulaArgumentMetadata {types:readonlystring[];}interfaceFormulaArgumentMetadata {types:readonlystring[];}API reference →FormulaFunctionMetadata.returnKind: stringinterfaceFormulaFunctionMetadata {returnKind:string;}interfaceFormulaFunctionMetadata {returnKind:string;}API reference →FormulaFunctionMetadata.examples?: readonly string[] | undefinedinterfaceFormulaFunctionMetadata {examples?:readonlystring[] |undefined;}interfaceFormulaFunctionMetadata {examples?:readonlystring[] |undefined;}API reference →region: FormulaArgumentlet region:FormulaArgumentlet region:FormulaArgumentAPI reference →Store.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultStore.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultStore.applyTransaction(tx: Transaction, options?: TransactionApplicationOptions): ApplyTransactionResultApply a low-level storage transaction. This bypasses Grid read-only checks and Grid undo/redo history. Use Grid.applyTransaction for normal host-driven edits. Queued and flushed at a barrier — never reentrant.API reference →Transaction.patches: DocumentOp[]interfaceTransaction {patches:DocumentOp[];}interfaceTransaction {patches:DocumentOp[];}Ordered document operations submitted as one store commit.API reference →addr: CellAddresslet addr:CellAddresslet addr:CellAddressAPI reference →CellAddress.sheet: stringinterfaceCellAddress {sheet:string;}interfaceCellAddress {sheet:string;}Sheet identity used by the simple data-first adapter. Defaults to sheet1.API reference →CellAddress.row: numberinterfaceCellAddress {row:number;}interfaceCellAddress {row:number;}0-based row of the cell that holds the top-left corner.API reference →CellAddress.col: numberinterfaceCellAddress {col:number;}interfaceCellAddress {col:number;}0-based column of the cell that holds the top-left corner.API reference →value: CellValuelet value:CellValuelet value:CellValueAPI reference →value: CellScalarlet value:CellScalarlet value:CellScalarAPI reference →src: stringlet src:stringlet src:stringfunction createGrid(host: HTMLElement, opts: GridOptions): GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridCreates and mounts an imperative Grid in the supplied host element.Exampleimport { createGrid, initSheetwrite } from"@sheetwrite/core";awaitinitSheetwrite();consthost= document.createElement("div");host.style.height ="400px";document.body.append(host);constgrid=createGrid(host, {workbook: {activeSheet: "sales",sheets: [{ id: "sales", name: "Sales", rowCount: 2,columns: [{ key: "amount", header: "Amount", type: "number", width: 120 }] }],},data: { rowCount: 2, columns: { amount: [12, 18] } },});grid.scrollToCell({ sheet: "sales", row: 1, col: 0 });// Call when the host view is removed.grid.destroy();See alsoGrid *See alsoGridOptionsAPI reference →function initSheetwrite(source?: BufferSource | URL | string | Request | WebAssembly.Module, engine?: SheetwriteEngine): Promise<void>functioninitSheetwrite(source?:BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>functioninitSheetwrite(source?:|BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>Load one WASM engine before createGrid. The default is @sheetwrite/wasm. Pass the @sheetwrite/formulas module as the second argument to select the full engine.Exampleimport { initSheetwrite } from"@sheetwrite/core";import*as formulas from"@sheetwrite/formulas";awaitinitSheetwrite(undefined, formulas);See also/docs/start/choose-an-engine/See alsocreateGridAPI reference →const host: HTMLElement | nullconsthost:HTMLElement|nullconsthost:HTMLElement|nullvar document: Documentvar document:Documentvar document:Documentwindow.document returns a reference to the document contained in the window. MDN ReferenceAPI reference →ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement | null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)Returns the first element that is a descendant of node that matches selectors. MDN Referenceinterface HTMLElementinterfaceHTMLElementinterfaceHTMLElementThe HTMLElement interface represents any HTML element. MDN Referencevar Error: ErrorConstructor
new (message?: string, options?: ErrorOptions) => Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)const host: HTMLElementconsthost:HTMLElementconsthost:HTMLElementElementCSSInlineStyle.style: CSSStyleDeclarationinterfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}interfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}MDN ReferenceAPI reference →CSSStyleDeclaration.height: stringinterfaceCSSStyleDeclaration {height:string;}interfaceCSSStyleDeclaration {height:string;}MDN ReferenceAPI reference →var undefinedvar undefinedvar undefinedGridOptions.workbook: WorkbookinterfaceGridOptions {workbook:Workbook;}interfaceGridOptions {workbook:Workbook;}Live workbook schema adopted by the store and updated by document operations.API reference →Workbook.activeSheet: stringinterfaceWorkbook {activeSheet:string;}interfaceWorkbook {activeSheet:string;}Active sheet ID and initial tab presented when the grid is created.API reference →Workbook.sheets: Sheet[]interfaceWorkbook {sheets:Sheet[];}interfaceWorkbook {sheets:Sheet[];}Sheets in display/tab order.API reference →Sheet.id: stringinterfaceSheet {id:string;}interfaceSheet {id:string;}Stable identifier, unique within the workbook and used by every cell address.API reference →Sheet.name: stringinterfaceSheet {name:string;}interfaceSheet {name:string;}User-facing sheet name shown in tabs and workbook exports.API reference →Sheet.rowCount: numberinterfaceSheet {rowCount:number;}interfaceSheet {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →Sheet.columns: Column[]interfaceSheet {columns:Column[];}interfaceSheet {columns:Column[];}Ordered schema; array positions are the zero-based column coordinates.API reference →Column.key: stringinterfaceColumn {key:string;}interfaceColumn {key:string;}Non-empty key, unique within the sheet, used to map input and datasource values.API reference →Column.header: stringinterfaceColumn {header:string;}interfaceColumn {header:string;}Schema label used by table exports and data-grid presentation headers.API reference →Column.type: CellFormatinterfaceColumn {type:CellFormat;}interfaceColumn {type:CellFormat;}Controls cell input parsing and default value formatting for this column.API reference →Column.width: numberinterfaceColumn {width:number;}interfaceColumn {width:number;}Unzoomed column width in CSS pixels.API reference →GridOptions.data?: ColumnarData | undefinedinterfaceGridOptions {data?:ColumnarData|undefined;}interfaceGridOptions {data?:ColumnarData|undefined;}Eager column-major values loaded into workbook.activeSheet; use instead of datasource.API reference →ColumnarData.rowCount: numberinterfaceColumnarData {rowCount:number;}interfaceColumnarData {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →ColumnarData.columns: Record<string, ArrayLike<CellScalar | CellValue>>interfaceColumnarData {columns:Record<string, ArrayLike<CellScalar|CellValue>>;}interfaceColumnarData {columns:Record<string,ArrayLike<CellScalar|CellValue>>;}Exact column runs represented by every returned row.API reference →region: null[]let region:null[]let region:null[]rate: null[]let rate:null[]let rate:null[]