# Google Sheets

Read ranges and add rows in Google Sheets. Share a sheet with the service account and apps can use it.

URL: https://docs.qaldy.com/connectors/google-sheets/

- **Status:** [[Preview]]
- **Shelf:** Data and docs
- **How you connect:** Service account key file
- **Regions:** global and Kazakhstan
- **The service’s own API reference:** https://developers.google.com/workspace/sheets/api/reference/rest

Apps read ranges and add rows in Google Sheets through a **service account**: a robot with an
address of its own (`name@project.iam.gserviceaccount.com`) that can open only the spreadsheets
someone shares with it. Nobody signs in, so there is no OAuth app to register and the connection
does not depend on one person's Google login.

> **Status: Preview.** The gate signs the sign-in, exchanges it and calls the Sheets API as
> Google documents them, and the flow is tested end to end against a stand-in token endpoint. It
> has not been run against a live Google account, so a first call may need a fix. The catalog,
> the sheet in the console and the Gallery say Preview until it has.

## How it works

1. An admin uploads the service account's **JSON key file** on Connectors. The API reads it,
   keeps only `{ type, client_email, private_key }` (Google's `token_uri`, project and client ids
   are dropped) and seals that to the gate (HPKE, write-only, bound to the workspace and the
   `sheets` connector). The address is kept in plain text, because it is not secret and the
   console must show it so a sheet can be shared with it.
2. For a call, the gate opens the key in memory, signs a short-lived assertion (RS256) and trades
   it at `oauth2.googleapis.com` for an access token. The token is kept in gate memory until two
   minutes before Google would refuse it, keyed by workspace, sealed blob and scope, so a rotated
   key is a new entry and one workspace can never be handed another's token.
3. The gate builds the whole Sheets request from the app's small input. Apps never see the key,
   the token or the token endpoint.

The key file's own `token_uri` is ignored: the token host and the Sheets host are constants in the
gate, so a key file cannot aim it at another server. A token Google refuses is dropped and the
call is made once more with a fresh one.

## Connect it

1. In **Google Cloud**, create or pick a project and turn on the **Google Sheets API**.
2. **IAM → Service accounts** ([console.cloud.google.com/iam-admin/serviceaccounts](https://console.cloud.google.com/iam-admin/serviceaccounts)):
   create a service account. It needs no project role.
3. Open it → **Keys → Add key → Create new key → JSON**. Google downloads the file once.
4. Qaldy → **Connectors** → Google Sheets → **Connect Google Sheets** → **Choose the key file**
   (or paste its contents) → **Seal and connect**. The console reads the file in your browser
   first and says what is wrong with a wrong one (an OAuth client file, a truncated copy) before
   anything is sent. Only the three fields above leave the page.
5. **Share each spreadsheet** with the account's address (the console shows it, with a Copy
   button): in Google Sheets press **Share** and add it as **Editor**, or **Viewer** if apps only
   read. Without this Google answers `403` or `404`, and the SDK adds "Share the spreadsheet with
   the service account’s address" to the error.

**Test connection** signs in at Google and checks that Google accepts the key. It does not open a
spreadsheet (the gate doesn't know one), so a good test does not prove a sheet is shared.

## What the gate allows

| | |
|---|---|
| Hosts | `sheets.googleapis.com` for calls, `oauth2.googleapis.com` to sign in (both are listed in the app's contract, "the only hosts its gate opens to") |
| Actions | `readRange` and `appendRow`; any other action is `403 action_not_supported` |
| `readRange` | `GET /v4/spreadsheets/{id}/values/{range}`, with a **read-only** token (`spreadsheets.readonly`), so an app that may only read can't write even when the account is an editor |
| `appendRow` | `POST …/values/{range}:append`, `valueInputOption=RAW`, `insertDataOption=INSERT_ROWS`, with the `spreadsheets` scope |
| Inputs | `spreadsheetId` 10–128 of `A-Za-z0-9_-`; `range` up to 200 characters, no control characters, percent-encoded into one path segment; one row of up to 200 cells (text up to 50 000 characters, numbers, true/false or empty) |
| Values | stored as typed (`RAW`): a cell that starts with `=` stays text and never runs as a formula |

The fence on **which** spreadsheets is Google's own: the account sees only what is shared with it.
A manifest-level scope per spreadsheet is not built; the templates take the sheet id from a
setting an editor can change.

## Use it from an app

```json
{ "id": "sheets", "actions": ["readRange", "appendRow"] }
```

```ts
const { values } = await ctx.connectors.sheets.readRange({
  spreadsheetId,
  range: 'Orders!A2:F',
})
await ctx.connectors.sheets.appendRow({
  spreadsheetId,
  range: 'Orders!A:F',
  values: ['Aigerim', 42, true],
})
```

`readRange` returns `values: string[][]` as Sheets shows them (formatted text). The SDK also
accepts a pasted sheet address in place of the id.

## When it breaks

| You see | Why | Do |
|---|---|---|
| Connect says "That is an OAuth client file" | The downloaded JSON has `installed` or `web` | Create the key on a **service account**, not an OAuth client |
| Test: "refused the key (401)" | The key was deleted or the account disabled | Create a new key and **Replace the key** |
| `403` / `404` on a call | The spreadsheet isn't shared with the account | Share it with the address shown on the connector |
| `403` on `appendRow` only | The account is a Viewer | Make it an Editor |
| `429` | Google's per-minute quota | Slow the app down, or read a wider range in one call |

## Gaps

- Never run against a live Google account (hence Preview).
- No spreadsheet-level scope in the manifest, no "row added" trigger (the catalog lists it as
  planned), no formatting, no sheet creation.
- The access token lives in gate memory, so a gate restart signs in again (one extra call).