Google Sheets Automation skill for Claude Code

A Sheets integration is done when it reads and writes in a handful of batched calls (not one per cell), survives a 429 with backoff, never clobbers columns humans edit, handles dates and numbers as values rather than display strings, and is idempotent when rerun. A spreadsheet is a UI, not a database: if the job needs joins, constraints, or more than a few hundred thousand rows, move the data elsewhere and use the sheet as a view.

Limits to design around

Limit	Value (check current docs)
Sheets API requests	300 per minute per project, 60 per minute per user per project, for reads and for writes separately
Spreadsheet size	10 million cells across all tabs
Apps Script execution time	6 minutes per execution; custom functions 30 seconds
Apps Script triggers	20 per user per script; total trigger runtime 90 min/day (consumer) or 6 h/day (Workspace)
Apps Script UrlFetch	20,000 calls/day consumer, 100,000 Workspace

Exceeding the per-minute quota returns HTTP 429; retry with exponential backoff and jitter. There is no daily request cap while per-minute limits are respected.

Choose the access path

Situation	Use
Server job, cron, ETL writing to a team sheet	Sheets API with a service account; share the sheet with the service account email
Acting on behalf of a user’s own files	OAuth user flow with the narrowest scope (spreadsheets or drive.file)
Logic that lives with the sheet: menus, onEdit validation, small scheduled jobs	Apps Script bound to the spreadsheet
Large data movement (100k+ rows)	warehouse plus connected sheets / a BI tool; or CSV import, not cell-by-cell writes

Service accounts cannot see a sheet until it is shared with their email; a 403 or 404 on a sheet that “exists” is usually this. Domain-wide delegation is a Workspace admin decision - do not request it casually.

Workflow

- [ ] 1. Map the sheet: tabs, header row, which columns code owns vs humans own
- [ ] 2. Pick access path and minimal scope; store credentials in a secret manager
- [ ] 3. Read with one batchGet using unformatted values
- [ ] 4. Compute changes in memory; write with one batchUpdate (values) and one spreadsheets.batchUpdate (formatting)
- [ ] 5. Add backoff, locking (Apps Script), and idempotency keys
- [ ] 6. Test against a copy of the sheet; verify with a read-back

3-4. Python with google-api-python-client

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("sa.json", scopes=SCOPES)
sheets = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets()

resp = sheets.values().batchGet(
    spreadsheetId=SID,
    ranges=["Orders!A1:H", "Config!A2:B"],
    valueRenderOption="UNFORMATTED_VALUE",       # numbers as numbers, not "$1,200.00"
    dateTimeRenderOption="SERIAL_NUMBER",        # dates as day counts since 1899-12-30
).execute()
header, *rows = resp["valueRanges"][0].get("values", [[]])

sheets.values().batchUpdate(spreadsheetId=SID, body={
    "valueInputOption": "USER_ENTERED",          # parse "2026-10-20" as a date, "=SUM(...)" as a formula
    "data": [
        {"range": "Summary!A1:C1", "values": [["Region", "Revenue", "Updated"]]},
        {"range": "Summary!A2", "values": summary_rows},
    ],
}).execute()

Option	Values	Use
valueInputOption	RAW / USER_ENTERED	RAW stores strings exactly (IDs with leading zeros, text that looks like formulas); USER_ENTERED parses like typing
valueRenderOption	FORMATTED_VALUE / UNFORMATTED_VALUE / FORMULA	read numbers unformatted; read FORMULA to audit formulas
insertDataOption (append)	INSERT_ROWS / OVERWRITE	append to logs

• Trailing empty cells and rows are omitted in responses; pad rows to the header length.
• Write user-supplied strings with RAW, or a value starting with = becomes a formula (formula injection in shared sheets).
• Formatting, column widths, freezing, conditional formats, protected ranges, and adding tabs go through spreadsheets.batchUpdate with a list of requests in one call.

sheets.batchUpdate(spreadsheetId=SID, body={"requests": [
    {"updateSheetProperties": {"properties": {"sheetId": 0, "gridProperties": {"frozenRowCount": 1}},
                               "fields": "gridProperties.frozenRowCount"}},
    {"repeatCell": {"range": {"sheetId": 0, "startRowIndex": 1, "startColumnIndex": 1, "endColumnIndex": 2},
                    "cell": {"userEnteredFormat": {"numberFormat": {"type": "CURRENCY", "pattern": "$#,##0.00"}}},
                    "fields": "userEnteredFormat.numberFormat"}},
]}).execute()

sheetId is the numeric tab id (in the URL as gid=), not the tab name.

gspread (6.x) is a thinner wrapper for the same API:

import gspread
gc = gspread.service_account(filename="sa.json")
ws = gc.open_by_key(SID).worksheet("Summary")
ws.update([["Region", "Revenue"], *summary_rows], "A1", value_input_option="USER_ENTERED")
records = ws.get_all_records()              # list of dicts keyed by header row

gspread 6 changed update() to take values first and range second; code written for 5.x with update("A1", values) must be updated.

Owned vs human columns

• Code writes only to ranges it owns (whole tabs like _data, or named ranges). Humans annotate in other columns or tabs that look up by key.
• Upserts: read the key column, build an index of key to row number, update matched rows in one batch, append new rows. Never assume row positions stay fixed between runs - users sort and insert rows.
• Clear-then-write a code-owned tab (values().clear then update) for full refreshes; it is idempotent and avoids stale trailing rows.

Dates and numbers

• Serial dates count days from 1899-12-30; convert with datetime(1899, 12, 30) + timedelta(days=serial). Fractions are time of day in the spreadsheet’s time zone (File > Settings), which may differ from the server’s.
• Locale affects parsing of USER_ENTERED values (“1,5” in de_DE is 1.5). Send ISO dates and dot decimals, or write RAW numbers and apply number formats.

Apps Script

function refreshSummary() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return;                  // another run in progress
  try {
    const ss = SpreadsheetApp.getActive();
    const src = ss.getSheetByName('Orders');
    if (src.getLastRow() < 2) return;                     // header only: getRange with 0 rows throws
    const values = src.getRange(2, 1, src.getLastRow() - 1, 8).getValues();   // one read
    const totals = {};
    for (const [date, region, , amount] of values) totals[region] = (totals[region] || 0) + amount;
    const out = Object.entries(totals).map(([r, t]) => [r, t]);
    const dst = ss.getSheetByName('Summary');
    if (dst.getLastRow() > 1) dst.getRange(2, 1, dst.getLastRow() - 1, 2).clearContent();
    if (out.length) dst.getRange(2, 1, out.length, 2).setValues(out);       // one write
  } finally {
    lock.releaseLock();
  }
}

• Every getValue/setValue call is a round trip; loops over cells hit the 6-minute limit quickly. Read ranges into arrays, compute, write once.
• Simple triggers (onEdit(e), onOpen) run without authorization prompts but cannot call services that need auth and are limited to 30 seconds. Installable triggers (edit, change, time-driven, form submit) run as the user who created them.
• Long jobs: process in chunks, save a cursor in PropertiesService, and schedule a continuation with a time-driven trigger; delete the trigger when finished.
• onEdit does not fire for changes made by scripts or the API.
• Store secrets in Script Properties, not in code; restrict who can edit the script.
• Custom functions (=MYFUNC()) cannot modify other cells and should be pure and fast.

Gotchas

• Opening a sheet by name breaks when someone renames it; use the spreadsheet ID and tab gid, or named ranges.
• Formulas referencing rows that code deletes become #REF!; prefer clearing values or writing to a data tab the formulas reference by whole columns.
• IMPORTRANGE, QUERY, and volatile functions recalculate on every change; a code-written data tab plus simple formulas is faster than chains of them.
• Concurrent writers (two cron jobs, a user editing) overwrite each other; schedule serially and lock in Apps Script.
• The API is not transactional across calls; a failure between writes leaves partial updates, so make each run safe to repeat.

Verification

• Read back the written ranges and compare to the intended values (count, keys, a checksum of numeric columns).
• Run the job twice; the sheet is unchanged after the second run.
• Simulate a 429 (or lower concurrency) and confirm retries with backoff succeed.
• Apps Script: check the Executions log for duration well under 6 minutes and no failures.