--- name: gsheets-api description: Complete endpoint-level reference for the Google Sheets API v4 and the Drive API methods that go with it — all 17 core and values methods, all 74 batchUpdate request types, and the OAuth installed-app flow from a client ID and secret to a working refresh token. Use this skill whenever the user mentions Google Sheets, a spreadsheet, gspread, googleapis, GOOGLE_CLIENT_ID, a service account, or asks to create a sheet, write or append rows, read a range, format cells, add a chart or conditional formatting, share a spreadsheet, or export a list to Sheets. Use it BEFORE writing any Sheets call, because append writes somewhere other than where you point it, reads come back ragged, and the wrong field mask silently erases data. --- # Google Sheets API v4 + the Drive methods it needs 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.