Python scripting

This is the same document you'll find inside Spredin under Help.

Automate your spreadsheet with Python. Spredin runs real CPython in the app (via Pyodide/WebAssembly), sandboxed, so scripts can read and write the active sheet, transform data, and generate values — the modern equivalent of Excel VBA.

This guide is also the canonical reference for the scripting API. It is written to be readable by people and usable as context for an LLM that generates Spredin scripts. When the API changes, update this file in the same change.

1. Opening the panel#

Click Python in the top toolbar (or ⌘K → "Toggle Python panel"). The panel has a code editor (with syntax highlighting), a ▶ Run button, and an output pane that shows everything you print(). The script runs against the active sheet; whatever it writes appears in the grid immediately.

The first run starts the bundled Python runtime and takes a few seconds; later runs are instant. Nothing is downloaded and no internet connection is needed — the runtime ships inside the app (see docs/PYTHON_RUNTIME.md).

2. The model: pandas in, spreadsheet out#

Think of it as plain Python + pandas. The global sheet is your active sheet: read it into a DataFrame, compute with pandas/numpy/scipy as usual, then write results back and present them (charts, styles). The spreadsheet is the data source and the canvas — everything in between is normal Python.

df = sheet.read()                 # a pandas DataFrame (row 1 → column names)
top = df.sort_values(df.columns[1], ascending=False).head(5)
sheet.write(top, "H1")            # write the DataFrame back, starting at H1
sheet.chart("H1:I6", "bar", "Top 5")
print(df.shape, "rows × cols")

Imports: numpy, pandas, scipy are installed, but not imported for you — you must write import numpy as np / import pandas as pd / from scipy import stats yourself. (Using pd without importing it is a NameError; the app binds them privately, not into your scope.) You do not import anything to get sheet; it's already defined. Write top-level code (no def main(), no if __name__), and never reassign sheet.

Data types#

Read / write (pandas-first)#

Cells (fine-grained, 0-indexed; A1 = 0, 0)#

Spredin operations (call the app engine — no pure-Python equivalent)#

Structure, filtering & data tools (whole rows/cols are 0-indexed; formulas rebase):

This is the same surface an LLM sees. The complete, always-current reference is Scripting API reference (Help → API reference), kept in lockstep with the runtime by a test — nothing here is a method the app doesn't actually have.

Sheets#

Copy data into a new sheet:

dst = sheet.add_sheet("Copy")
dst.write(sheet.read())            # DataFrame round-trips (headers + rows)

Cross-sheet — combine data from several sheets. Every handle from book() / add_sheet() is a full Sheet with the same methods, each acting on its own sheet, so you can read from many and write to another:

import pandas as pd
a = sheet.book("Sales").read()        # DataFrame from the "Sales" sheet
b = sheet.book("Targets").read()      # DataFrame from the "Targets" sheet
merged = a.merge(b, on="Region")      # join in pandas
merged["Gap"] = merged["Actual"] - merged["Target"]
out = sheet.add_sheet("Combined")
out.write(merged)                     # write the result to a new sheet
out.chart("A1:D10", "bar", "Actual vs Target")

Formulas can reference other sheets directly too: sheet.set("B2", "=Sales!B2-Targets!B2").

Charts draw with the same ECharts engine as the UI (no matplotlib) — write your computed data to cells, then sheet.chart(range, …). Writing None/"" clears a cell.

Undo. A whole script run is one undo step — press ⌘Z to revert everything a script wrote (even a run that errored partway).

Saved with the workbook. Your script is stored in the .sprd file, so it travels with the document.

The Examples… dropdown in the panel inserts ready-to-run scripts (linear regression, moving-average time series, descriptive stats) that seed their own data and chart the result.

3. Examples#

Sum a column and write the total#

total = 0
for r in range(sheet.rows):
    v = sheet.value(r, 1)          # column B
    if isinstance(v, (int, float)):
        total += v
print("Total:", total)
sheet.set(sheet.rows, 1, total)   # write below the last row

Add a computed column (write formulas)#

# For each data row, put Revenue = Units * Price in column D.
for r in range(1, sheet.rows):     # skip the header row
    units = sheet.value(r, 1)
    if isinstance(units, (int, float)):
        # Write an Excel formula so it stays live:
        sheet.set(r, 3, f"=B{r+1}*C{r+1}")

Transform text (clean up a column)#

for r in range(sheet.rows):
    name = sheet.get(r, 0)         # column A
    sheet.set(r, 0, name.strip().title())

Generate data#

import math
sheet.set_cell("A1", "x")
sheet.set_cell("B1", "sin(x)")
for i in range(20):
    x = i * 0.5
    sheet.set(i + 1, 0, round(x, 2))
    sheet.set(i + 1, 1, round(math.sin(x), 4))

Conditional flagging#

for r in range(1, sheet.rows):
    score = sheet.value(r, 2)      # column C
    if isinstance(score, (int, float)):
        sheet.set(r, 3, "PASS" if score >= 60 else "FAIL")

Read a block into a list of dicts (finance-style)#

headers = [sheet.get(0, c) for c in range(sheet.cols)]
records = []
for r in range(1, sheet.rows):
    row = {headers[c]: sheet.value(r, c) for c in range(sheet.cols)}
    records.append(row)
print(records[:3])

Writing live formulas#

sheet.set(r, c, '=…') stores a formula, not a value — the engine recalculates it like Excel. Only the functions the engine implements work (~236 of them); anything else evaluates to #NAME?.

# Live column: Revenue = Units * Price, recalculates when either changes.
for r in range(1, sheet.rows):
    sheet.set(r, 3, '=B%d*C%d' % (r + 1, r + 1))

sheet.set('E1', '=SUM(D2:D100)')                     # aggregate
sheet.set('F2', '=XLOOKUP(A2, Prices!A:A, Prices!B:B)')  # cross-sheet lookup
sheet.set('G2', '=IFERROR(E2/D2, 0)')                # guard a division

To browse the functions yourself, type = in a cell — autocomplete lists every one with its signature. The same catalogue is generated straight from the engine and given to the AI, so the assistant only writes functions that really exist.

Families: math/trig, stats, lookup (XLOOKUP/VLOOKUP/INDEX/MATCH), logical, text, date/time, finance (XIRR/PMT/IPMT), engineering and info functions. See Scripting API reference for the family list and the volatile-function caveat (RAND/NOW cache until re-edited).

4. Tips & limits#

• The active sheet is sheet; reach others by name with sheet.book("Name") or sheet.add_sheet("New").
• Writes trigger a recalc + redraw automatically when the script finishes.
• Errors print a Python traceback to the output pane — nothing is written if the script raises before the relevant set.
• No file/network access from scripts (sandboxed). Bring data in via the spreadsheet (Open / Import) and read it with the API.
• For large rewrites, prefer building values in Python and writing once at the end.

5. AI-assisted generation (local LLM)#

The Python panel has an AI prompt box at the top (marked with the robot glyph). Type what you want in plain English and press Gen — or ⌘/Ctrl+Enter — and the configured model writes a Spredin script using only the sheet API above. Leave the box empty and Gen works from your current script plus the sheet context, which is how you say "now add a chart to that".

Auto-fix level#

Above Gen / Run is a three-position switch: None / Low / High. It is how many times a failed script is handed back to the model with the error, and it is the SAME setting as Settings → AI model… → Auto-fix rounds — moving either moves the other.

Cost. Each retry is a complete extra generation, so High costs roughly 3× the tokens of None. On a metered API (OpenAI, Anthropic, any hosted endpoint) that is real money on every failed script. On local Ollama nothing is metered — retries cost time and battery, not money. The switch's tooltip says which case you are in, because it reads your configured provider.

Some failures are comprehension failures rather than crashes, and asking again does not fix comprehension. Past Low you are buying very little — the leverage is in choosing a better model, not in more retries.

Default backend: a local server. No cloud and no key: Ollama running at localhost:11434, with a model pulled. Spredin ships no model of its own — if none is there, Gen tells you the exact command to run and lists what you do have.

Settings → AI model… switches between local servers and hosted services with one click, and Help → Choosing an AI model walks through both. Bigger models produce noticeably fewer wrong answers; that is where the leverage is. Your API key is stored only on this device and sent only to the endpoint you configured.

What the model is given. Every generation carries: the API contract, the sliced API.md + this guide, the full formula-function catalogue and the number-format codes (all generated from the engine, so they can't drift); a workbook summary — the sheet's data blocks, found by scanning for islands separated by blank rows/columns, each with its real A1 range, whether it has a header, and a sample row (so a title at A1, a table at A3:D6 and a summary panel at G3:H4 are described as three things, not flattened into one imaginary table); a document-state summary — the live formulas in use (one representative per column), the number format per column sampled from each block's first data row, frozen panes and named ranges, none of which values alone can convey; and whatever is already in your editor, so "now add a chart" edits your script instead of replacing it. Big inputs are bounded, not dumped: a 1M-row .sprd would bury the reference.

The agentic loop (generate → run → verify → repair). Generate doesn't stop at code: the model is instructed to append a # --- verify --- section of asserts that re-read the sheet and prove the task was done (expected values computed independently from the inputs). The script runs immediately; a failed assert — or any crash — raises, and the app hands the model the exact traceback (including the API guard's "no such method, use one of: …" message), reverts the failed attempt's writes, and re-runs the corrected code (1–3 attempts, Settings → AI model… → Auto-fix rounds; more rounds gives a weak model more chances, but makes a hopeless task slower to fail). This catches the "runs but computes the wrong thing" class, not just crashes. The model sees a workbook-level summary (the active sheet's headers + sample rows, plus every other sheet's name/shape/headers), so cross-sheet requests work. Each run is one undo step — ⌘Z reverts what the script wrote.

Model quality matters. The repair loop fixes crashes, not wrong answers — a small local model can produce code that runs but computes the wrong thing (you will see it instantly in the grid; ⌘Z and refine the prompt). Larger models make far fewer of both error kinds; the default stays local so nothing leaves your machine without a key.

How the defaults were chosen. Not from a vendor benchmark: by running a library of everyday spreadsheet requests through the app itself and grading the workbook each model produced. The honest test of a model is the thing you actually do — try it on one of your own tasks and look at the grid.

What to expect from a model#

Model names and their quality change every few months, so this page does not name any — the honest guidance is about the shape of the results, which has been stable.

A capable model is competent at the domain. It reaches for the right functions, stays inside the documented sheet API, and writes formula scripts that work first time. Its mistakes are ordinary programming ones: off-by-one around a header row, running past the end of the used range, forgetting that a cell's contents are text until you convert them.

A model too small for the job fails differently. It does not make fixable slips; it misunderstands the request. Retrying does not help, and neither does rewording. If Gen keeps producing something that runs but answers the wrong question, the fix is a larger model, not a better prompt.

Judge the result, not the confidence. Generated code that runs cleanly can still be wrong — a total over the wrong column, a rolling window centred instead of trailing. Spot-check the numbers against something you already know before you rely on them. The Python panel's output pane and one ⌘Z are there for exactly that.

Practical settings. Auto-fix Low is the good default (above). If you are paying per request, prefer a stronger model with fewer retries over a weak one with more — it is usually both cheaper and better.

6. Notes for LLM-assisted code generation#

When generating a Spredin script from a natural-language request:

• Use only the sheet API above plus the Python standard library / Pyodide.
• Remember coordinates are 0-indexed; the user usually thinks in A1 — convert (A1 → (0,0)), or use sheet.cell / sheet.set_cell with A1 strings.
• Use sheet.value(...) for math, sheet.get(...) for raw/formula text.
• To keep results live, write formulas ("=B2*C2") rather than computed numbers when the user wants them to update with the data.
• Always print(...) a short summary so the user sees what happened.

Keep this file in sync with src/script/python.ts (the API preamble) and src/script/bridge.ts (the implementation).