Both engines accept array constants in braces. Commas separate columns and semicolons separate rows: ={1,2;3,4} spills into two rows and two columns. ={1,2,3} is one row; ={1;2;3} is one column. Normal spill limits and collision rules apply.
Elements can be numbers with an optional sign, quoted text, TRUE, FALSE, or error constants such as #N/A. Every row must have the same length. Empty arrays, references, and expressions inside braces are invalid and return #VALUE!.
Functions that accept arrays can use these values directly, for example =SUM({1,2,3}) returns 6 and =INDEX({1,2;3,4},2,1) returns 3. Copy and structural edits preserve constants and their text elements. XLSX import and export preserve the formula source.
Spill references
Put # after a cell reference to refer to the whole spill of the formula in that cell. If A1 contains =SEQUENCE(D1), then =SUM(A1#) adds all the values in the spill, and =SORT(A1#,1,-1) spills them in reverse order. When the spill changes size, formulas that use A1# use the new range. A sheet name is permitted: ='Report'!B2#.
A1# returns #REF! when the cell does not hold a spill: for example, the cell contains a value, or a collision blocks the spill (#SPILL!). If you delete the row or column of the anchor cell, the reference becomes #REF!. Both engines support spill references. XLSX files store A1# as _xlfn.ANCHORARRAY(A1); import and export convert between the two forms.
Arithmetic on a whole spill applies to each element. For example, =A1#*10 multiplies each value in the spill by 10 and spills the result.
Array arithmetic
Operators apply to each element of a range, array constant, spill reference, or dynamic-array result. This includes arithmetic (+, -, *, /, ^), text concatenation (&), comparisons, unary + and -, and %.
For example, =A1:A3*10 spills three results. =FILTER(A1:A5,B1:B5>2) uses a comparison array to select rows. =SUMPRODUCT((A1:A5>2)*B1:B5) multiplies the comparison results by the values in column B.
A scalar applies to every element. Rows and columns expand separately. A size of one expands to match the other operand. For example, ={1,2,3}+{10;20} returns {11,12,13;21,22,23}. If two sizes differ and neither is one, the result uses the larger size. Positions outside the smaller operand return #N/A.
Each element uses the scalar rules for values and errors. An error affects its element, not the whole array. Functions that accept arrays use the results directly. For example, =SUM(A1:A3*B1:B3) returns one sum. A top-level array result spills into adjacent cells. A blocked spill returns #SPILL!. Array size and memory limits still apply.
Dynamic arrays and spills
FILTER, SORT, UNIQUE, TRANSPOSE, SEQUENCE, TAKE, DROP, CHOOSECOLS, and CHOOSEROWS return rectangular values. A direct range formula such as =A1:B4 also spills:
FILTER(array, include, [if_empty]) accepts a one-column include range matching the array's rows or a one-row include range matching its columns. Shape mismatches are #VALUE!. No selected values returns if_empty, or #CALC! when omitted.
SORT(array, [sort_index], [sort_order], [by_col]) defaults to the first column, ascending. sort_index selects the row/column key; sort_order is 1 or -1; by_col=TRUE sorts columns instead of rows. For several keys, use SORTBY in the full engine.
UNIQUE(array, [by_col], [exactly_once]) preserves first-seen order. by_col=TRUE compares columns; exactly_once=TRUE keeps only items occurring once. An empty result is #CALC!.
TRANSPOSE(array) swaps rows and columns.
SEQUENCE(rows, [columns], [start], [step]) requires positive integer dimensions and defaults to one column, start 1, step 1; values fill row-major.
TAKE/DROP(array, rows, [columns]) use positive counts from the start and negative counts from the end. A zero or empty result is #CALC!; counts beyond an axis clamp to that axis.
CHOOSECOLS/CHOOSEROWS(array, index1, …) use 1-based positive indices and end-relative negative indices. Repeated indices repeat output axes; zero/out-of-range indices return #VALUE!.
Errors inside a returned array stay at their corresponding spill children; a mixed result such
as {1;#N/A;2} does not collapse to an anchor-only error.
The formula cell is the anchor and owns the complete runtime rectangle. Spill children
have no formula source or persisted document identity. store.getSpillAnchor(address) returns
the anchor for either the anchor or a child, and returns null for an ordinary cell.
Spills publish atomically. Any nonempty destination, another spill, merge, validation or
protected range, unloaded paged cell, or sheet boundary makes the anchor #SPILL!; no partial
children remain. Styles, conditional formatting, and hidden rows/columns do not obstruct.
Editing a projected child through canonical core mutations is rejected. Edit or clear the
anchor instead. Structural and metadata changes invalidate ownership and recompute it.
Snapshots and XLSX export serialize only the anchor formula. Children recompute after
hydration. Internal rich copy preserves the anchor formula while treating children as
derived blanks on paste; external TSV receives the displayed spill values. History snapshots
likewise restore the anchor and recompute children, so undo never persists stale projections.
Unsupported OOXML array/data-table formula records remain inert source text with an explicit
warning rather than being silently treated as Sheetwrite spills.
Each array dimension is capped at Excel's row/column limits, one spill is capped at 1,000,000
cells and 64 MiB of bounded value/intermediate storage, and one dynamic recompute pass is
capped at 2,000,000 cell operations. Materialized spill ownership across the complete store is
also capped at 1,000,000 cells and 64 MiB, including per-child error metadata. Installation
checks that cumulative budget atomically; clearing, shrinking, or removing a spill releases its
ownership. Oversized work returns stable #NUM! without partial children, while unstable or
colliding shapes return an explicit error rather than truncating.
The admission check happens before materialization when the shape is statically knowable and is repeated before spill ownership is installed. It accounts source/result copies and per-item auxiliary storage; a shape that could exceed a ceiling is rejected rather than optimistically allocated. A recompute either atomically replaces the complete previous spill or leaves no projected children.
const grid: Gridconstgrid:Gridconstgrid:GridThe imperative core grid this controller owns.API reference →Grid.store: StoreinterfaceGrid {store:Store;}interfaceGrid {store:Store;}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 →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 →GridOptions.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 →a: null[]let a:null[]let a:null[]b: null[]let b:null[]let b:null[]