Custom Functions

Write Lua functions, save them to a file, and call them in formulas like built-ins. Custom functions are sandboxed, deterministic, and verifiable.

=ACCRUED_INTEREST(B6, C6, D6)
=HAIRCUT(B6 + E6, F6)
=WEIGHTED_AVG(A1:A10, B1:B10)

Quick Start

1. Write your functions

Create ~/.config/visigrid/functions.lua:

function ACCRUED_INTEREST(principal, rate, days)
    return principal * rate * days / 365
end

function HAIRCUT(value, haircut_pct)
    return value * (1 - haircut_pct)
end

2. Launch VisiGrid

Custom functions load automatically on startup. The status bar shows the count:

Loaded 2 custom functions

3. Use them in formulas

Type =ACCRUED_INTEREST(1000000, 0.0425, 92) in any cell. It evaluates like a built-in.

4. Reload after edits

Edit functions.lua, then reload without restarting:

Command palette (Ctrl+Shift+P) → Reload Custom Functions

Function Rules

• Names must be uppercase: ACCRUED_INTEREST, MY_FUNC, TAX2024
• Pattern: ^[A-Z][A-Z0-9_]*$
• Built-ins always win: If your function collides with a built-in (e.g., SUM), the built-in is used and you get a warning on reload
• Return scalars: number, string, boolean, or nil (no tables)
• Nil returns empty: A function returning nil produces an empty cell

Arguments

Scalars

Cell references and literals are evaluated before your function sees them. Your function receives plain Lua values.

function DOUBLE(x)
    return x * 2
end
-- =DOUBLE(A1) passes the value of A1, not a reference

Ranges

Range arguments arrive as a special object with .n (count) and :get(i) (1-based access).

function WEIGHTED_AVG(values, weights)
    local sum, wsum = 0, 0
    for i = 1, values.n do
        local v = values:get(i)
        local w = weights:get(i)
        if v ~= nil and w ~= nil then
            sum = sum + v * w
            wsum = wsum + w
        end
    end
    if wsum == 0 then error("total weight is zero") end
    return sum / wsum
end
-- =WEIGHTED_AVG(A1:A10, B1:B10)

API	Returns
range.n	Total cell count
range:get(i)	Value at 1-based index (nil for empty cells)

Out-of-bounds access (:get(0) or :get(n+1)) raises an error, not a silent nil. This catches off-by-one bugs.

Error Handling

Error	Cause
#NAME?	Function not found in functions.lua
#LUA!	Runtime error, sandbox violation, or resource limit

Errors from arguments propagate automatically. If any argument contains #DIV/0! or another error, that error propagates without calling your function.

Use error() for intentional errors:

function POSITIVE_ONLY(x)
    if x == nil then error("requires 1 argument") end
    if x < 0 then error("value must be positive") end
    return x
end
-- =POSITIVE_ONLY(-5) → #LUA! value must be positive

Sandbox

Custom functions run in a locked-down environment. No side effects, no state mutation.

Available: math.* (except random/randomseed), string.*, table.*, type, tonumber, tostring, pairs, ipairs, select, error, pcall

Blocked: os, io, debug, require, load, loadfile, dofile, math.random

Global mutation blocked: Attempting to set a global variable raises #LUA!:

-- This will error, not silently succeed
counter = 0
function COUNT_CALLS()
    counter = counter + 1  -- #LUA! Global state mutation is not allowed
    return counter
end

Resource Limits

Limit	Value
Instructions	10,000,000 per call
Wall-clock	1 second per call

These are per function call, not per recalc. A function that exceeds either limit returns #LUA!.

Verification

Custom functions are deterministic: same inputs always produce the same output. Combined with VisiGrid’s semantic fingerprinting, this means you can prove that a spreadsheet hasn’t been tampered with.

The Verification Flow

# 1. Build a sheet with custom function formulas
vgrid sheet apply portfolio.sheet --lua bond_portfolio.lua --stamp

# 2. Fingerprint it
vgrid sheet fingerprint portfolio.sheet
# → v1:24:a1b2c3d4e5f6g7h8

# 3. Change an input → fingerprint changes
vgrid sheet apply portfolio.sheet --lua <(echo 'set("B6", 2000000)')
vgrid sheet fingerprint portfolio.sheet
# → v1:24:x9y8z7w6v5u4t3s2  (different)

# 4. Revert → fingerprint matches
vgrid sheet apply portfolio.sheet --lua <(echo 'set("B6", 1000000)')
vgrid sheet fingerprint portfolio.sheet
# → v1:24:a1b2c3d4e5f6g7h8  (same as step 2)

# 5. Verify
vgrid sheet verify portfolio.sheet --fingerprint v1:24:a1b2c3d4e5f6g7h8
# → Verified

The fingerprint covers inputs and formulas (including =ACCRUED_INTEREST(...)), not styling. Same script + same engine = same fingerprint. Always.

Why This Matters

Excel and Google Sheets cannot do this. Their computation is opaque — you trust the output because you trust the tool. VisiGrid’s computation is verifiable — you trust the output because you can prove it.

Memoization

Custom functions are automatically memoized within each recalc cycle. If =ACCRUED_INTEREST(B6, C6, D6) appears in multiple cells with the same arguments, Lua is called once. Range arguments are fingerprinted using blake3 for efficient cache keys.

Example: Bond Portfolio

See examples/custom-functions/ in the VisiGrid repository for a complete worked example:

• functions.lua — ACCRUED_INTEREST, HAIRCUT, WEIGHTED_AVG
• bond_portfolio.lua — Provenance script building a 3-bond portfolio
• demo.sh — Full verification flow (build → fingerprint → change → verify)