Scripting API reference

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

The single, stable contract for driving Spredin by code instead of by clicking the UI. An LLM (local or cloud) reads this as context and writes Python against the global sheet; the spreadsheet is both the data source and the canvas.

Ask in plain English, the Python panel's AI, works from this same reference, and every method listed here exists in the app.

Model#

Think in normal Python + pandas. numpy / pandas / scipy are installed but not imported for you — write import pandas as pd yourself (using pd without it is a NameError). Every run starts fresh: nothing an earlier run defined or imported is there, so a script imports and defines all it uses. sheet is the active sheet — already defined; never reassign it. Write top-level code (no def main(), no if __name__).

Row numbering — three systems, don't mix them#

This is the single most common way a script goes wrong (an IndexError, or a formula written into the header row):

for r in range(1, sheet.rows):            # data rows, 0-indexed
    sheet.set(r, 3, '=B%d+C%d' % (r+1, r+1))   # A1 rows are r+1
    same_row_in_df = df.iloc[r - 1]        # df has no header row

Never index a DataFrame with a sheet row number — that is off by one and runs off the end.

What you may pass#

• A row or a column is a whole number from 0. A numpy integer (np.where(...)[0], np.argmax(...)) works. A float, text or a negative number raises ValueError and names the argument; there is no counting back from the end, and a row or column past the last one a sheet has (row 4,194,304, column 1,048,576) raises too.

• A cell or a range is on the handle's own sheet. Its sheet's name in front ('Sales!B2:B9', "'Q3 data'!A1") is that range when it names this sheet; a range on another sheet raises and says what to write instead: sheet.book('Other').merge('A1:B4'). read, read_values, write, to_csv, to_json, remove_duplicates, pivot, fill_down and copy_to take a range on any sheet.

• One cell takes one value: a number, text, a date, a time or TRUE/FALSE (numpy and pandas scalars, Decimal and Fraction included). Anything else raises and says so (convert it first: str(v)); a heading or index label of another kind (a pd.cut interval) is written as its text. sheet.write takes many: a list is a row, a list of lists a block, and a numpy array, a pandas Index, a range or a generator is written the same way. A dict or a set raises (write pd.DataFrame(d) or sorted(s)), and so does a list given to sheet.set.

• A Python int is a number however long (10**16, a pandas int64), kept to 15 digits as Excel keeps one. Text of digits longer than 15 stays text (an ID), and as_text=True keeps every digit of a number.

• No value is an empty cell. None, NaN, pd.NA and NaT all write an empty cell, and an empty cell reads back as None. Infinity raises: replace it first (df.replace([np.inf, -np.inf], np.nan)).

• True and False are written as TRUE and FALSE, as you would type them, and read back as booleans.

• An error from a sheet call is a ValueError, with the reason as its message: a range it cannot read, a sheet that is not there, a rule it could not run. try: ... except ValueError as e: print(e) catches it.

• Text that looks like something else. A cell's type follows what is written into it: 345 and '345' are numbers, '=A1+1' is a formula, 'TRUE' a logical value, an ISO date a date. '007' stays text (leading zeros are kept), and so does text of more than 15 digits, such as an account number; a Python number that long is written as a number. That is less than typing does, on purpose: a script holds real values, so text such as '1,200', '$5', '50%', '(300)' or '1/2/2024' is written as that text (it is not guessed at, and SUM does not count it). Convert it in Python first (float('1,200'.replace(',', ''))), or hand CSV text to paste_csv, which reads fields the way typing them would. To write text on purpose, pass as_text=True to set, set_cell, write, write_col or write_row:

  sheet.write_col('A', ['007', '012', '345', '0890'], 1, as_text=True)   # IDs, all text
  sheet.set('B1', '=not a formula', as_text=True)
  sheet.write(df, 'D1', as_text=True)        # every value of the table as text
  

  It is the apostrophe you would type in front of the value ('345): the cell keeps it, the grid does not show it, and the value reads back as the text 345 (TRUE as the text 'TRUE', #N/A as the text, not the error).

Writing a DataFrame#

sheet.write(df, 'A1') writes the column names in row 1 and the values from row 2. That first row is the thing to remember:

sheet.write(df[['Country']], 'D1')                 # names in D1, values from D2
sheet.write(df[['Country']], 'D2', header=False)   # values only, under a header that is already there

sheet.write(df[['Country']], 'D2') would write Country a second time in D2 and move every value one row down. When column names land under a row that already holds them, the run prints a note saying so.

Two levels of column names (agg(['sum', 'mean'])) are written as one row: Q1 sum, Q1 mean. An index's header is its name, or index when it has none.

Writing a table back over itself. write() writes its cells and nothing else. A cleaned table that is shorter (or narrower) than the one it replaces leaves the old last rows standing below it, and the run prints a note naming them. replace=True clears the table the target cell is in, from that cell down and to the right, before writing:

df = sheet.read().drop_duplicates()
sheet.write(df, 'A1', replace=True)    # no old rows left below

Filtering#

sheet.filter('A2:F100', 'Region', '=', 'East')     # by heading
sheet.filter('A2:F100', 'F', '>', 1000)            # by letter: narrows the first
sheet.filter('A2:F100', 3, 'between', 10, 20)      # by offset in the range
sheet.clear_filter()

It is the sheet's own AutoFilter: the header row shows the filter, the user can change or clear it, charts and SUBTOTAL follow it, and it is saved with the workbook. A second call on another column narrows the first; the same column again replaces that column's condition. Like Data → Filter (AutoFilter), it runs from the row under the header down to the first blank row. Pass the data rows, the range below the header (filter('A2:J5001', …)): the row above it is the header, and is never hidden. A range that starts at row 1 has no row above, so its first row is taken as the header, and the run says so.

The same spellings are read in a pivot's filters= (every operator above), in validate(op=…) (the comparisons, 'between' and 'notBetween') and as a cond_format kind ('>' '<' '=' 'between' 'contains' 'top' 'bottom'), and a comparison may also be spelt 'gt' or 'greaterThan'. A word a call does not take, or a column that is not in the range, raises an error that lists what is allowed.

What a read gives you#

A formula that reads an empty cell gives 0, as in Excel (=A1 over an empty A1 reads 0), and arithmetic on a formula's "" is '#VALUE!'.

An error is never None, so a broken formula cannot pass for a blank. One error makes a DataFrame column non-numeric, and read() prints a note naming the first one; pd.to_numeric(df[col], errors='coerce') turns errors into NaN when you mean to leave them out.

Sheets and names made in the same run#

• New sheets appear as tabs only after the script finishes. Sheets you create with add_sheet / pivot are fully writable within the run (write to the returned handle). A name from define_name works at once: a formula in the same run can use it (=SUM(NewName)), and it moves with rows or columns inserted later in the run.

Writing a script you can run twice#

A script that makes its own sheet is run again safely with one argument. add_sheet('Report') on a second run returns a differently named sheet; replace=True hands back the same sheet, empty, as a new one would be:

out = sheet.add_sheet('Report', replace=True)   # the same sheet, emptied
out.write(rows, 'A1')
out.chart('A1:B10', 'bar', 'Revenue')
out.freeze(1)

A script that works on a sheet it does not replace (the active sheet, say) adds to what is there, so take off what the last run added first:

sheet.clear_charts()            # charts are added, never replaced
sheet.clear_cond_formats()      # a different rule on the same cells would stack
sheet.cond_format('E2:E%d' % sheet.rows, 'dataBar')
sheet.validate('C2:C%d' % sheet.rows, 'whole', op='>=', f1=0)   # replaces the rule on those cells

The same conditional format on the same cells is added once, however often the script runs.

Methods#

Read / write (pandas-first)#

Cells#

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

Structure (whole rows / columns — 0-indexed; formulas rebase)#

Data tools (Data and Insert menus)#

Import / export (in-memory — no file dialog)#

Saving a workbook is a menu action (File → Save, or the File menu's exports). Inside a script, read() gives you the data and to_csv / to_json hand it back out as text — an agent can persist or transmit that however it likes. The one file a script can write is a PDF (export_pdf, below), and only where the user chooses or allows. A script the AI writes that writes a file is never run unseen: Ask in plain English puts it in the editor for you to read and run.

Reading another workbook — await open_file(path)

Opens a .sprd, .xlsx, .xls, .csv or .tsv from disk as data, without a window and read-only. Note the await.

The path is a full path ('/Users/me/Reports/jan.xlsx'); a relative path, a ~ path or anything but text raises before anything is asked. The first time a script names a file you have not opened yourself in this session, Spredin asks you, showing the full path, before it reads it — Spredin opens only files you choose. Refusing raises an error in the script. A file you opened with File → Open or from the Finder is not asked about again.

wb = await open_file('/Users/me/Reports/jan.xlsx')
print(wb.sheets)                          # ['Summary', 'Raw']
total = wb.sheet('Summary').cell('B12')   # computed value, like sheet.value
rows = wb.sheet('Raw').read_values()      # every cell as a 2-D list
df = wb.sheet('Raw').read()               # a DataFrame, row 1 as the header
wb.close()

Formulas in the file compute against that file's own sheets and defined names. A CSV is converted the way File → Open converts it ($1,200 → 1200, dates → dates). A large .xlsx is read in full, so it can take a few seconds.

Printing & PDF (the page setup and pages File → Print… uses)#

page_setup settings — the same as the Print dialog's, in Excel's terms:

• A setting a script names wrongly, or gives a value it cannot take, raises an error that lists what it can take — nothing is changed silently.
• area (a range for this one print, like Print → Selection) is not stored; a print_area is. area and workbook=True do not go together.
• pages=(first, last) counts from 1. A print of more than 200 pages asks the user first, as the Print dialog does; more than 5,000 is refused.
• export_pdf and print_out start at once. await them to get the result; a script that does not still gets its PDF or print panel once it finishes.
• When Ask in plain English (the Python panel) writes a script that saves a PDF or prints, it is put in the editor for you to read and run, like one that opens a file.

Sheets#

More for working with sheets:

Copying a sheet

backup = sheet.copy('Sales backup')          # everything, as a second sheet
sheet.copy('Sales backup', replace=True)     # the same on a re-run: one sheet, not two
report = sheet.add_sheet('Report', replace=True)
sheet.copy_to(report, 'A1:E20', at='B3')     # a range, as copy and paste
sheet.copy_to('Report', 'A1:E20', values=True)   # what the formulas show (Paste Values)

copy() copies what the sheet holds, not what it shows. Formulas stay formulas, a date stays a date, and number formats, bold and colours, merged cells, column widths and row heights, hidden rows, frozen panes, conditional formats, validation rules, comments, the filter, page setup and charts come with them. With no name the copy is called <sheet> (2). A formula that names its own sheet (=Sales!B2 on Sales) names the copy in the copy, as in Excel; a reference to any other sheet is left alone. The copy of a pivot sheet is the cells it showed, not a second pivot.

copy_to() copies cells with their formats and merged cells. A formula stays a formula and its relative references move by the distance pasted (=C2*D2 pasted two rows down is =C4*D4), so copy the cells a formula reads along with it, or pass values=True to copy the results. It does not copy column widths, frozen panes or rules: use copy() for the whole sheet.

dst.write(src.read()) copies values only, through a DataFrame: formulas become the numbers they showed and formats are left behind. Use it when that is what you want (data for another table), not to copy a sheet.

Cross-sheet (combine multiple sheets)#

Every handle from sheet.book(name) / sheet.add_sheet(name) supports the same methods (read/write/value/set/chart/style/…), each acting on its own sheet — so a script can read several sheets and write results to another:

import pandas as pd
a = sheet.book('Sales').read()
b = sheet.book('Targets').read()
merged = a.merge(b, on='Region')
out = sheet.add_sheet('Combined')
out.write(merged)

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

Number formats (number_format=)#

The codes are Excel's, dates included. A date is stored as a serial number (days since 1899-12-30); its format only changes how the serial is shown.

Anything that is not a format ('banana') raises an error that gives examples; it is never written into the cells.

Dates from a script#

Write datetime.date / datetime.datetime objects, pandas Timestamps, numpy datetime64 values or ISO text ('2024-01-15', '2024-01-15T14:30:00') and the cell becomes a real date — a serial with the 'date' (or 'datetime') format already applied, so =F2+30, sorting and charts work. That is what Excel's VBA does on write. A locale form such as '1/2/2024' is not guessed at and stays text, and a cell that already has a date format keeps it. Reading a date cell back (sheet.value, sheet.read) gives the serial; pd.to_datetime(serial, unit='D', origin='1899-12-30') turns it into a Timestamp.

A time of day (datetime.time(14, 30)) is written as a time, and a length of time (datetime.timedelta, a pandas Timedelta, a numpy timedelta64) as a duration: a number of days, shown in hours (36:00:00, the [h]:mm:ss format). Both are numbers, so a column of lap times adds up. Text such as '14:30:00' that a script writes itself stays text.

Formatting never changes the stored value, only its display.

Formula functions (inside "=…" strings)#

sheet.set(r, c, '=…') writes a live formula that the engine recalculates. The engine implements a specific set of Excel functions — anything outside it evaluates to #NAME?. Ask in plain English (the Python panel) is given the full list with signatures, so it only writes functions the engine has.

Covered, by family: math/trig (SUM ROUND ABS LN LOG EXP PI SIN…), stats (AVERAGE MEDIAN STDEV PERCENTILE NORM.DIST RANK…), lookup (XLOOKUP VLOOKUP INDEX MATCH INDIRECT OFFSET), logical (IF IFS IFERROR AND OR SWITCH), text (LEFT MID TEXTJOIN TEXTBEFORE SUBSTITUTE TEXT), date/time (TODAY EOMONTH NETWORKDAYS YEARFRAC WEEKNUM), finance (NPV IRR XIRR PMT IPMT SLN DDB), engineering (DEC2BIN BITAND…), info (ISNA ISTEXT ERROR.TYPE), arrays (FILTER SORT UNIQUE SEQUENCE TAKE VSTACK; they do not spill, so read one with INDEX or total it), LAMBDA with MAP BYROW REDUCE, and database (DSUM DCOUNT DGET). Browse them in-app by typing = in a cell.

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.

Patterns#

# Analyze
df = sheet.read()
sheet.write(df.describe(), 'H1')

# Derived formula column (live, recalculates): write row 2, fill it down
sheet.set_cell('D1', 'Revenue')
sheet.set_cell('D2', '=B2*C2')
sheet.fill_down('D2:D%d' % sheet.rows)

# Chart
sheet.chart('A1:C13', 'bar', 'Revenue by region')

# Copy a sheet (formulas, formats, widths and all)
dst = sheet.copy('Copy')

# Restructure + tidy
sheet.insert_cols(2)                       # new blank column C
sheet.set(0, 2, 'Margin')
sheet.filter('A2:F100', 'F', '>', 1000)    # hide rows where col F <= 1000 (start below the header)
sheet.remove_duplicates('A2:F100')
sheet.freeze(1)                            # pin the header row

# Pivot to a new sheet by header NAME, then chart it
pv = sheet.pivot('A1:E5', rows=['Region'], values=[('Q1', 'sum'), ('Q2', 'sum')])
pv.chart('A1:C6', 'bar', 'Q1 vs Q2 by region')
# Re-edit the pivot in place (Excel field-pane edit, from code):
pv.update_pivot(rows=['Region'], values=[('Q1', 'sum')], show_as='pctOfTotal')
# A second text column (say Product) can go across the top as the column field:
# pv.update_pivot(rows=['Region'], cols='Product', values=[('Q1', 'sum')])
pv.refresh_pivot()                          # recompute if the source data changed

# Validation + named range
sheet.validate('B2:B100', 'list', options='Low,Medium,High')
sheet.define_name('Revenue', 'C2:C100')
sheet.set('E1', '=SUM(Revenue)')

# Hand data back out as text (no file dialog)
print(sheet.to_csv('A1:C10'))

# Print setup, then a PDF (only when the user asked for one)
sheet.page_setup(orientation='landscape', fit=(1, 0), title_rows='1:1',
                 footer={'right': 'Page &P of &N'})
print(sheet.page_count(), 'pages')
path = await sheet.export_pdf('/Users/me/Documents/Q3 report.pdf')

Rules (for correct, hallucination-free output)#

• Call only the methods above — anything else raises a clear "no such method".
• sheet.write(...) places a whole table at once; sheet.set(r, c, v) is for a single cell only. Never loop to write a DataFrame.
• Don't import the app; don't reassign sheet.
• Chart with sheet.chart(...): the chart sits by its data and stays editable. matplotlib.pyplot and df.plot() are only a stand-in: they copy the data to a sheet named Plot and chart it there.

Roadmap (not available yet)#

A script cannot yet run SQL over a sheet's data or read a database query straight into a DataFrame: bring the result into a sheet with Data → Connect to Database…, then sheet.read() it.