1. Spreadsheet docs
  2. 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 done

React

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

FieldWhat it is
nameLetters, 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, maxThe fewest and most arguments; 0 and 255 by default
description, params, categoryShown 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
asyncThe function returns a promise; its cells show loading until it settles. A function that returns a promise is treated as async anyway
refreshFor 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
loadingWhat a cell shows while its call runs; #GETTING_DATA by default
volatileRecalculate on every calculation, as RAND and NOW do
referencesRange arguments arrive as objects with values(), cell(row, col), sheetName, row, col, rows and cols, instead of their values
errorsError 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.