Google Sheets Automation skill for Codex

Done well means the job reads and writes in a few batched calls, survives quota errors and reruns without duplicating rows, never needs a person’s password or a committed key file, and the sheet layout it depends on is checked before it writes.

Choose the runtime

Situation	Use
Code already runs in a backend, CI job or data pipeline	Sheets API v4 via google-api-python-client or googleapis (Node)
Quick Python scripts, team already uses it	gspread (thin wrapper over the same API)
Logic should live with the sheet, run on edit/form submit/schedule, owners are non-devs	Apps Script bound to the spreadsheet, versioned with clasp
Heavy analysis	Pull data out, compute elsewhere, write results back; do not compute in thousands of API calls

Every option needs network access to Google. In a sandbox with network off, write and unit-test the transformation logic offline against fixture data, and run the API parts only with the user’s approval.

Auth (never commit credentials)

Caller	Credential
Server job acting as itself	Service account; share the spreadsheet with the service account’s email (Editor or Viewer)
Running on Google Cloud	Attached service account via Application Default Credentials, no key file
Must act as Workspace users	Service account with domain-wide delegation, creds.with_subject("[email protected]"), admin-approved scopes
Desktop tool for one person	OAuth installed-app flow, token cached outside the repo

Use the narrowest scope: spreadsheets.readonly for reads, spreadsheets for writes, drive.file when the app only touches files it created. Key files belong in a secret manager or an env var path, listed in .gitignore; check AGENTS.md for the project’s secret convention.

Workflow

- [ ] 1. Identify spreadsheet id, tabs, header row, and who else edits the sheet
- [ ] 2. Read once; validate headers and shape before writing
- [ ] 3. Compute the change set offline
- [ ] 4. Write in batches with backoff
- [ ] 5. Verify by reading back; log what changed

1–2. Read and validate layout

import os
from google.oauth2 import service_account
from googleapiclient.discovery import build

SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
creds = service_account.Credentials.from_service_account_file(os.environ["GOOGLE_SA_KEY"], scopes=SCOPES)
sheets = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets()

SHEET_ID = "1AbC..."                        # from the URL /d/<id>/edit
resp = sheets.values().get(
    spreadsheetId=SHEET_ID, range="Orders!A1:H",
    valueRenderOption="UNFORMATTED_VALUE",    # numbers as numbers, not "1,234.00"
    dateTimeRenderOption="SERIAL_NUMBER",
).execute()
rows = resp.get("values", [])
header, data = rows[0], rows[1:]
EXPECTED = ["order_id", "date", "customer", "status", "amount"]
assert header[: len(EXPECTED)] == EXPECTED, f"layout changed: {header}"

Rows come back ragged: trailing empty cells are omitted, so pad each row to the header length before indexing. Dates with SERIAL_NUMBER are days since 1899-12-30.

3. Compute the change set

Treat the sheet as a table keyed by a stable ID column, not by row number; humans insert and sort rows. Build {id: row_index} from what you read, then produce updates for changed rows and appends for new IDs. This makes reruns idempotent.

4. Write in batches

updates = [
    {"range": f"Orders!D{idx + 2}:E{idx + 2}", "values": [[status, amount]]}
    for idx, status, amount in changed
]
if updates:
    sheets.values().batchUpdate(spreadsheetId=SHEET_ID, body={
        "valueInputOption": "RAW",                 # USER_ENTERED parses "=SUM()" and "1/2" like typing
        "data": updates,
    }).execute()

if new_rows:
    sheets.values().append(
        spreadsheetId=SHEET_ID, range="Orders!A1",
        valueInputOption="RAW", insertDataOption="INSERT_ROWS",
        body={"values": new_rows},
    ).execute()

RAW stores strings as typed, so 00123 and 1-2 stay text. USER_ENTERED is right only when you intend formulas or locale parsing.

Formatting, freezing and validation go through spreadsheets.batchUpdate requests, all in one call:

sheets.batchUpdate(spreadsheetId=SHEET_ID, body={"requests": [
    {"updateSheetProperties": {
        "properties": {"sheetId": 0, "gridProperties": {"frozenRowCount": 1}},
        "fields": "gridProperties.frozenRowCount"}},
    {"repeatCell": {
        "range": {"sheetId": 0, "startRowIndex": 1, "startColumnIndex": 4, "endColumnIndex": 5},
        "cell": {"userEnteredFormat": {"numberFormat": {"type": "CURRENCY", "pattern": "£#,##0.00"}}},
        "fields": "userEnteredFormat.numberFormat"}},
    {"setDataValidation": {
        "range": {"sheetId": 0, "startRowIndex": 1, "startColumnIndex": 3, "endColumnIndex": 4},
        "rule": {"condition": {"type": "ONE_OF_LIST", "values": [
            {"userEnteredValue": v} for v in ("new", "paid", "shipped", "refunded")]},
            "strict": True, "showCustomUi": True}}},
]}).execute()

sheetId is the numeric tab id (gid= in the URL), not the tab name. Ranges in requests are 0-based and end-exclusive; A1 ranges in values calls are 1-based.

gspread equivalent for small jobs:

import gspread
gc = gspread.service_account(filename=os.environ["GOOGLE_SA_KEY"])
ws = gc.open_by_key(SHEET_ID).worksheet("Orders")
records = ws.get_all_records()                     # list of dicts keyed by header
ws.update(values=[["paid", 120.5]], range_name="D7:E7")   # keyword args: positional order changed in gspread 6
ws.append_rows(new_rows, value_input_option="RAW")

Quotas and retries

Default Sheets API limits are per minute: about 300 read and 300 write requests per project, and 60 of each per user per project. Per-call payloads can be large, so the fix for quota errors is fewer, bigger calls, not sleeping between single-cell writes.

import random, time
from googleapiclient.errors import HttpError

def with_backoff(call, attempts=6):
    for i in range(attempts):
        try:
            return call.execute()
        except HttpError as e:
            if e.resp.status in (429, 500, 502, 503) and i < attempts - 1:
                time.sleep(min(64, 2 ** i) + random.random())
                continue
            raise

A spreadsheet holds at most 10 million cells across all tabs; long-running append logs need rotation to a new tab or file.

Apps Script

Use it when the automation belongs to the sheet. Batch with getValues/setValues; each getValue/setValue in a loop is a round trip and hits the 6-minute execution cap.

function syncStatuses() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return;               // previous run still going
  try {
    const sh = SpreadsheetApp.getActive().getSheetByName("Orders");
    const range = sh.getRange(2, 1, sh.getLastRow() - 1, 5);
    const values = range.getValues();
    const token = PropertiesService.getScriptProperties().getProperty("API_TOKEN");
    const res = UrlFetchApp.fetch("https://api.example.com/orders/status", {
      headers: { Authorization: `Bearer ${token}` }, muteHttpExceptions: true });
    if (res.getResponseCode() !== 200) throw new Error(res.getContentText());
    const status = JSON.parse(res.getContentText());   // { orderId: "paid", ... }
    for (const row of values) if (status[row[0]]) row[3] = status[row[0]];
    range.setValues(values);
  } finally {
    lock.releaseLock();
  }
}

function installTrigger() {   // run once by hand
  ScriptApp.newTrigger("syncStatuses").timeBased().everyHours(1).create();
}

• Simple onEdit(e) triggers cannot call services that need authorization (UrlFetchApp, other files). Use an installable trigger for that.
• Secrets live in Script Properties, never in code.
• Version with clasp (npm i -g @google/clasp, clasp login, clasp clone <scriptId>, clasp push); commit .clasp.json without credentials. Check clasp --help if a command name differs in the installed major version.

Destructive operations

Clearing ranges (values().clear), deleting tabs or rows, and overwriting a whole range need explicit user confirmation, naming the spreadsheet and range. Prefer writing to a new tab and swapping over clearing live data that others read.

Verify

• Read back the written range and compare with the change set (count and spot values).
• Re-run the job: the second run must produce zero updates and zero appends.
• Check types: numbers right-aligned in the UI, IDs with leading zeros intact, dates as dates (not text) when intended.
• For Apps Script, check Executions in the script editor for errors and duration.

Gotchas

• Humans add columns in the middle; locate columns by header name, not letter.
• values().update on A1 with fewer rows than before leaves old rows below; clear the stale tail explicitly (with confirmation) or write a full-height block.
• Formula columns next to synced data break when you write RAW over them; keep imported data and formulas on separate tabs.
• Sheet-level protections apply to service accounts too; a 403 may be a protected range.
• Time zones: the spreadsheet has its own time zone setting; serial dates are interpreted in it.

Output

## Sheets sync: Orders (1AbC.../gid=0)
- Auth: service account orders-sync@..., Editor on this file only; scope spreadsheets
- Run: 2 API reads, 1 batchUpdate (37 rows), 1 append (12 rows); 0 retries
- Idempotency: immediate rerun gave 0 updates, 0 appends
- Schedule: Cloud Scheduler hourly (or Apps Script time trigger hourly)