---
name: google-sheets-oauth-app
description: >-
  The Google Sheets API layer on top of the shared
  `google-cloud-console-oauth` skill — the two Sheets scopes (sensitive, not
  restricted), the Drive scope an app adds to find a spreadsheet and how that
  choice decides whether it needs an annual CASA assessment, A1 versus grid
  ranges, batch methods and the per-minute quotas that punish per-cell calls,
  spreadsheet hard limits, and the Apps Script and add-on paths. Use when
  asked to get Google Sheets OAuth credentials, scope a Sheets integration,
  decide between `spreadsheets`, `spreadsheets.readonly`, `drive.readonly` and
  `drive.file`, explain 429s or quota errors on Sheets, or judge whether a
  Sheets connector needs a security assessment. Read
  `google-cloud-console-oauth` first for the console mechanics and
  `google-drive-oauth-app` for Drive-side depth; use the relevant product
  skill for other Google products.
---

# Google Sheets OAuth2 App Registration

Both Sheets scopes are **sensitive — neither is restricted**, so a Sheets-only app needs verification and no security
assessment. That is the good news, and it is not usually the whole story: **the Sheets API has no method that lists or
searches spreadsheets.** An app that must find a user's spreadsheet reaches for a Drive scope to do it, and *which*
Drive scope it picks is what decides whether the project owes an annual third-party CASA assessment forever.

So the expensive decision in a Sheets registration is not a Sheets decision. It is the Drive one made while doing it
(§2), and it is a product decision — raise it, do not take it.

## Built on: `google-cloud-console-oauth`

**Read `google-cloud-console-oauth` first, then this file.** It owns all the console mechanics, and this file does not
repeat them:

- Choosing/creating the Cloud project, enabling the APIs the scopes belong to, and who may administer it.
- Configuring the app on the Google Auth Platform (Branding, Audience, Data Access, Clients) and creating a **Web
  application** client — the only type that yields a usable client secret — plus redirect-URI matching rules.
- The generic scope model: declaring every scope on Data Access, the non-sensitive / sensitive / restricted tiers, and
  the rule that **the app's tier is its most sensitive scope**.
- The client secret shown and downloadable **only once at creation**, and add-then-disable rotation.
- `Testing` vs `In production` — including the seven-day refresh-token expiry — the 100-user caps, brand and scope
  verification, the annual CASA assessment at assurance level AL1 or AL2, and `access_type=offline` + `prompt=consent`
  as the condition for getting a refresh token at all.

If you are reading only this file, you are missing all of the above.

## Inputs to collect before you start

The base skill lists the console inputs. These are the Sheets-specific ones, and the first three set the project's
tier:

| Input | Notes |
| --- | --- |
| **How does the app get a spreadsheet ID?** | User pastes a URL, a Picker, or "sync everything" — this is the tier decision (§2) |
| **Read-only or read-write?** | `spreadsheets.readonly` vs `spreadsheets`; both sensitive, so this does *not* change the tier (§1) |
| **Must it enumerate or watch the user's spreadsheets?** | If yes, a restricted Drive scope and a CASA assessment are in scope — check the Drive skill's eligibility rule too (§2) |
| **Does it need Drive-side file properties (names, owners, labels)?** | Pulls in Drive scopes even when the Sheets work would not (§2) |
| **Expected concurrent users and calls per minute** | Quotas are per minute and per user per project, not per day (§5) |
| **How large are the spreadsheets?** | Cell and metadata caps, and the 180-second request timeout, bite before the API does (§5) |
| **Is any part of this an Apps Script project or a Workspace add-on?** | Changes which OAuth client exists at all (§6) |

## Quick Start

1. Work the base skill's console steps, and **enable the Google Sheets API** (`sheets.googleapis.com`) on the project
   — plus the Google Drive API if, and only if, §2 concludes a Drive scope is needed.
2. Settle §2 **before** touching Data Access. It is the only irreversible-feeling choice here.
3. Declare exactly the scopes from §1/§2 on Data Access, and request exactly those in the authorize URL.
4. If a restricted Drive scope survives §2, stop and confirm CASA budget, owner and application-type eligibility —
   `google-drive-oauth-app` §2 — before registering anything.
5. Finish the base skill's capture, round-trip and handoff steps, adding the Sheets checks in §7.
6. Hand over the quota and limits facts (§5) with the credentials — they are what the first load test hits.

## Sheets platform state (verified 2026-09-20 — re-verify before trusting)

- **Scope tiers, from the Sheets API's own scope page:** `spreadsheets` and `spreadsheets.readonly` are **Sensitive**.
  `drive.file` is **Non-sensitive** and is the page's *Recommended* option. `drive` and `drive.readonly` are
  **Restricted**. There is no restricted Sheets scope.
- **Sheets scopes apply to a whole spreadsheet file and cannot be limited to one sheet within it.** Google's own
  mitigation is a protected range, which is a document feature, not an authorization boundary.
- **The `spreadsheets` resource exposes `create`, `get`, `getByDataFilter` and `batchUpdate` — and no `list`.** There
  is no Sheets call that enumerates or searches a user's spreadsheets. This is the fact behind §2.
- **Quotas: 300 read and 300 write requests per minute per project, 60 of each per minute per user per project**, with
  no daily cap. Exceeding them returns `429`.
- **All standard use of the Sheets API is free today, and Google states that exceeding the quota limits is *planned*
  to incur charges to the Cloud billing account later in 2026.** Treat "over quota is free to retry" as expiring.
- **Google now ships a Sheets MCP server** (`sheetsmcp.googleapis.com`) whose tools draw on the same read/write quota
  pools. If someone is pointing an agent at Sheets, it is a separate API to enable, not a different quota budget.

If the scope table or the method list does not look like this, stop and report what you actually see.

## 1. The Sheets scopes

| Scope | Grants | Tier |
| --- | --- | --- |
| `https://www.googleapis.com/auth/spreadsheets` | See, edit, create and delete **all** the user's spreadsheets | **Sensitive** |
| `https://www.googleapis.com/auth/spreadsheets.readonly` | See **all** the user's spreadsheets | **Sensitive** |

Say it plainly, because people assume otherwise in both directions:

- **Neither Sheets scope is restricted.** A Sheets-only app needs brand and sensitive-scope verification — a
  justification per scope and a demo video — and **no security assessment, no annual re-assessment**. Anyone budgeting
  CASA for a pure Sheets integration is budgeting for a Drive scope they have not noticed (§2).
- **Neither is narrow, either.** `spreadsheets.readonly` reads *every* spreadsheet the user can open, given its ID. It
  is "read-only", not "limited". The only per-file Sheets access that exists comes from `drive.file` (§2).
- **Read-only vs read-write does not change the tier.** Choose it on least privilege, not on review cost. A connector
  that only ever reports should hold `spreadsheets.readonly`, but it buys no paperwork relief.

## 2. The Drive decision that sets the project's tier

This is the section worth reading twice.

The Sheets API takes a spreadsheet ID and works on that file. It cannot tell you which spreadsheets exist. So the
product question — *how does the app learn the ID?* — decides the scope set, and the scope set decides the tier:

| How the app gets the ID | Scopes beyond Sheets | Tier of the whole app | What the product can do |
| --- | --- | --- | --- |
| **User pastes a spreadsheet URL or ID** | none | **Sensitive** | Works on the sheets the user names. No browsing, no discovery, no "watch my Drive". |
| **User picks the file** (Google Picker) | `drive.file` — non-sensitive | **Sensitive** (from the Sheets scope) | Per-file consent: files the user picked, files shared with the app, files the app created. No enumeration. |
| **App lists/searches the user's spreadsheets** | `drive.readonly` or `drive` — **restricted** | **Restricted** | Whole-Drive discovery, sync-everything, "find the file called Q3 Forecast" — and verification **plus an annual CASA assessment**. |

Three things follow, and they are the whole point of this file:

- **One scope moves the entire project.** Adding `drive.readonly` so a dropdown can list spreadsheets puts an app that
  is otherwise sensitive-tier into restricted-tier verification, an external security assessment, and a yearly
  renewal — for a file-picker convenience. Price the convenience against the assessment before declaring the scope.
- **`drive.file` composes cleanly with Sheets.** It is non-sensitive, Google's recommended option on the Sheets scope
  page, and once a file is picked the full Sheets API works on it normally — create, read, batch-update, all of it. An
  app that creates its own spreadsheets also reaches them under `drive.file`, because it created them. What it can
  never do is *find* a file the user did not hand over.
- **It is a product decision to raise, not to take.** "Connect your Sheet" (paste or pick) and "sync my whole Drive"
  are different products with different costs, not two configurations of one product. Present both, with the
  assessment attached to the second, and let the owner choose. If whole-Drive visibility genuinely *is* the product,
  the assessment is the price of the product — and then read `google-drive-oauth-app` §2 first, because restricted
  Drive scopes are additionally limited to three application categories and are unobtainable outside them at any
  price.

Two traps inside the trade:

- **Switching later re-consents everyone.** Moving a live integration between `drive.readonly` and `drive.file` is a
  re-authorization event for every connected customer plus a UI change (something has to pick the file). It is a
  project, not a settings change.
- **Drive-side metadata drags the scope back in.** Wanting the file's name, owner, folder, sharing state or Drive
  labels is a Drive read, not a Sheets read. Check what the product actually renders before concluding it is
  Sheets-only. `drive.labels.readonly` is itself non-sensitive, but the file read it accompanies usually is not.

## 3. Addressing cells: A1, grid ranges, and anchors that survive edits

Two range syntaxes, and they are not interchangeable across methods — value methods take A1 (or R1C1) strings, while
`batchUpdate` requests take `GridRange` objects:

- **A1** is `Sheet1!A1:B2`. `Sheet1!A:A` is a whole column, `Sheet1!1:2` whole rows, `Sheet1!A5:A` open-ended from row
  5, and a bare `Sheet1` is the entire sheet. Omitting the sheet name targets **the first visible sheet** — which is
  whatever the user dragged to the front this morning.
- **Quoting is load-bearing.** Sheet names with spaces or special characters need single quotes: `'My Custom Sheet'!A:A`.
  And names collide: with a named range called `Sheet1`, the string `Sheet1` resolves to the *named range* while
  `'Sheet1'` resolves to the sheet. A connector that interpolates user-chosen tab names into A1 strings without
  quoting will eventually write to the wrong place rather than fail.
- **`GridRange` is zero-based and half-open** — `startIndex` inclusive, `endIndex` exclusive — and identifies the sheet
  by its numeric `sheetId`, not its title. Missing indexes mean unbounded on that side; start equal to end is an empty
  range. Off-by-one and one-based/zero-based confusion between the two systems is the most common Sheets mapping bug.
- **Sheet IDs and spreadsheet IDs are stable across renames; A1 strings are not stable across edits.** Any row the app
  wants to come back to later should be anchored by **developer metadata**, not by row number — inserting a row above
  moves the data and leaves the range pointing at a neighbour.
- **Developer metadata has a visibility that is tied to the Cloud project.** Project-visible metadata is readable only
  from the Cloud project that wrote it. Re-registering the integration under a *different* Cloud project therefore
  makes every project-visible anchor invisible, with no error — the data is simply not found. That is an OAuth-layer
  decision with a data-layer consequence: keep the project stable, and treat a project change as a migration.

## 4. Batch or die

Sheets counts quota per **request**, and **a batch request — including every subrequest inside it — counts as one**.
That single sentence is the difference between an integration that scales and one that does not:

- Writing 500 cells with 500 `values.update` calls is 500 write requests, which exceeds the 60-per-user-per-minute
  ceiling eight times over. The same 500 cells in one `values.batchUpdate` is **one** write request.
- Use `spreadsheets.values.batchGet` / `batchUpdate` for cell values, `spreadsheets.batchUpdate` for structure
  (inserting rows, formatting, named and protected ranges, sheets, tables, find-and-replace), and
  `spreadsheets.values.append` to add rows after an existing table.
- **Batches are atomic.** If any subrequest is invalid, the whole update fails and *none* of the changes apply. That
  is a feature for consistency and a trap for partial progress: one bad row rejects the batch, so validate before
  sending rather than retrying blind.
- On the read side, one `spreadsheets.get` with a `fields` mask usually beats several narrow reads; a `get` with full
  grid data on a large file is one request but an enormous response. There is no explicit cap on the data a read
  returns, but empty trailing rows and columns are omitted — so a response is not a reliable statement of the sheet's
  shape.
- `valueInputOption` is required on writes and is a correctness choice, not a formatting one: `RAW` stores `=1+2` as
  the literal string, `USER_ENTERED` parses it into a formula and infers types and formats exactly as typing would.

## 5. Quotas, 429s and hard limits

| Quota | Read requests | Write requests |
| --- | --- | --- |
| Per minute per project | 300 | 300 |
| Per minute per user per project | 60 | 60 |

- There is **no daily limit** — stay inside the per-minute windows and the day is unbounded.
- **429s are normal at scale, not a bug and not an auth failure.** The documented response is truncated exponential
  backoff with jitter, up to a 32–64 second maximum, and the window clears after a minute. Any connector doing bulk
  work should treat `429` as flow control; note that Google also surfaces rate limiting as `403` with a
  `rateLimitExceeded` / `userRateLimitExceeded` / `RATE_LIMIT_EXCEEDED` reason, which is *not* a permission problem
  and must not trigger a re-authorization.
- A **service account counts as a single user** against the per-user quota, so routing every customer through one
  service identity concentrates them all into 60 requests a minute.
- Quota increases can be requested from the Cloud console's Quotas page; approval is not guaranteed and large
  increases take longer.

Hard limits that end a design before the quota does:

- **10 million cells, or 18,278 columns (column ZZZ), per spreadsheet** — and that cell budget is shared across *all*
  sheets in the file, so "add another tab per month" runs out. Google publishes no separate per-spreadsheet tab count
  on the file-limits page; the cell cap is the one that binds.
- **A cell over 50,000 characters is dropped** when a spreadsheet is converted from Excel.
- **Connected Sheets**: up to 200k rows for pivot tables, and 500k rows or 5M cells for extracts.
- **Developer metadata: 30,000 characters for the spreadsheet plus 30,000 per sheet.** A per-row anchoring scheme with
  long keys will hit this on a long sheet.
- **A single request that takes more than 180 seconds times out.** Large batches need splitting for time even when
  they are legal for size.

## 6. Apps Script and add-ons — only where they change the credential

If the work is a bound script or a Workspace add-on rather than a hosted connector, the OAuth client is not yours in
the way §1–§2 assume:

- **Every Apps Script project has an associated Cloud project.** By default Apps Script creates a *default* project
  that most users cannot even open in the Cloud console. There is no OAuth client of yours to configure, and no
  consent screen you control.
- **A standard Cloud project is required** to publish as a Google Workspace add-on, **to verify the script's OAuth
  client**, to run `scripts.run`, to read Cloud logs, and to create a file-open dialog (the Picker). Verification is
  on that list: a script on a default project cannot be verified, so anything customer-facing needs the switch.
- **The switch is one-way and best made early.** Default → standard is possible at any time; standard → default is
  not, and switching late can force every user to re-authorize. Moving between two standard projects additionally
  means re-enabling advanced services and APIs and can disturb an existing Marketplace listing.
- **Apps Script infers scopes by scanning the code, and it sometimes infers permissive ones.** A published add-on
  should pin the narrowest set explicitly in the manifest's `oauthScopes`; an inferred broad scope is exactly how a
  sensitive-tier add-on quietly becomes a restricted-tier one.
- **A container-bound script sidesteps §2 entirely** — it is handed the spreadsheet it lives in, so it never needs to
  search Drive for it. That is a genuine architectural answer to the restricted-scope problem when the product can
  live inside the document.
- **Granular consent applies across every Apps Script surface**, so partial grants are normal and the script must
  handle a scope the user declined rather than assuming all-or-nothing.

## Product fact — what the Sheets connector asks for

> **As of 2026-09-20, Unified.to's Google Sheets connector requests**, per capability and direction:
>
> - **Login / identity only:** `openid`, `profile`, `email` — non-sensitive.
> - **Cell, row, table and query capabilities:** read `https://www.googleapis.com/auth/spreadsheets.readonly`;
>   write `https://www.googleapis.com/auth/spreadsheets`. No Drive scope — **sensitive tier**.
> - **Capabilities that locate or list spreadsheets, and the file-level capability:** read adds
>   `https://www.googleapis.com/auth/drive.readonly`; write adds `https://www.googleapis.com/auth/drive.file`. The
>   file-level read and write paths also add `https://www.googleapis.com/auth/drive.labels.readonly`, because that
>   capability reuses the Drive connector's implementation, which resolves Drive labels.
>
> **So the read and write sides land in different tiers.** The write configuration tops out at `spreadsheets` +
> `drive.file` — **sensitive**, no security assessment. The read configuration includes `drive.readonly`, which is
> **restricted**: enabling any read path that lists or locates spreadsheets puts the registering app into
> restricted-scope verification **and an annual CASA assessment**. This is §2 made concrete, and it means the cheapest
> honest answer is often to enable only the capabilities actually used.
>
> Auth quirks worth knowing: the authorize request carries `access_type` and `prompt`, so the offline/consent
> behaviour the base skill describes is available; token exchange and refresh are standard form-POSTs against Google's
> endpoints. The connector also supports a **Google service-account credential** as an alternative to the user OAuth
> flow, requesting `spreadsheets` plus `drive.labels.readonly` — that path has no consent screen and no user scopes,
> and inherits the service-account constraints in `google-drive-oauth-app` §4. Finally, it normalizes Google's `403`
> rate-limit responses into `429`, so quota pressure surfaces as backoff rather than as an auth failure.
>
> Scope sets change. This note is dated, not live; confirm with the connector's owner before submitting anything.

## 7. Sheets-specific end-to-end checks

On top of the base skill's authorize → refresh round trip:

1. Make one real read of cell values and confirm the **Google Sheets API is enabled** on the project — a perfectly
   configured client against a project without the API fails at the first call, not at authorization.
2. Compare the **granted** `scope` against the requested one. Granular consent lets a user drop, say, the Drive scope
   and keep the Sheets one; the failure then appears later as a missing file rather than as a consent error.
3. If the app uses `drive.file`, test the asymmetry deliberately: a spreadsheet the user picked is readable, **and one
   that was never picked is not**. Testing only against a file the app itself created proves nothing.
4. Test against a spreadsheet the authorizing user **does not own** but can open, and one in a shared drive if those
   matter — that is where permission problems masquerade as scope problems.
5. Exercise one real batch write with `valueInputOption` set as production sets it, and confirm formulas and dates
   land as intended rather than as strings.
6. Push past 60 requests in a minute on purpose once, and confirm the client backs off rather than re-authorizing.

| Symptom | Cause |
| --- | --- |
| Every Sheets call fails although the scopes look right | Google Sheets API not enabled on the project |
| Auth succeeds; reading a specific spreadsheet 404s | With `drive.file`, a file the user never picked is invisible, not forbidden (§2) |
| The app cannot list the user's spreadsheets | Expected — no Sheets method lists spreadsheets; that needs a Drive scope (§2) |
| `403` with `rateLimitExceeded` / `userRateLimitExceeded`, or `429` | Per-minute quota, not authorization — back off, never re-authorize (§5) |
| Writes land in the wrong tab | A1 range with no sheet name resolves to the first **visible** sheet, or an unquoted name collided with a named range (§3) |
| A whole batch fails for one bad row | `batchUpdate` is atomic by design (§4) |
| Request times out on a big sheet | The 180-second per-request processing limit (§5) |
| Previously written row anchors stop resolving | Project-visible developer metadata is scoped to the Cloud project that created it (§3) |
| Verification asks for a security assessment on a "Sheets app" | A restricted Drive scope is in the declared set (§2) |
| A published Apps Script add-on cannot be verified | It is still on a default Cloud project (§6) |

## Stop and ask

Beyond the base skill's list, hand back to a human when:

- The product needs to **enumerate or search a user's spreadsheets** and nobody has confirmed CASA budget, a named
  owner, and application-type eligibility (§2, and `google-drive-oauth-app` §2). Register nothing until that is answered.
- Someone proposes **adding `drive.readonly` "just to list files"** on an app that is otherwise sensitive-tier — that
  is a tier change, an assessment and an annual renewal, and it deserves an explicit decision.
- Someone proposes **migrating live connections** between `drive.readonly` and `drive.file`, or moving an Apps Script
  project to a different Cloud project — both re-consent every user (§2, §6).
- Expected volume needs a **quota increase**, or the planned move to charging for over-quota usage changes the cost
  model (§5).
- A single spreadsheet is near the **10-million-cell** ceiling and the design assumes it can keep growing (§5).
- The Sheets scope tiers or the method list do not match the **Sheets platform state** section above.

## References

Sheets-specific only; the base skill carries the generic Google OAuth references, and `google-drive-oauth-app` carries
the Drive-side ones (restricted-scope application types, the Picker, shared drives). Verified to resolve on 2026-09-20.

- Choose Google Sheets API scopes (the two Sheets scopes, their tiers, the Drive options, the no-per-sheet-scope rule) — https://developers.google.com/workspace/sheets/api/scopes
- Google Sheets API overview (spreadsheet/sheet IDs, A1 and R1C1 notation, named and protected ranges) — https://developers.google.com/workspace/sheets/api/guides/concepts
- Usage limits (per-minute quotas, one request per batch, atomicity, 180-second timeout, planned over-quota charging) — https://developers.google.com/workspace/sheets/api/limits
- Read and write cell values (`valueInputOption`, render options, append, omitted trailing rows) — https://developers.google.com/workspace/sheets/api/guides/values
- Update spreadsheets with `batchUpdate` (request types, atomicity, field masks) — https://developers.google.com/workspace/sheets/api/guides/batchupdate
- Read, write and search metadata (developer metadata, project vs document visibility, 30,000-character limits) — https://developers.google.com/workspace/sheets/api/guides/metadata
- `spreadsheets` REST resource — the method list with no `list` — https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets
- `GridRange` and `DataFilter` semantics (zero-based, half-open) — https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/other
- Improve performance (`fields` partial responses, gzip) — https://developers.google.com/workspace/sheets/api/guides/performance
- Files you can store in Google Drive (10M cells, 18,278 columns, Connected Sheets caps) — https://support.google.com/drive/answer/37603
- Apps Script Google Cloud projects (default vs standard, the one-way switch, what standard is required for) — https://developers.google.com/apps-script/guides/cloud-platform-projects
- Apps Script authorization scopes (inferred scopes, `oauthScopes` manifest, granular consent) — https://developers.google.com/apps-script/concepts/scopes