google-sheets

# Google Sheets

Use `gws sheets` to create spreadsheets, read cell ranges, append rows, update values, and run structured batch updates against the Sheets v4 API. This is the **content** surface for Google Sheets — for the file as an object (sharing, copying, moving, trashing) use `google-drive` instead.

## Prerequisites

- `gws` installed and authenticated. If `gws` is not on PATH OR `gws auth status` reports no authenticated user, do NOT silently call setup. Instead, in a single short reply to the user:
  1. State plainly what's missing — e.g. "Google Workspace access isn't set up on this machine yet" or "your Google sign-in has expired."
  2. Ask one sentence: "Want me to walk you through setting it up?" Wait for the user's answer.
  3. If they say yes, call `read_skill` with name `google-workspace-setup` and run that skill's onboarding flow turn-by-turn. If they say no or ask to defer, acknowledge briefly and stop — do not retry the original request.
- Apply the same flow when any `gws sheets ...` call fails mid-task with `command not found` / ENOENT, HTTP 401, "no credentials", or "scope required". Don't report the failure as a dead end — surface the missing prerequisite and ask if the user wants to set it up before moving on.
- OAuth scopes the user picked at login must cover the verbs the agent will use:
  - Read and write Sheets: `sheets` (maps to `https://www.googleapis.com/auth/spreadsheets`)
  - Read-only Sheets: pass `--scopes "https://www.googleapis.com/auth/spreadsheets.readonly"` at login
  - Find sheets by title (or list recent sheets) before reading: pair with `drive.readonly`

## Selecting a Google account

The connected Google accounts (each with its tag, email, and config dir) are listed in your system context under **"Connected Google accounts"**. To target a specific account, prefix the command with its config dir:

```bash
GOOGLE_WORKSPACE_CLI_CONFIG_DIR="<configDir>" gws sheets spreadsheets create --json '{"properties":{"title":"Tracker"}}'
```

Selection rule: one account connected → just use it. Two or more:

- The user named or clearly implied one account (a tag, an email, or unambiguous context) → use only that account.
- A read/lookup/search the user didn't tie to an account (e.g. listing events, searching mail, finding a doc) → run it against **every** connected account (one `gws` call per config dir) and aggregate, labeling each result by its tag and email. Don't pick just one, and don't ask — the user wants the whole picture across accounts.
- A write (send, create, edit, delete) with no account named → ASK which account first; never guess.

If no accounts are connected yet, fall back to the setup flow in Prerequisites (`read_skill` with `google-workspace-setup`).

## When to Use

- The user asks Gini to read cell values, ranges, or whole sheets out of a Google Spreadsheet.
- Appending rows to a tracking sheet (CRM log, expense tracker, AI run log, etc.).
- Updating specific cells or ranges with computed values.
- Creating a new spreadsheet from scratch as a starting point.
- Running structured edits (insert sheets, freeze rows, format ranges, conditional formats) via `spreadsheets.batchUpdate`.

## When NOT to Use

- Sharing, moving, renaming, copying, trashing, or permission-managing a spreadsheet — use `google-drive` for the file-as-object surface.
- Long-form prose or formatted documents — use `google-docs`.
- Slide decks — use Slides (`gws slides ...`), not Sheets.
- Lightweight key-value state the agent owns internally — use the `memory` tool, not a sheet.
- Numeric analysis Gini can do in-process (sum, average, sort, filter) — fetch the data once with `+read`, compute locally, write the result back if needed. Don't round-trip every calculation through the Sheets API.

## Quick Reference

The Sheets surface splits into two layers: helper commands for the common cases (`+read`, `+append`) and the raw API (`spreadsheets.get`, `spreadsheets.values.*`, `spreadsheets.batchUpdate`) for everything else.

### Create a blank spreadsheet

```bash
gws sheets spreadsheets create --json '{"properties":{"title":"Weekly tracker"}}'
```

The response includes a `spreadsheetId` you will need for subsequent reads and writes. The `spreadsheetUrl` field is the user-facing URL — surface that, not the bare ID, when telling the user where the new sheet is.

### Read a range (helper)

```bash
gws sheets +read --spreadsheet <SHEET_ID> --range 'Sheet1!A1:D10'
gws sheets +read --spreadsheet <SHEET_ID> --range Sheet1
```

`+read` is read-only. The response is the matched `values` array (rows of cells), already unwrapped from the raw API envelope. Pass `--format csv` if the user wants to pipe the result somewhere; `--format table` for human review in chat.

### Append a row (helper)

```bash
# Simple single row, comma-separated
gws sheets +append --spreadsheet <SHEET_ID> --values 'Alice,100,true'

# Bulk multi-row insert as JSON
gws sheets +append --spreadsheet <SHEET_ID> --json-values '[["a","b"],["c","d"]]'
```

`+append` finds the first empty row at the bottom of the existing data range and writes there. To write to a specific range (overwriting), use `spreadsheets.values.update` instead.

### Read a range (raw API)

```bash
gws sheets spreadsheets values get \
  --params '{"spreadsheetId":"<SHEET_ID>","range":"Sheet1!A1:D10"}'
```

The raw response wraps the values in `{ "range": "...", "majorDimension": "ROWS", "values": [[…]] }`. Useful when you need the `range` echo or want to set `majorDimension=COLUMNS`.

### Update a specific range

```bash
gws sheets spreadsheets values update \
  --params '{"spreadsheetId":"<SHEET_ID>","range":"Sheet1!A1:B2","valueInputOption":"USER_ENTERED"}' \
  --json '{"values":[["Header1","Header2"],["Row1A","Row1B"]]}'
```

`valueInputOption` matters:
- `RAW` — strings are stored verbatim, no formula or number parsing.
- `USER_ENTERED` — Sheets parses input as if the user typed it: `=SUM(A1:A5)` becomes a formula, `1,000` becomes a number. T