Spreadsheet Macros

Macros let you write JavaScript that reads and writes the spreadsheets in your ELN. They run in a sandboxed environment with a hard 10-second timeout. The full mathjs library is available for numeric and statistical calculations.

For an overview of how to manage and run macros, see Spreadsheets.

Quick start

In the spreadsheet’s Macros dialog, click + Macro, give it a label, and paste:

const source = context.getSheet("Sheet1");
const target = context.getSheet("Plate-1");

const total = math.sum(
    source.range("A1:A10").read().flat().filter(v => typeof v === "number"),
);
target.cell("A1").set(total);
target.cell("A1").setStyle({ background: "#ffd" });

Click Run. The target sheet’s A1 cell is set to the sum of the first ten cells of Sheet1’s column A, with a yellow background. The result row below the editor shows how many cells were written.

Two globals: `context` and `math`

Inside every macro, two globals are in scope:

context — the spreadsheet API. Lets you read and write sheets, cells, and ranges across every spreadsheet section in the ELN.
math — the full mathjs library. All numeric, statistical, matrix, and unit operations work normally. A small set of code-execution functions are disabled for safety (evaluate, import, simplify, derivative, resolve, createUnit, reviver).

A macro takes effect entirely through the writes it queues (cell.set, range.set, setStyle, …). There is no output value to return — write your results into a sheet instead.

`context` — workbook access

`context.activeSheet()`

Returns the worksheet currently shown in the spreadsheet.

const sheet = context.activeSheet();
sheet.cell("A1").set("Hello");

`context.getSheet(name)`

Returns the worksheet with the given name. Throws if no sheet matches — a typo surfaces immediately rather than silently doing nothing.

const plate = context.getSheet("Plate-1");
plate.cell("B2").set(42);

If two sections in the same ELN have a worksheet with the same name, getSheet returns the active section’s match first. Use getSheets() and filter manually for explicit disambiguation.

`context.getSheets()`

Returns every worksheet in every spreadsheet section in the current ELN. Useful for “do something to every sheet” macros.

for (const sheet of context.getSheets()) {
    sheet.cell("Z1").set(new Date().toISOString());
}

`Sheet` — a worksheet handle

Member	Description
`sheet.name`	The worksheet’s display name.
`sheet.rows`	The current snapshot’s 2D row array. Includes any writes the macro has queued so far.
`sheet.cell(addr)`	Get a `Cell` handle. `addr` is `"A1"` style or `{ x, y }` 0-indexed coords.
`sheet.range(addr)`	Get a `Range` handle. `addr` is `"A1:B10"` style.

const sheet = context.getSheet("Sheet1");
sheet.cell("A1").set(1);
sheet.cell({ x: 0, y: 1 }).set(2);
sheet.range("A1:B2").read();    // [[1, null], [2, null]]

`Cell` — a single-cell handle

Member	Description
`cell.address`	The A1 address, e.g. `"B5"`.
`cell.x`, `cell.y`	0-indexed column / row.
`cell.read()`	Returns the cell’s current value. Sees writes the macro has already queued — `cell.set(42); cell.read()` returns `42`.
`cell.set(value)`	Queues a write. Strings starting with `=` are treated as formulas.
`cell.setBlock(values)`	Anchors a 2D array at this cell and queues writes for every element. Dimensions are inferred — handy when you don’t know how many rows a CSV will have. Returns the resulting `Range`.
`cell.setStyle(style)`	Queues a style write. `style` is a CSS-property map, e.g. `{ background: "#ffd", fontWeight: "bold" }`.
`cell.setMeta(meta)`	Queues a per-cell metadata write — an arbitrary `{ key: value }` payload persisted with the workbook.

const c = context.activeSheet().cell("D7");
c.set("=SUM(A7:C7)");
c.setStyle({ background: "#dfd", color: "#060" });

`Range` — a rectangular block

Member	Description
`range.address`	The A1:B10 form.
`range.start`, `range.end`	`{ x, y }` 0-indexed corners.
`range.width`, `range.height`	Dimensions in cells.
`range.read()`	2D array of values. Sees queued writes. Cells that don’t exist yet read as `null`.
`range.set(values)`	Queues writes for a 2D array. Dimensions must match the range exactly.
`range.setStyle(style)`	Applies the same style to every cell in the range.
`range.cells()`	Iterator yielding a `Cell` handle for every coordinate in row-major order.

const sheet = context.activeSheet();

// Bulk write a 3×2 block
sheet.range("A1:B3").set([
    ["a", 1],
    ["b", 2],
    ["c", 3],
]);

// Apply a header style across a row
sheet.range("A1:Z1").setStyle({ fontWeight: "bold", background: "#eee" });

Cross-section reads and writes

A macro can read and write any spreadsheet section in the current ELN — not just the one it lives in.

Reads happen against a snapshot taken when the run starts. They’re always fast and synchronous.
Writes to the active section flow through the live spreadsheet immediately. The section auto-saves as usual.
Writes to other sections update that section’s saved data directly and trigger a save. The other section won’t visually refresh until the user reloads the ELN, but the data is persisted.

const plate = context.getSheet("Plate-1");   // in another section
const summary = context.getSheet("Summary"); // in this section

const od = plate.range("B2:B11").read().flat().filter(v => typeof v === "number");
summary.cell("A1").set("Mean OD");
summary.cell("B1").set(math.mean(od));

There is no conflict detection: if another user is editing a different section at the same time, the macro’s write wins on save.

Interactive UI — alerts, confirms, prompts, file uploads

Macros can pause mid-run to ask the user for input. The 10-second timeout is suspended while a dialog is open.

Method	Description
`await context.alert(message)`	Show an OK-only message dialog.
`await context.confirm(message, opts?)`	Yes/No dialog. Returns `true`/`false`. Cancel counts as `false`. `opts.yes` / `opts.no` rename the buttons.
`await context.prompt(message, defaultValue?)`	Single-line text input. Returns the entered string, or `null` if cancelled.
`await context.openFile(opts?)`	File picker. Returns a parsed file object, or `null` if cancelled. See below.

Every dialog title is prefixed with Macro "<your label>" so users can see that it is macro-driven.

`context.openFile`

await context.openFile({
    accept?: string[];                               // e.g. [".csv", ".xlsx"]
    as?: "text" | "csv" | "json" | "xlsx" | "blob"; // default "text"
}): IUploadedFile | null

The returned object contains the parsed file data:

Property	Set when
`file.name`, `file.type`, `file.size`	Always
`file.text`	`as = "text"`
`file.json`	`as = "json"`
`file.rows`	`as = "csv"` — 2D array of rows
`file.sheets`	`as = "xlsx"` — array of `{ name, rows }`, one per sheet
`file.buffer`	`as = "blob"`

XLSX note: file.rows is always empty for XLSX files. Use file.sheets and pick the sheet by name:

const file = await context.openFile({ accept: [".xlsx"], as: "xlsx" });
if (file) {
    const sheet = file.sheets.find(s => s.name === "Results") ?? file.sheets[0];
    context.activeSheet().cell("A1").setBlock(sheet.rows);
}

Examples

// Confirm before a destructive write
if (await context.confirm("Wipe column A?", { yes: "Wipe", no: "Keep" })) {
    context.activeSheet().range("A1:A1000").set(Array(1000).fill([null]));
}

// Prompt for a numeric threshold
const raw = await context.prompt("OD threshold (e.g. 0.5):", "0.5");
if (raw !== null) {
    const threshold = parseFloat(raw);
    // ... use threshold to filter, format, etc.
}

// Import a CSV
const file = await context.openFile({ accept: [".csv"], as: "csv" });
if (file) {
    context.activeSheet().cell("A1").setBlock(file.rows);
    await context.alert(`Imported ${file.rows.length} rows from ${file.name}.`);
}

`math` — mathjs

The full mathjs function set is available except for these blocked functions: evaluate, simplify, derivative, resolve, import, createUnit, reviver. Calling any of them throws an error.

math.sum([1, 2, 3]);                     // 6
math.std([2, 4, 4, 4, 5, 5, 7, 9]);      // 2
math.mean([1, 2, 3, 4]);                 // 2.5
math.unit("5 km").to("m");               // 5000 m
math.format(math.pi, { precision: 4 }); // "3.142"

Common patterns

Sum a column

const sheet = context.activeSheet();
const values = sheet.range("A2:A100").read().flat().filter(v => typeof v === "number");
sheet.cell("A1").set(math.sum(values));

Set a formula

context.activeSheet().cell("D2").set("=SUM(A2:C2)");

Conditional formatting

const sheet = context.getSheet("QC");
const data = sheet.range("B2:B100").read();

for (let i = 0; i < data.length; i++) {
    const v = data[i][0];
    if (typeof v !== "number") continue;
    const color = v < 0.5 ? "#fdd" : v > 1.5 ? "#dfd" : "#fff";
    sheet.cell({ x: 1, y: i + 1 }).setStyle({ background: color });
}

Copy a row across sheets

const source = context.getSheet("Master");
const target = context.getSheet("Today");
target.range("A1:Z1").set(source.range("A1:Z1").read());

Inspect all sheets in the ELN

const rows = [["Sheet", "Rows"]];
for (const s of context.getSheets()) {
    rows.push([s.name, s.rows.length]);
}
context.activeSheet().cell("A1").setBlock(rows);

Debugging

When a macro doesn’t do what you expect, it helps to pause and look at a value mid-run.

`debugger`

Add a debugger; statement, open your browser’s developer tools (F12), then click Run. Execution pauses on that line so you can inspect variables and step through the code.

const values = context.activeSheet().range("A2:A100").read().flat();
debugger; // pauses here while dev tools are open
const numbers = values.filter(v => typeof v === "number");
context.activeSheet().cell("A1").set(math.sum(numbers));

With dev tools closed, debugger does nothing and the macro runs straight through. The 10-second timeout keeps counting while you are paused, so use it for a quick look — pausing too long terminates the run.

`context.alert`

To check a value without opening dev tools, show it in a dialog. The timeout is suspended while the dialog is open:

const values = context.activeSheet().range("A2:A100").read().flat();
await context.alert(`Read ${values.length} cells: ${JSON.stringify(values.slice(0, 10))}`);

Sandbox

Macros run in an isolated Web Worker.

No DOM or network access. document, fetch, XMLHttpRequest, WebSocket, and WebAssembly are not available. Trying to use them throws an error.
No persistent state. Each run gets a fresh environment — nothing carries over between runs.
Hard 10-second timeout. Runaway macros (infinite loops, slow computation) are forcibly terminated. The app never freezes.
Op cap. A macro that generates more than 1,000,000 cell writes is aborted with a clear error.

Errors

Error message	Cause
`Worksheet "Foo" not found.`	`getSheet("Foo")` found no match. Check spelling; use `getSheets()` to list every available sheet.
`No active worksheet available.`	`activeSheet()` was called before the spreadsheet finished loading. Rare.
`Range A1:B2 expects 2 rows, got 1.`	The 2D array passed to `range.set()` doesn’t match the range dimensions.
`Function evaluate is disabled`	One of the blocked mathjs functions was called. Use direct functions instead (`math.sum`, `math.mean`, etc.).
`Macro exceeded 10s and was terminated.`	Hit the hard timeout. Reduce work or split into multiple macros.
`Macro produced too many operations (>1000000).`	Hit the op cap. You are probably looping over more cells than intended.
`fetch is not a function`	Network access is not available in macros.
`A macro is already running.`	Another macro is still executing. Wait for it to finish before starting a new one.

Spreadsheet Macros

Quick start

Two globals: context and math

context — workbook access

context.activeSheet()

context.getSheet(name)

context.getSheets()

Sheet — a worksheet handle

Cell — a single-cell handle

Range — a rectangular block