Worksheet Functions

A workbook can carry functions of its own, written in Lua, C# or as Divooka graphs, and formulas call them like any built-in function: =LUA.TAX(B2), =CS.SUMSQ(A1:A10), =DVK.LOANPAYMENT(B1, B2, B3). They are Functions: they compute a value from their arguments, cannot change the workbook, and are recalculated when the cells they read change.

Declaring functions

Write them in a script named functions.lua or functions.csx (or anything.functions.lua, anything.functions.csx) in the Scripts pane, and save it. Every top-level function whose name is all capitals becomes a worksheet function; other functions are helpers the sheet cannot see.

--- Twice its argument.
---@param x number
function DOUBLE(x) return x * 2 end

--- Tax on an amount, at 20% unless a rate is given.
---@param rate? number
function TAX(amount, rate) return amount * (rate or 0.2) end

/// <summary>Tax on an amount.</summary>
double TAX(double amount, double rate = 0.2) => amount * rate;
double SUMSQ(double[] values) => values.Sum(v => v * v);
int COUNT(params object[] items) => items.Length;

Lua functions are called as LUA.NAME and C# functions as CS.NAME, so these are =LUA.DOUBLE(A1), =LUA.TAX(B2, 0.15), =CS.SUMSQ(A1:A10) and =CS.COUNT(A1, B1, C1). They appear in Insert Function with their arguments and description.

Descriptions and arguments. In Lua, a --- line above the function is its description; ---@param name type gives an argument a type (number, string, boolean), name? makes it optional, and ... takes any number of arguments. In C#, the /// summary is the description, the parameter types convert the arguments, a default value makes one optional, and params takes any number.
Ranges. An argument declared as a table in Lua (---@param values table) or as an array in C# (double[], object[,]) receives the whole range, as rows of values. Any other argument given a range is applied to each cell, and the results spill.
Results. A number, text or a logical value; returning a table of rows (Lua) or a two-dimensional array (C#) spills it.

Saving a functions script reloads it and recalculates every cell that calls its functions.

Divooka graphs

Every Function graph named in capitals in a workbook's graph documents is a worksheet function called as =DVK.NAME(...); see Divooka graphs.

Parameters and fetched data

A function reads the document's parameters as param.Name in Lua and Param["Name"] in C#, and a formula reads one directly as =PARAM("Name"); changing a parameter recalculates exactly the cells that read it.

A function that fetches from the network or reads a file through the host (host.get and its siblings) is a data-source Function: it shows #N/A until Data ▸ Refresh Data fetches, and then answers what the last refresh fetched, kept in the workbook, until the next refresh. The first refresh asks you to Allow what it fetches.

Limits

A Lua function call that computes for more than 2 seconds (time spent waiting for the host to fetch does not count) is stopped and answers an error. A function cannot read the workbook except through its arguments and the document's parameters, and has no app, doc or os.
A function's result for the same arguments is reused rather than computed again.
Named LAMBDAs do not appear in Insert Function yet.

Enabling

The functions are stored in the workbook, so they go wherever it goes. They do not run until the workbook is enabled: until then, cells that call them show #NAME?, and the bar over the grid reads Scripts disabled with an Enable button. See Trust. Once enabled, the bar shows why the active cell's function failed, if it did, with the script's file and line.

In Excel

Excel keeps the functions' scripts in the file and keeps the formulas as written, but cannot run them: the cells show #NAME? in Excel.