- Spreadsheet docs
- Developers
Custom functions
Add your own worksheet functions in JavaScript, synchronous or asynchronous, with descriptions for Insert Function, refresh intervals, spilled results and errors.
Your own functions can sit next to the 502 built-in ones: a price lookup from your API, a company-specific calculation, a check against your rules. Users type them in formulas like any other function, see them in Insert Function and in the formula hints, and they recalculate with the workbook. A function can return a value, an array that spills, a date or an error, and it can be asynchronous.
Define a function
JavaScript
import { mountWorkbook } from '@8sheets/core';
const wb = await mountWorkbook(document.querySelector('#sheet'));
wb.functions.define({
name: 'VAT',
min: 1, max: 2,
category: 'Financial',
description: 'The VAT on a net amount',
params: [{ name: 'net', description: 'The amount before tax' }, { name: 'rate', optional: true }],
fn: ([net, rate = 0.23]) => (typeof net === 'number' ? net * rate : { error: '#VALUE!' }),
});
wb.functions.define({
name: 'FX.RATE',
min: 2, max: 2,
async: true,
refresh: { every: 60_000 }, // fetch again every minute
description: 'The exchange rate between two currencies',
fn: async ([from, to]) => (await (await fetch(`/api/rates/${from}/${to}`)).json()).rate,
});
wb.activeSheet.range('A1').values = [[100]];
wb.activeSheet.range('B1').formulas = '=VAT(A1)';
wb.activeSheet.range('C1').formulas = '=A1*FX.RATE("EUR","USD")';
await wb.functions.whenSettled(); // every async call has settled and calculation is doneReact
import { Sheet } from '@8sheets/react';
function defineFunctions(wb) {
if (wb.functions.has('VAT')) return; // page-wide: define once
wb.functions.define({
name: 'VAT', min: 1, max: 2, category: 'Financial', description: 'The VAT on a net amount',
params: [{ name: 'net' }, { name: 'rate', optional: true }],
fn: ([net, rate = 0.23]) => net * rate,
});
}
export function Invoice() {
return (
<div style={{ height: 480 }}>
<Sheet onReady={defineFunctions} />
</div>
);
}Vue
<script setup>
import { Sheet } from '@8sheets/vue';
function defineFunctions(wb) {
if (wb.functions.has('VAT')) return;
wb.functions.define({
name: 'VAT', min: 1, max: 2, category: 'Financial', description: 'The VAT on a net amount',
params: [{ name: 'net' }, { name: 'rate', optional: true }],
fn: ([net, rate = 0.23]) => net * rate,
});
}
</script>
<template>
<Sheet style="height: 480px" @ready="defineFunctions" />
</template>Angular
import { Component } from '@angular/core';
import { SheetComponent, SheetsWorkbook } from '@8sheets/angular';
@Component({
selector: 'app-invoice',
imports: [SheetComponent],
template: `<eight-sheet class="grid" (ready)="defineFunctions($event)" />`,
styles: `.grid { height: 480px; }`,
})
export class Invoice {
defineFunctions(wb: SheetsWorkbook) {
if (wb.functions.has('VAT')) return;
wb.functions.define({
name: 'VAT', min: 1, max: 2, category: 'Financial', description: 'The VAT on a net amount',
params: [{ name: 'net' }, { name: 'rate', optional: true }],
fn: ([net, rate = 0.23]: any[]) => net * rate,
});
}
}The function spec
| Field | What it is |
|---|---|
name | Letters, digits, _ and ., starting with a letter or _, such as FX.RATE. Any case; stored upper case. A built-in function's name is refused with a RangeError |
fn(args, ctx) | The code. args are plain values: numbers, strings, booleans, null for an empty cell, and 2D arrays for ranges. ctx is { row, col, sheetName } of the calling cell |
min, max | The fewest and most arguments; 0 and 255 by default |
description, params, category | Shown by Insert Function and the formula hints. params are { name, description, optional }; category is a built-in one such as Financial or Text (see wb.functions.categories), or your own; Custom by default |
async | The function returns a promise; its cells show loading until it settles. A function that returns a promise is treated as async anyway |
refresh | For async functions: args (the default) calls again when the arguments change, recalc on every calculation, once only once per cell, or { every: ms } on a timer |
loading | What a cell shows while its call runs; #GETTING_DATA by default |
volatile | Recalculate on every calculation, as RAND and NOW do |
references | Range arguments arrive as objects with values(), cell(row, col), sheetName, row, col, rows and cols, instead of their values |
errors | Error arguments reach fn as { error: '#N/A' } instead of making the result that error |
Return values. A number, string, boolean or Date; a 2D array, which spills into the cells below and to the right; { error: '#VALUE!' } (or any Excel error) for an error; or a promise of one of these.
wb.functions.define({ name: 'SQUARES', min: 1, max: 1, fn: ([n]) => Array.from({ length: n }, (_, i) => [i + 1, (i + 1) ** 2]) });
// =SQUARES(3) spills a 3 by 2 block
Manage functions
wb.functions.has('VAT'); // true
wb.functions.list(); // [{ name, category, description, params, min, max, async, volatile }]
wb.functions.remove('VAT'); // formulas that use it show #NAME? again
await wb.functions.whenSettled();
Defining or removing a function recalculates every workbook on the page, so formulas that showed #NAME? start working.
Page-wide, like the built-ins
Custom functions belong to the page, as the built-in ones do: every workbook on the page, or in one Node process, can call a function once it is defined, and it stays until remove or the page goes away. In the components, define functions once (check wb.functions.has(name)), not every time a component mounts.
Limits
- Files store formulas, not functions. A workbook saves the formula text and its last results. Another page that opens it needs the same functions defined; until then those cells show
#NAME?when they recalculate. - Your code runs in the page. Functions run on the main thread, inside calculation; keep synchronous ones fast and move slow work into async functions.
- Failures: a function that throws, or a promise that rejects, shows
#VALUE!(a synchronous throw is also logged to the console). Return an error value such as{ error: '#N/A' }when you want a specific one. - With
onlineFeatures: false, your own functions keep working: they are your code, and decide themselves what to fetch.