Spreadsheet Automation skill for Codex

Done well means: the manual routine becomes one command that anyone can rerun, the inputs are validated before anything is written, the output workbook still looks like the one people already use (template, formats, formulas), and the totals match the last manual run. Automate the routine people do today first; improve it second.

Map the manual process first

Ask for, or reconstruct, one real run end to end and write it down:

1. Download orders.csv from Shopify admin (Mondays)
2. Open Pricing.xlsx, copy sheet "Rates"
3. VLOOKUP rate per SKU into orders, sum by region
4. Paste into Weekly.xlsx template, sheet "Summary", rows from A5
5. Email to finance

Every step becomes a function. Steps that need a human (downloading from a portal behind SSO, approving) stay manual and are listed in the README; automating a login flow is a separate decision for $browser-automation.

Tool choice

Job	Default
Read CSV / xlsx into tables	pandas.read_csv, pandas.read_excel(engine="openpyxl")
Very large or messy CSVs	$csv-processing, or polars/duckdb
Write into an existing styled template	openpyxl.load_workbook(template) then write cells
Create a new formatted workbook	pandas to_excel + openpyxl styling, or $xlsx-spreadsheets
Read/write Google Sheets	gspread with a service account
Formulas users will edit later	Write formulas as strings; see $excel-formulas
Logic must stay inside Excel	$office-macros

Install with the project’s package manager (read AGENTS.md): pip install pandas openpyxl gspread needs network; ask for approval in an offline sandbox.

Workflow

- [ ] 1. Collect sample inputs from a real past run and that run's final output
- [ ] 2. Write load_*() per input with explicit dtypes and column checks
- [ ] 3. Write transform() as a pure function: DataFrames in, DataFrames out
- [ ] 4. Reproduce the past output; diff totals and row counts against it
- [ ] 5. Write the output into a copy of the template; open it and look
- [ ] 6. Add CLI flags (--period, --input-dir, --dry-run) and logging
- [ ] 7. Schedule or hand over; delivery steps (email, upload) need user confirmation

Ingest with validation

import pandas as pd

REQUIRED = {"order_id", "sku", "region", "qty", "created_at"}

def load_orders(path: str) -> pd.DataFrame:
    df = pd.read_csv(path, dtype={"order_id": "string", "sku": "string", "region": "string"},
                     parse_dates=["created_at"])
    missing = REQUIRED - set(df.columns)
    if missing:
        raise ValueError(f"{path}: missing columns {sorted(missing)}")
    df["sku"] = df["sku"].str.strip().str.upper()
    if df["order_id"].duplicated().any():
        raise ValueError(f"{path}: duplicate order_id values")
    return df

def load_rates(path: str) -> pd.DataFrame:
    rates = pd.read_excel(path, sheet_name="Rates", dtype={"sku": "string"})
    rates["sku"] = rates["sku"].str.strip().str.upper()
    return rates

• Read IDs, SKUs, ZIP codes and phone numbers as strings; pandas otherwise drops leading zeros and turns long IDs into floats.
• Fail on missing columns and duplicate keys before transforming; a silent bad join is worse than a crash.

Transform like the spreadsheet did

def summarise(orders: pd.DataFrame, rates: pd.DataFrame) -> pd.DataFrame:
    merged = orders.merge(rates, on="sku", how="left", validate="many_to_one",
                          indicator=True)
    unmatched = merged[merged["_merge"] == "left_only"]
    if not unmatched.empty:
        raise ValueError(f"{len(unmatched)} orders have SKUs with no rate: "
                         f"{unmatched['sku'].unique()[:10].tolist()}")
    merged["revenue"] = merged["qty"] * merged["rate"]
    return (merged.groupby("region", as_index=False)
                  .agg(orders=("order_id", "nunique"), revenue=("revenue", "sum"))
                  .sort_values("region"))

validate="many_to_one" raises when the lookup table has duplicate keys, which is the pandas equivalent of a VLOOKUP silently picking the first match.

Write into the existing template

from openpyxl import load_workbook
from shutil import copyfile

def write_report(summary: pd.DataFrame, template: str, out: str, period: str) -> None:
    copyfile(template, out)                       # never write over the template
    wb = load_workbook(out)                       # keep_vba=True for .xlsm
    ws = wb["Summary"]
    ws["B2"] = period
    start = 5
    for i, row in enumerate(summary.itertuples(index=False), start=start):
        ws.cell(i, 1, row.region)
        ws.cell(i, 2, int(row.orders))
        ws.cell(i, 3, float(row.revenue)).number_format = "#,##0.00"
    last = start + len(summary) - 1
    ws.cell(last + 1, 1, "Total")
    ws.cell(last + 1, 3, f"=SUM(C{start}:C{last})")   # live formula for the readers
    wb.save(out)

• openpyxl does not keep everything it cannot parse: images, charts and some shapes in the template may be lost on save. Check the saved file; if visuals disappear, keep them on a sheet that pandas never touches and use a template built for automation.
• openpyxl writes formulas but does not calculate them. A file saved by openpyxl has no cached values until Excel (or LibreOffice headless) recalculates it, so reading it back with data_only=True returns None for formula cells.
• Convert numpy types (int64, Timestamp) to Python types when writing cell by cell.

Google Sheets

import gspread

gc = gspread.service_account(filename="sa.json")      # share the sheet with the SA email
sh = gc.open_by_key(SHEET_ID)
ws = sh.worksheet("Summary")
values = [summary.columns.tolist()] + summary.astype(object).where(summary.notna(), "").values.tolist()
ws.batch_clear(["A5:D500"])
ws.update(values=values, range_name="A5", value_input_option="USER_ENTERED")

• Write in one update or batch_update call, not cell by cell; the Sheets API quota is per minute, and loops hit 429 quickly.
• Name the keyword arguments: gspread 6 changed the positional order of update.
• USER_ENTERED parses =SUM(...) and dates like a person typing; RAW stores strings.
• The service account key is a secret: keep it out of the repo and out of logs.

Scheduling and delivery

• Wrap the pipeline in a CLI with --period so any past run can be regenerated.
• Schedule with whatever the team already uses (cron, Task Scheduler, GitHub Actions); pass absolute paths.
• Emailing or uploading the result is outward-facing: preview to the user first, and get confirmation before sending to the real list (see $scheduled-reports).

Gotchas

• Excel date serials: read_excel usually converts them, but CSVs exported from Excel may contain locale-formatted dates (03/04/2026). Pass dayfirst or an explicit format.
• Merged cells and multi-row headers: read with header=[0, 1] or skiprows, then flatten column names; do not guess offsets.
• Hidden rows and filtered views are still in the data pandas reads.
• Files open in Excel on Windows are locked; detect PermissionError and tell the user.
• Floating-point totals: round money at presentation, compare with a tolerance (0.005).

Verification

• Reproduce a past manual output: row counts, per-group totals and grand total match within tolerance. Keep this as a regression test with the sample files.
• Open the generated workbook (or render with LibreOffice headless to PDF) and check the template’s formatting and formulas survived.
• Run twice with the same inputs: identical output apart from timestamps.

Handover

**Replaces:** Monday "weekly revenue" routine (approx. 2 h manual)
**Run:** `python -m weekly_report --period 2026-W43 --input-dir ~/Downloads/orders`
**Inputs:** orders.csv (Shopify export), Pricing.xlsx sheet Rates
**Output:** out/Weekly-2026-W43.xlsx from templates/Weekly.xlsx; Google Sheet "Weekly" A5:D
**Checks:** fails on missing columns, duplicate order IDs, SKUs without rates
**Verified:** matches manual output for W41 and W42 to the penny