> ## Documentation Index
> Fetch the complete documentation index at: https://docs.reducto.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom functions

A custom function is a function that you write in JavaScript and that a formula calls by its name, such as `=ACME.QR(A2)`. It can return a number, text, a logical value, an error, a picture or rows that spill. It can also return a promise, to get data from a server.

Microsoft Excel does not need your code to open the file. When you save, each result that a formula uses goes into the file with the formula. Excel shows these results with no add-in.

Press **Run**. Then change a link in column A: its QR code in column B changes.

```ts theme={null}
// `workbook` is the open workbook. Each run is one undo step in the grid.
import { defineFunction, PictureValue } from "@reductoai/xlsx";
import type { ExcelWorkbook } from "@reductoai/xlsx/officejs";
import { encode } from "uqr";

declare const workbook: ExcelWorkbook;

/** A QR code as PNG bytes, 8 pixels to a module. */
function qrPng(text: string): Uint8Array {
  const { data, size } = encode(text, { ecc: "M", border: 2 });
  const canvas = document.createElement("canvas");
  canvas.width = canvas.height = size * 8;
  const ctx = canvas.getContext("2d")!;
  ctx.fillStyle = "#FFFFFF";
  ctx.fillRect(0, 0, canvas.width, canvas.height);
  ctx.fillStyle = "#000000";
  data.forEach((row, y) => row.forEach((dark, x) => dark && ctx.fillRect(x * 8, y * 8, 8, 8)));
  const base64 = canvas.toDataURL("image/png").split(",")[1]!;
  return Uint8Array.from(atob(base64), (ch) => ch.charCodeAt(0));
}

const qr = defineFunction({
  name: "ACME.QR",
  description: "Makes a QR code of the text, as a picture in the cell.",
  example: '=ACME.QR("https://reducto.ai")',
  helpUrl: "https://docs.reducto.ai",
  args: [{ name: "text", description: "What the code holds, such as a URL" }],
  compute: ([text]) => new PictureValue(qrPng(String(text ?? "")), `QR code: ${text}`),
});
workbook.model.defineFunction(qr);

const sheet = workbook.worksheets.getActiveWorksheet();
sheet.getRange("A1:B1").values = [["Link", "QR code"]];
sheet.getRange("A2:A4").values = [
  ["https://reducto.ai"],
  ["https://example.com"],
  ["Hello from a cell"],
];
sheet.getRange("B2:B4").formulas = [["=ACME.QR(A2)"], ["=ACME.QR(A3)"], ["=ACME.QR(A4)"]];

sheet.getRange("A1:B1").format.font.bold = true;
sheet.getRange("2:4").format.rowHeight = 72;
sheet.getRange("A:A").format.columnWidth = 160;
sheet.getRange("B:B").format.columnWidth = 80;
sheet.getRange("A2:B4").format.verticalAlignment = "Center";
sheet.getRange("B2:B4").format.horizontalAlignment = "Center";
```

On this page, other packages load from [esm.sh](https://esm.sh). In your app, run `npm install uqr`.

## Define a function

`defineFunction` checks a definition and gives it back.

* `name`: a namespace, a dot and a name, as Excel add-ins name their functions: `ACME.QR`. The namespace keeps the name apart from Excel's own functions, now and in the future. Formulas call the function in any case.
* `description`, and a `description` for each argument: the editor shows them when you type the function.
* `example` and `helpUrl`: optional. The ScreenTip shows the example. **Help on this function** opens the `helpUrl`, which must start with `http` or `https`.
* `args`: the arguments, in order.
* `compute(args)`: gives the result, or a promise of it. The same arguments must give the same result, unless you set `maxAgeMs`.
* `maxAgeMs`: optional. A result this old computes again. See [Wait for a result](#wait-for-a-result).

| Argument option | What it does |
| - | - |
| `optional` | The formula can leave the argument out. `compute` then gets `null`. An optional argument comes after each required one. |
| `range` | `compute` gets the rows of a range, as `FunctionValue[][]`. Without it, a range gives its value in the formula's row or column, as in Excel. |
| `acceptErrors` | `compute` gets an error value in this argument. Without it, the function gives that error and `compute` does not run. |

| `compute` gives | The cell shows |
| - | - |
| A number, text, `true` or `false`, `null` | That value; `null` is an empty result |
| An `XlError` | That error |
| A `PictureValue` | The picture in the cell. See [Pictures in cells](https://docs.reducto.ai/office/xlsx/recipes/pictures-in-cells.md) |
| Rows, as `[[1, 2], [3, 4]]` | The values, spilled |
| A thrown error, `NaN` or `Infinity` | `#VALUE!`, or `#NUM!` for a number that is not finite |
| A promise | `#BUSY!` until it settles, then its value. A rejected promise gives `#VALUE!` |

## Register it

A function belongs to the workbook. A formula that calls a function that is not registered shows `#NAME?`. When you register the function, the formula calculates.

```ts theme={null}
import { Workbook } from "@reductoai/xlsx";

const wb = Workbook.open(bytes);
wb.defineFunction(qr);
wb.sheet(0).setFormula(1, 1, "=ACME.QR(A2)");
console.log(wb.sheet(0).value(1, 1)); // a PictureValue
```

The editor registers its `functions` on each workbook that it opens:

```ts theme={null}
import { mountXlsxEditor } from "@reductoai/xlsx-editor";

mountXlsxEditor(document.getElementById("app")!, { workbook: bytes, functions: [qr] });
```

`compute` runs once for each different set of arguments. A second cell with the same arguments uses the first result.

## Help in the editor

The editor shows the help of a registered function, as it does for Excel's own functions:

* Type `=ACME.` in a cell. AutoComplete lists the functions, with the description of the one that you select.
* Type `=ACME.QR(`. The ScreenTip shows the arguments, the example and the help link.
* Press **Shift+F3** for **Insert Function**. The **User Defined** category lists the functions, and the search finds them by their descriptions.

The help belongs to the workbook. Two editors on one page with different functions each show their own.

## Spill rows

When `compute` gives rows, the result spills into the cells below and to the right, as in Excel. The file keeps the rows too, so Excel shows the same spill.

Press **Run**. Then change the text in column A: the spill changes size.

```ts theme={null}
import { defineFunction } from "@reductoai/xlsx";
import type { ExcelWorkbook } from "@reductoai/xlsx/officejs";

declare const workbook: ExcelWorkbook;

const split = defineFunction({
  name: "ACME.SPLIT",
  description: "Splits text at a separator into a column, or a row when wide is TRUE.",
  example: '=ACME.SPLIT("red,green,blue", ",")',
  args: [
    { name: "text", description: "The text to split" },
    { name: "separator", description: "The text between parts, such as a comma" },
    { name: "wide", optional: true, description: "TRUE spills to the right" },
  ],
  compute: ([text, separator, wide]) => {
    const parts = String(text ?? "").split(String(separator || ","));
    return wide ? [parts] : parts.map((part) => [part]);
  },
});
workbook.model.defineFunction(split);

const sheet = workbook.worksheets.getActiveWorksheet();
sheet.getRange("A1:C1").values = [["Text", "Column", "Row"]];
sheet.getRange("A2").values = [["red,green,blue"]];
sheet.getRange("B2:C2").formulas = [['=ACME.SPLIT(A2, ",")', '=ACME.SPLIT(A2, ",", TRUE)']];
sheet.getRange("A1:C1").format.font.bold = true;
sheet.getRange("A:A").format.columnWidth = 120;
```

## Wait for a result

When `compute` returns a promise, the cell shows `#BUSY!`, as Excel does for an add-in function. When the promise settles, the cell and each formula that reads it update. Two cells with the same arguments share one call.

Press **Run**. The rates come from [Frankfurter](https://frankfurter.dev), a free exchange-rate service. Then type another currency in column B, such as `JPY`.

```ts theme={null}
import { defineFunction } from "@reductoai/xlsx";
import type { ExcelWorkbook } from "@reductoai/xlsx/officejs";

declare const workbook: ExcelWorkbook;

const fx = defineFunction({
  name: "ACME.FX",
  description: "The exchange rate from one currency to another, as of the last working day.",
  args: [
    { name: "from", description: "A currency code, such as USD" },
    { name: "to", description: "A currency code, such as EUR" },
  ],
  maxAgeMs: 60 * 60 * 1000,
  compute: async ([from, to]) => {
    const url = `https://api.frankfurter.dev/v1/latest?base=${from}&symbols=${to}`;
    const response = await fetch(url);
    if (!response.ok) throw new Error(`${response.status}`);
    const { rates } = await response.json();
    return rates[String(to).toUpperCase()];
  },
});
workbook.model.defineFunction(fx);

const sheet = workbook.worksheets.getActiveWorksheet();
sheet.getRange("A1:D1").values = [["From", "To", "Rate", "100 in it"]];
sheet.getRange("A2:B4").values = [
  ["USD", "EUR"],
  ["USD", "SGD"],
  ["EUR", "GBP"],
];
sheet.getRange("C2:D4").formulas = [
  ["=ACME.FX(A2,B2)", "=100*C2"],
  ["=ACME.FX(A3,B3)", "=100*C3"],
  ["=ACME.FX(A4,B4)", "=100*C4"],
];
sheet.getRange("A1:D1").format.font.bold = true;
sheet.getRange("C2:D4").numberFormat = [
  ["0.0000", "#,##0.00"],
  ["0.0000", "#,##0.00"],
  ["0.0000", "#,##0.00"],
];

// Wait for every call to settle, as before a save.
await workbook.model.functionsSettled();
console.log(sheet.getRange("C2:C4").values);
```

| To | Do this |
| - | - |
| Wait until no formula waits for a result | `await wb.functionsSettled()`. Do this before `save()`: a call that is still running is not in the file |
| Get new results | `wb.refreshFunctions("ACME.FX")`, or `wb.refreshFunctions()` for every function. The old results show until the new ones arrive |
| Get new results on a schedule | Set `maxAgeMs` (1000 or more) in the definition. The old result shows until the new one arrives |
| Read the cells again when results arrive | `wb.onFunctionResults(listener)`. The grid does this itself |

## In Excel

On save, the workbook gets a very hidden sheet, `_FunctionCache`. It holds the arguments and the result of each call that a formula uses. For rows, it holds the number of rows and columns and each value. Each function also gets a defined name with the same name, as `ACME.QR`. The name holds a `LAMBDA` that looks the arguments up on that sheet. For rows, the `LAMBDA` builds them again with `MAKEARRAY`. Excel calls this name when a formula calls `ACME.QR`, so the formula and its results stay the same in Excel, pictures too.

* A link that you change in Excel to one that the sheet does not hold gives `#N/A`. Open the file here again and save it, and the new result goes into the file.
* The lookup matches text in the same case only, as `compute` sees it.
* With the function registered, the function comes before the name. Without it, `@reductoai/xlsx` calls the name, as Excel does.

This was checked with Excel for Windows: Excel opened the saved file, showed the text, picture and spilled results, and kept them when it saved the file again. An empty value in spilled rows shows 0, here and in Excel.

> [!NOTE]
>
> Calls with a range or a picture argument do not go into the file. Excel calculates the file when it opens it, so such a call shows `#VALUE!` in Excel, not the result from here. Excel does not get a new result: the file keeps the last one that arrived here.
