Google Sheets API

Two things make this API deceptively hard. Its most-used write method, append, does not write where you tell it. And its formatting layer has a field mask where the intuitive value is a data-erasing operation. Both fail quietly, on valid input, with a 200 response.

Read the reference for the surface you need:

• `references/auth-and-drive.md` — OAuth installed-app flow, the scope matrix, service accounts, every Drive method Sheets can't do alone, quotas and the 403 reason catalogue. Read this first if nothing is authenticated yet.
• `references/core-and-values.md` — all 17 methods on spreadsheets, spreadsheets.values, sheets.copyTo, and developerMetadata, plus A1/GridRange notation and the six behavioral enums.
• `references/batchupdate.md` — all 74 batchUpdate request types in 13 groups, which 27 return replies, the field mask, atomicity, and a worked end-to-end example.

Getting authenticated

A client ID and secret are not access. They identify your app; they don't authorize a Google account. You need a one-time consent that mints a refresh token, which is what scripts actually use.

scripts/gsheets_auth.py does this: run it once, approve in the browser, and it writes token.json. Every run after that is non-interactive.

Four things to get right, because each one costs an hour otherwise:

Ask for `drive.file` and nothing else. It is sufficient for spreadsheets.create, values.update, batchUpdate, and Drive's files.create, files.copy, files.list, permissions.create, and files.export — everything needed to build and share sheets you create. It is also the only non-sensitive scope in the set, so it needs no Google verification even in production. spreadsheets is sensitive; drive and even drive.metadata.readonly are restricted. Escalate to spreadsheets only when you must open a sheet a human created.

Publish the consent screen to production. While it sits in Testing, refresh tokens expire after 7 days — the script works all week and dies with invalid_grant on Monday, which reads like a code bug and isn't. Publishing is free and instant if you only use drive.file.

Pass both `access_type="offline"` and `prompt="consent"`. Offline alone issues a refresh token only on the very first authorization; a second run returns an access token with refresh_token: null, and the failure surfaces days later. Assert the refresh token is non-empty before persisting.

Enable both APIs in the Cloud project. Enabling Sheets does not enable Drive, and you need Drive.

invalid_grant is never retryable. It maps to seven different terminal causes — revoked access, six months unused, the 100-token-per-account limit, the Testing expiry. Re-authorize rather than retrying.

The two silent data-loss bugs

`append`'s `range` is a search hint, not a destination. The API finds the last table your range intersects and writes at the row below it, in that table's first column. From Google's own worked example: A1 writes to A3, while Sheet1, B4, B2:D4, and A3:G10 all write to B7.

Two consequences that bite GTM work specifically. A single stray blank row splits a table, so the next append lands hundreds of rows away. And the destination column follows the table, not the sheet — appending to a table that starts at column B shifts every field one column right, silently misaligning an entire enriched list.

Read the real destination from updates.updatedRange in the response, never from tableRange (which is the pre-append state and is empty when no table was found). Set insertDataOption: "INSERT_ROWS" unless you specifically want OVERWRITE, which is the default and destroys whatever sits below the detected table.

*`fields: "" erases data.** Google's field-mask semantics are "masked but absent means cleared." So repeatCell with fields: ""` to bold a header row wipes the values, number formats, notes, and validation in that range. Omitting `fields` is a hard 400, which pushes people straight to `""` as the fix that makes the error go away — and it destroys the sheet.

Name the exact paths instead: fields: "userEnteredFormat.textFormat.bold". The mask root is implied, so never prefix it with cell.. Only 17 of the 74 requests take a mask.

Reading data back

Responses are ragged and sometimes have no data key at all. Trailing empty rows and trailing empty cells per row are omitted, while interior empties come back as "". So row[4] is undefined rather than "", and a header-zip loop silently drops fields off the end of short rows. An entirely empty range returns a response with no `values` key, so naive code raises a KeyError instead of handling an empty result. ValueRange.range echoes what you requested and tells you nothing about the actual shape — pad every row to the header length before using it.

Set valueRenderOption: "UNFORMATTED_VALUE" on any read you intend to compute with. The default is FORMATTED_VALUE, which returns locale-formatted strings like "1,234.50" and "50.00%" that break parseFloat.

Dates are serial numbers with a 1899-12-30 epoch, naive wall-clock in the sheet's timezone. Treating them as UTC is the classic "everything is two hours early" bug.

Writing well

`valueInputOption` is required and it moves. It's a query param on values.update and values.append, but a body field on values.batchUpdate — passed as a query param there it is silently ignored and the call 400s.

The choice is lossy either way. USER_ENTERED parses like a human typing: "007" becomes 7, "1/2" becomes a date, and numbers parse per the spreadsheet's locale, so 1.234 differs between en_US and de_DE. RAW preserves strings exactly but leaves formulas as inert text. For enriched GTM data — phone numbers with leading zeros, IDs, version strings — RAW is usually right, and you set number formats separately via batchUpdate.

Batch aggressively. The quota is 60 requests per minute per user — one per second — separately for reads and writes. A loop writing one row at a time will hit it. One values.batchUpdate carrying many ranges costs one request.

Build a whole sheet in one atomic call. You can't consume a reply mid-batch, but sheetId, chartId, namedRangeId, bandedRangeId, slicerId, and filterViewId are all client-assignable. Pre-assign them and a create-sheet-write-header-freeze-format-chart sequence collapses into a single batchUpdate that either fully applies or doesn't. The worked example in references/batchupdate.md does exactly this.

Requests apply in order, so index shifts are real. Deleting rows or rules must go descending, and moveDimension.destinationIndex is computed before the removal.

Traps that look like bugs in your code

• `sheetId: 0` is real and falsy. if sheet_id: silently skips the first sheet. Test against None.
• GridRange ↔ A1 is a two-sided off-by-one. GridRange is 0-indexed and half-open; A1 is 1-indexed and inclusive. startRowIndex = a1Row - 1 but endRowIndex = a1EndRow — different adjustments on the two ends. Getting the end wrong silently drops the last row. Omitting sheetId targets sheet 0, not "the current sheet."
• `spreadsheets.create` cannot set a parent folder. Its body has no parents field. To create inside a folder, use Drive's files.create with mimeType: application/vnd.google-apps.spreadsheet, then write content via Sheets.
• `drive.file` cannot see files it didn't create. files.list omits them silently and files.get by ID returns 404, not 403 — so it reads as "deleted" rather than "not permitted."
• Drive reports rate limits as 403, not always 429 (rateLimitExceeded, userRateLimitExceeded). Retry logic keyed only on 429 fails permanently against Drive.
• `Color` is deprecated everywhere in favor of ColorStyle, which also unlocks themeColor. If both are set, ColorStyle wins. Components are floats 0–1, not 0–255.
• *The `ByDataFilter methods reject .readonly scopes even for reads** — a read-only app on spreadsheets.readonly will 403 on batchGetByDataFilter`.
• `includeGridData` is silently ignored whenever a fields mask is set.

Appends are not idempotent

values.* has no ETag and no If-Match. Concurrent appends race with last-write-wins, and a retried append duplicates rows — a timeout-retry on a 4,000-row append writes them twice.

This is the same hazard as the GTM APIs in this repo, so apply the same discipline: cap the batch, dry-run at small N, and on a timeout reconcile by reading the sheet back before retrying. If rows must be deduplicable after the fact, write a stable key column you can group on.

Service accounts

Reach for one when the job is unattended and there's no human to consent. The gotcha that wastes the most time: a sheet created by a service account is owned by that service account and does not appear in any human's Drive. Service accounts also have zero storage quota of their own. The fix is to create the file in a Shared Drive, or create it and immediately grant a human ownership or edit access via permissions.create. references/auth-and-drive.md covers four approaches.