Python scripting

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

Automate your spreadsheet with Python. Spredin runs real CPython inside the app (via Pyodide/WebAssembly), so scripts can read and write the active sheet, transform data, and generate values — the modern equivalent of Excel VBA. A script works on your workbook; to read any other file it calls open_file(), and Spredin asks you first.

This guide is also the reference for the scripting API. It is written to be readable by people and usable as context for the AI model that generates Spredin scripts — the model is given this same text.

1. Opening the panel#

Choose Tools → Show Python Panel (or ⌘K → "Show 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.

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.

Libraries: the app carries numpy, pandas and scipy and Python's standard library, and downloads nothing. Other libraries (scikit-learn, statsmodels, seaborn…) are not available, and pip / micropip cannot add them, nor can Pyodide's own loadPackage. There is no real matplotlib: import matplotlib.pyplot as plt is a stand-in. plt.plot, bar, barh, scatter, pie and hist, the axes from plt.subplots(), and df.plot() (kind='hist' too) copy their data to a sheet named Plot and make a Spredin chart of it there; a histogram writes its bins (10 unless bins= says otherwise) with their counts and draws them as bars. The first plot of a run empties Plot, so a script run again shows its plots again; a plot never passed to plt.show() is drawn when the run ends. For a chart beside your own table, use sheet.chart(...).

Every run starts fresh. Nothing an earlier run defined is there: its variables, its functions and its imports are gone, in this workbook and in any other. A script has to import and define everything it uses, so it runs the same for whoever opens the workbook next. Libraries that were already loaded stay loaded, so importing pandas a second time takes no time.

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 set of methods the AI uses when it writes a script for you (Ask in plain English in the Python panel). The complete reference is the API reference (Help → Agent / API Reference); every method in it exists in the app.

Sheets#

Copy a sheet:

dst = sheet.copy("Copy")           # formulas, formats, widths, merges, rules

sheet.copy_to(dst, "A1:E20", at="B3") copies a range as copy and paste does (add values=True for what the formulas show). To move data into a new table, values only, write a DataFrame: sheet.add_sheet("Data").write(sheet.read()).

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 (matplotlib is only a stand-in, above) — write your computed data to cells, then sheet.chart(range, …). Writing None, NaN or "" clears a cell.

Undo. A whole script run is one undo step — press ⌘Z to revert everything a script did (even a run that errored partway): the cells and formats it wrote, the sheets it made or emptied, the rows it inserted, and its frozen panes, rules, filter, charts, names and refreshed pivots. Redo (⇧⌘Z) puts all of it back.

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

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)) and not isinstance(v, bool):   # a typed TRUE reads as True
        total += v
print("Total:", total)
sheet.set(sheet.rows, 1, total)   # write below the last row

Add a computed column (write formulas)#

# Revenue = Units * Price in column D, as live formulas: write the first
# one, then fill it down the table (the references move row by row).
sheet.set_cell("D1", "Revenue")
sheet.set_cell("D2", "=B2*C2")
sheet.fill_down(f"D2:D{sheet.rows}")

To write a formula only on some rows, loop and write each one:

for r in range(1, sheet.rows):     # skip the header row
    units = sheet.value(r, 1)
    if isinstance(units, (int, float)) and not isinstance(units, bool):
        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)) and not isinstance(score, bool):
        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 (over 330 of them); anything else evaluates to #NAME?. A formula that reads an empty cell gives 0, as in Excel; =IF(A2="","",A2) keeps a blank.

# 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 Ask in plain English, so the scripts it writes use only 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 Formula functions in the API reference (Help → Agent / API Reference) for the family list.

Volatile functions. RAND, RANDBETWEEN and NOW, and every cell that reads them, are worked out again after every edit; TODAY is too once the date has changed. Data → Calculate Now (F9) works out every volatile cell again, TODAY included.

Several scripts: import one from another#

The panel's other scripts are modules: with helpers.py beside main.py, import helpers in main.py runs helpers.py and gives you its functions. A module reaches the workbook through sheet, as a script does; an edit to it is seen by the next run; an error inside it names it and its line, and clicking the line opens it. A script named like a Python module (math.py, random.py) is hidden by that module: rename it. The names Python can import are the ones a variable could have (fit_model.py, not fit model.py).

# physics.py
def range_m(v, angle_deg, g=9.81):
    import math
    return v * v * math.sin(2 * math.radians(angle_deg)) / g

# main.py
import physics
sheet.set_cell('B6', physics.range_m(20, 45))

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.
• Files: a script reads another workbook only through open_file(), and Spredin asks you first, showing the path. The one file a script can write is a PDF (export_pdf), again only where you choose or allow. Bring data in with File → Open… or open_file, and hand results out with the File menu's exports or to_csv / to_json.
• Network: the sheet API itself never uses the network. A script is still full Python, though, and can reach the internet (through the js module, for example) — so run scripts only from people you trust. When Ask in plain English writes a script that reaches the network or your files (opening a file, or writing a PDF), it puts it in the editor for you to read and run yourself (see below).
• A script that runs too long is stopped. The limit is 60 seconds unless you change it in Settings → Script Time Limit…; 0 turns it off, and a runaway script can then only be stopped by quitting Spredin. The limit stops Python loops and time.sleep(…). One long call into a library (a big pandas or numpy operation) runs to its end before the limit is checked. A script the AI writes that sleeps is put in the editor for you to read first. Stop, shown while the model is writing a script, stops that request, not a script that is already running.
• Script files are saved in the workbook (a .sprd), in the order the panel's file list shows them. A file you left empty is saved too, as long as another file has code. The example a new panel starts with is not saved, and a workbook whose only script file is empty saves no scripts.
• For large rewrites, prefer building values in Python and writing once at the end.

5. AI-assisted generation#

The Python panel has a prompt box headed ASK IN PLAIN ENGLISH. Type what you want and press Gen — or ⌘↵ — and the configured model writes a Spredin script using only the sheet API above. The script already in the editor goes along with your request, so "now add a chart to that" edits it instead of starting over.

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.

Settings → AI Model… → Auto-fix rounds lets you pick the exact number, from 0 to 4. The switch then shows the nearest position: Low for 1 or 2, High for 3 or 4. A new install starts at 2 rounds, shown as Low.

Cost. Each retry is a complete extra generation, so High can cost up to four times as much as None (the first try plus three retries). On a metered API (OpenAI, Anthropic, any hosted endpoint) that is real money on every failed script. On a local server nothing is metered — retries cost time and battery, not money. When requests are billed, the switch's tooltip says so; it goes by the endpoint you set.

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 kept in your Mac's Keychain and sent only to the endpoint you configured. Before the first request to a hosted service, Spredin asks your permission: the question names the service and what is sent, and Not Now sends nothing. A server on this Mac is never asked about.

Thinking. Models that think before they answer can be asked to think less or more: Settings → AI Model… → Thinking. Automatic leaves the service at its own level; Low is faster and uses fewer tokens; High is more careful and slower. The row appears only for models whose service documents a level (Claude, OpenAI's reasoning models, Gemini 2.5 and later, DeepSeek, Kimi K3, GLM-5.2 and later, Groq's reasoning models and OpenRouter); any other is sent none. With Claude, the reference Ask AI sends with every request is cached for an hour, so asking again within it costs a tenth for that part. With OpenRouter there is no key to paste: Connect with OpenRouter… signs you in through your browser and Spredin gets its own key.

What the model is given. Every generation carries: the list of sheet methods, the API reference and this guide, the full list of formula functions and the number-format codes; 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. A large sheet is summarised, never sent whole.

Report… appears beside the box's heading once a script has been written. It opens Send Feedback with your request and the script filled in, for a script that is harmful, offensive or plainly wrong; you can edit or cut both before sending, and nothing goes until you send it.

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 (as many times as the Auto-fix setting above allows; more rounds give a weak model more chances, but make 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.

What is never run unseen. The model writes from your workbook's text, and text in a sheet someone sent you can try to steer it. So a generated script that reaches past the workbook — into the app itself, the network, files on your Mac (open_file, export_pdf), the printer, or code built while it runs (eval, __import__) — is put in the editor with a note saying what it reaches for, and is not run until you press Run. Scripts you run yourself are never held.

Scripts that came inside someone else's workbook ask once. A .sprd carries its scripts, so a file you were sent can carry code someone else wrote. Opening a workbook never runs anything. The first time you press Run on scripts this Mac has not seen before, Spredin says so and offers Don't Run (the default) or Run Script. A script can change your workbook and send what is in it over the internet, so run it only if you trust whoever sent the file. Scripts you wrote here, or allowed once, are remembered and never ask again; changing a remembered script's code makes it new again. Until then, a script Ask in plain English writes on such a workbook goes into the editor and is not run: press Run when you have read it, and Spredin asks the same question first.

Model quality matters. When the script's own # --- verify --- check fails, the panel says so and leaves the results on the sheet — the check can be the wrong part (a numpy type where int was asserted), so look at the sheet before believing either. 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, numpy, pandas and scipy.
• 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.
• sheet.write(df, cell) writes the column names at cell and the values from the row below. To fill a column under its existing header, write sheet.write(df[[col]], 'D2', header=False).
• 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.