Spreadsheet Ops Agent Skill

spreadsheet-ops — the spreadsheet as a programmable surface

A spreadsheet is a runtime, not a document. Your job is to emit a checkable artifact — a .xlsx

file that opens, a script that compiles, a cell range you can re-read — never a hand-waved formula in

prose. Pick the tool that matches the runtime (local file vs cloud sheet, one-shot vs event-driven),

write the formula with comma arguments, and verify before you claim it works.

The trap that bites everyone first: openpyxl writes formulas but does not compute them. The string

is stored; Excel evaluates it on open. If you read the cell back with Python it is None until a real

Excel/LibreOffice session has saved it. Internalize that before you touch a workbook.

Pick the tool first (this branches, so decide before coding)

| Your situation | Reach for | Why |

| --- | --- | --- |

| Read and modify an existing local .xlsx | openpyxl | Only mainstream lib that round-trips an existing file. |

| Write a new .xlsx rich with charts/conditional formats, possibly large | xlsxwriter (via pandas ExcelWriter) | Write-only but the richest formatting + low-memory constant_memory mode. |

| Dump a DataFrame to a sheet, minimal fuss | pandas df.to_excel(...) | Wraps an engine; pass engine= explicitly so the result does not depend on what is installed. |

| Read/write a cloud Google Sheet from a script | gspread (+ gspread-formatting for colors/rules) | Friendly wrapper over the Sheets API for CRUD. |

| High-volume cloud writes, must beat quotas | Sheets API v4 batchUpdate | One batched call counts as one request — survives the per-minute limit. |

| In-sheet menus, time-driven triggers, custom functions | Apps Script (V8) | Runs inside the sheet; the only place for onEdit/menus. |

Two you will confuse: xlsxwriter cannot open an existing file — if you need to edit one in place,

that is openpyxl. And gspread vs the raw Sheets API is convenience vs throughput: reach for raw

batchUpdate only when gspread's per-call writes would blow the quota.

Versions seen on PyPI when this was written (2026-06-02): openpyxl 3.1.5, gspread 6.2.1

(Python 3.8+), gspread-formatting 1.2.1, pandas on the 3.x line, Sheets API v4, Apps

Script on the V8 runtime. Treat these as a floor, re-check PyPI before pinning, and pin whatever

you actually resolve — do not copy a point release from a doc as gospel.

Formula rules (each rule, one reason)

• Arguments are comma-separated, always — never semicolons. openpyxl and the file format use

commas regardless of your machine's locale; a semicolon is the single most common "my written

formula is broken" cause. Bad: =SUM(A1;A2) → Good: =SUM(A1,A2).

• A formula string must start with =. ws["A1"] = "SUM(1,1)" stores literal text; `ws["A1"] =

"=SUM(1,1)" stores a formula. No leading =` means no calculation, ever.

• Anchor references you intend to fill. A lookup table dragged down breaks unless absolute: Bad:

=VLOOKUP(A2,Sheet2!A:B,2,0) → Good: =XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$B:$B).

• Prefer XLOOKUP over VLOOKUP — exact match by default, no magic column index to miscount, works

leftward. VLOOKUP's 4th arg silently defaulting to approximate match is a classic wrong-result bug.

• Prefer dynamic arrays over Ctrl+Shift+Enter array formulas — =FILTER(...), =SORT(...),

=UNIQUE(...) spill automatically and are readable. A spill blocked by data below it raises

#SPILL!; clear the range, do not array-enter.

• Never deep-nest IFs. Past two levels use LET (name sub-expressions once) or a helper column.

Bad: =IF(A1>90,"A",IF(A1>80,"B",IF(A1>70,"C","D"))) → Good: a lookup table + XLOOKUP, or

=LET(g,A1, IF(g>90,"A", IF(g>80,"B","C"))) kept shallow.

When generating cross-sheet formulas, SHEET() returns a sheet's index and SHEETS() returns the

sheet count — both have shipped in Excel for years (SHEET/SHEETS since Excel 2013) and exist in Google

Sheets, so they are safe to emit. Full XLOOKUP/SUMIFS/LET patterns and the #VALUE!/#REF!/#N/A/#NAME?

error decoder live in references/formula-cookbook.md.

Python → Excel

The no-compute trap, stated as code:

from openpyxl import load_workbook
wb = load_workbook("report.xlsx")
ws = wb.active
ws["C2"] = "=A2*B2"          # stores the FORMULA string, not a number
wb.save("report.xlsx")
# Re-reading C2 now returns "=A2*B2" (the formula), or with data_only=True
# returns None — because no Excel has opened+saved the file to cache a value.
back = load_workbook("report.xlsx", data_only=True)
assert back.active["C2"].value is None   # last cached value: none yet

So: if a downstream step needs the computed number, either compute it in Python and write the value, or

open the file once in Excel/LibreOffice headless to populate the cache. Do not assume openpyxl evaluated it.

Append a sheet to an existing file (the only mode that does not silently truncate):

import pandas as pd
with pd.ExcelWriter("report.xlsx", engine="openpyxl", mode="a",
                    if_sheet_exists="replace") as xl:
    df.to_excel(xl, sheet_name="2026-Q2", index=False)

Charts and conditional formatting on a new file go through xlsxwriter (richer) — it is write-only,

so never point it at a file you need to keep. Recipes (chart, conditional format, write_only/

constant_memory for big files) are in references/python-excel.md.

Google Sheets from a script

import gspread
gc = gspread.service_account(filename="bot-key.json")  # service account
sh = gc.open_by_key("SPREADSHEET_ID")
ws = sh.worksheet("Orders")
ws.append_rows([["2026-06-02", "INV-1001", 240.00]],
               value_input_option="USER_ENTERED")      # parses formulas/dates

The failure that wastes the most time: **a service account has zero access until the sheet is shared

with its email.** Open the key JSON, copy client_email, and share the sheet with it (Editor) — a

PermissionError/SpreadsheetNotFound here is almost always an unshared sheet, not bad code.

Quotas (as published 2026-06-02 — re-check Google's "Usage limits" page, these shift): **300 read

req/min/project, 60 read req/min/user** (writes mirror this), refilled each minute, no daily cap under

the per-minute ceiling. A batchUpdate — with all its sub-requests inside — counts as one request.

So batch: build one batchUpdate body instead of N update calls, and wrap calls in exponential

backoff on HTTP 429. Batching is the difference between a nightly job that survives and one that 429s.

Creating the OAuth client or service account itself is google-workspace's

job; this skill assumes the credential exists and owns the Sheets surface. batchUpdate request shapes

and backoff code are in references/sheets-api-appsscript.md.

In-sheet automation (Apps Script V8)

Write for V8 — Google deprecated the legacy Rhino runtime (Feb 2025) and set its retirement for on

or after 2026-01-31, so new code should never target it. V8 has no fetch, no timers, no streams

— use UrlFetchApp.fetch() (or fetchAll() for parallel) for HTTP. No ES6 import/export, no private

#fields.

Read and write the sheet in one range operation, never a per-cell loop — getValue()/setValue()

inside a loop makes one API round-trip per cell and times out on real data:

function colorOverdue() {
  const sh = SpreadsheetApp.getActiveSheet();
  const data = sh.getDataRange().getValues();   // ONE read, all rows
  const out = data.map(r => r[3] === "OVERDUE" ? ["#fde0e0"] : ["#ffffff"]);
  sh.getRange(1, 1, out.length, 1).setBackgrounds(out.map(c => c)); // ONE write
}

Time-driven triggers (run nightly), onEdit simple triggers, and custom menus are covered in

references/sheets-api-appsscript.md.

Pipeline pattern: read → transform → write

A pipeline that reruns must not duplicate rows. Decide the write mode first:

• Define a header contract — the exact columns and order. Assert the live header matches before

writing; a shifted column silently corrupts everything downstream.

• Choose the write mode:
• *Full overwrite* — clear the data range, write all rows. Simple, safe when the sheet is yours alone.
• *Keyed upsert* — read existing keys (e.g. order_id) into a map, update matched rows, append the

rest. Idempotent: rerunning yields the same sheet, no duplicates.

• Dry-run first. Compute the diff (rows to add/update) and log it; only write when a --apply flag

is set. This catches a wrong key or a header drift before it touches the live sheet.

• Batch the write (batchUpdate / setValues) and back off on 429.

Anti-patterns

| Anti-pattern | Why it bites | Do instead |

| --- | --- | --- |

| Semicolon arguments =SUM(A1;A2) | File format is comma-only; locale display fools you | Always commas =SUM(A1,A2) |

| Formula string without leading = | Stored as literal text, never calculates | Start the string with = |

| Assuming openpyxl computed the result | It only stores the formula; value is None until Excel opens it | Write the value, or open once to cache |

| Per-cell getValue()/setValue() loop (Apps Script) | One round-trip per cell → timeout | getValues()/setValues() once per range |

| N single update calls to the Sheets API | Blows 60/user-min, 300/project-min quota | One batchUpdate; backoff on 429 |

| Service account not shared with the sheet | PermissionError, looks like a code bug | Share the sheet with client_email from the key |

| Deep-nested IF chains | Unreadable, miscounted parens, hard to repair | LET, a lookup table, or a helper column |

| xlsxwriter to modify an existing file | It is write-only; you lose the original | openpyxl for read+modify |

| df.to_excel("f.xlsx") over an existing file | Default mode truncates the whole workbook | ExcelWriter(..., mode="a") to append |

| VLOOKUP with a hardcoded column index | Index miscounts when columns move; approximate-match default | XLOOKUP with explicit ranges |

Verify

Run scripts/verify.sh (read-only by default; pass a path to a generated .py/.xlsx). It

py_compiles generated scripts, opens produced workbooks with openpyxl to confirm a sheet + header row,

and lints formula strings for the semicolon-argument bug. Cloud paths are validated by structure, not

live Google calls — no network or credentials required. On an empty/clean target it exits 0.

References: python-excel.md ·

sheets-api-appsscript.md ·

formula-cookbook.md.