From building-agents
Read, write, update, delete cells in a Google Sheet; create / rename / duplicate / delete worksheet tabs; style headers with bold + colored backgrounds; freeze rows or columns; set column widths and row heights; add basic filters and sort ranges; apply alternating-row banding (zebra stripes), conditional formatting, borders, and number formats; or push a CSV file into an existing Sheet. Use when the user asks to push data into a Google Sheet, sync a CSV / DataFrame / table to a Sheet, format or style a Sheet (header colors, filters, freeze, banding), add or rearrange tabs / worksheets, build a stakeholder review or dashboard Sheet, or do anything Sheets-API-shaped that the gdoc-sync skill does for Docs.
How this skill is triggered — by the user, by Claude, or both
Slash command
/building-agents:gsheetsThis skill is limited to the following tools:
The summary Claude sees in its skill listing — used to decide when to auto-load this skill
A single-file Python CLI (`scripts/gsheets.py`) over the **Google Sheets API v4** covering data, tabs, structure, styling, filters/sort, and a raw-batchUpdate escape hatch. Sibling of [gdoc-sync](../gdoc-sync/SKILL.md) — same auth model, same retry semantics, different surface.
A single-file Python CLI (scripts/gsheets.py) over the Google Sheets API v4 covering data, tabs, structure, styling, filters/sort, and a raw-batchUpdate escape hatch. Sibling of gdoc-sync — same auth model, same retry semantics, different surface.
The script has one external dep beyond Python stdlib: pip install google-auth for service-account auth (recommended). Without it you can still use gcloud ADC, or install cryptography and the script will mint JWTs itself.
# One-time: share the target Sheet with your service account as Editor.
# Then:
scripts/gsheets.py info <SPREADSHEET_ID> --sa-key ~/sa.json
scripts/gsheets.py write <SPREADSHEET_ID> --range "Sheet1!A1" \
--values-json '[["name","status"],["alpha","ok"],["beta","fail"]]' \
--sa-key ~/sa.json
scripts/gsheets.py format-header <SPREADSHEET_ID> --tab Sheet1 \
--bg "#1a73e8" --fg "#ffffff" --filter \
--sa-key ~/sa.json
SPREADSHEET_ID is the long token in the URL: https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit.
Skip --sa-key to fall back to gcloud auth application-default print-access-token (needs prior gcloud auth application-default login --scopes=...spreadsheets,...drive). Service-account auth is more reliable — see reference/auth-setup.md.
A Google Sheets file is a spreadsheet. It contains one or more sheets (aka tabs or worksheets). Each sheet is a grid of cells. Three IDs / names matter:
| Identifier | Where from | Used by |
|---|---|---|
| spreadsheetId | URL: /spreadsheets/d/<ID>/edit | Every subcommand (first positional arg) |
sheet title (e.g. "Sheet1", "Review") | What you see on the tab strip | A1-notation ranges ("Review!A1:C10"); --tab flag |
| sheetId (integer) | list-tabs output | The raw batchUpdate API — but the CLI resolves titles for you, so you rarely need it directly |
A1 notation vs GridRange: the values.* endpoints (read/write/append/clear) take A1 strings. Everything else (formatting, structure, filters) takes a numeric GridRange — but the CLI lets you pass A1 + --tab and translates it internally.
Each row shows when to use a subcommand and a one-line example. Run scripts/gsheets.py <cmd> --help for full flags.
| Subcommand | When | Example |
|---|---|---|
read | Pull a range out as JSON | gsheets read $ID --range "Sheet1!A1:C10" |
write | Overwrite a range with values | gsheets write $ID --range "Sheet1!A1" --csv data.csv |
append | Add rows after the last data row | gsheets append $ID --range "Sheet1!A:Z" --values-json '[[..]]' |
clear | Wipe cell values, keep formatting | gsheets clear $ID --range "Sheet1!A2:Z" |
--values-json accepts inline JSON 2D arrays OR @file.json. --csv reads a file. --values-stdin reads CSV from stdin (useful for pipes).
| Subcommand | Example |
|---|---|
list-tabs | gsheets list-tabs $ID |
add-tab | gsheets add-tab $ID --title Review --color "#fbbc04" |
delete-tab | gsheets delete-tab $ID --title Scratch |
rename-tab | gsheets rename-tab $ID --from Sheet1 --to Q3Data |
duplicate-tab | gsheets duplicate-tab $ID --from Template --to "Q3 (copy)" |
| Subcommand | Example |
|---|---|
freeze | gsheets freeze $ID --tab Q3Data --rows 1 --cols 1 |
resize-cols | gsheets resize-cols $ID --tab Q3Data --cols A:E --width 140 |
resize-cols --auto | gsheets resize-cols $ID --tab Q3Data --cols A:E --auto |
resize-rows | gsheets resize-rows $ID --tab Q3Data --rows 1:1 --height 32 |
merge / unmerge | gsheets merge $ID --range "Q3Data!A1:C1" |
insert-rows / delete-rows | gsheets insert-rows $ID --tab Q3Data --rows 5:7 |
insert-cols / delete-cols | gsheets delete-cols $ID --tab Q3Data --cols B:B |
| Subcommand | When | Example |
|---|---|---|
format-header | Quick header styling (bold + color + freeze, optional filter) | gsheets format-header $ID --tab Q3 --bg "#1a73e8" --fg "#fff" --filter |
format | Generic cell formatting (bg/fg/bold/italic/size/align/wrap/number) | gsheets format $ID --range "Q3!E2:E" --number-format "0.00%%" |
borders | Add borders | gsheets borders $ID --range "Q3!A1:E10" --all --color "#dadce0" |
banding | Alternating row colors (zebra stripes) | gsheets banding $ID --range "Q3!A1:Z" |
conditional-format | Highlight cells matching a condition | gsheets conditional-format $ID --range "Q3!E2:E" --condition NUMBER_GREATER --value 100 --bg "#fce8e6" |
| Subcommand | Example |
|---|---|
add-filter | gsheets add-filter $ID --range "Q3!A1:Z" |
clear-filter | gsheets clear-filter $ID --tab Q3 |
sort | gsheets sort $ID --range "Q3!A2:E" --by "B:desc,C:asc" |
When the wrappers above don't cover something (charts, data validation, protected ranges, named ranges, pivot tables…), drop to raw Sheets batchUpdate:
gsheets batch-update $ID --requests @my-requests.json
my-requests.json is an array of Request objects. See reference/batch-update.md for worked examples (data validation dropdown, protected range, chart, pivot).
The cheap, atomic way: one batched call that you compose with the wrappers. All five effects below are pleasant defaults out of the box; you only pass a color if you want to override.
ID=1tRMIvLQM85UraW7eZF1gbdKp4bhPWXOuaaJ9xlDyf3c
TAB=Review
# 1. Write data
gsheets write $ID --range "$TAB!A1" --csv data.csv
# 2. Header styling: bold white-on-blue + frozen row 1 + filter dropdowns
gsheets format-header $ID --tab "$TAB" --filter
# 3. Auto-fit columns to content
gsheets resize-cols $ID --tab "$TAB" --cols A:Z --auto
# 4. Alternating row colors below the header
gsheets banding $ID --range "$TAB!A1:Z"
This is the workflow the user's project script (sync_fixtures_to_gsheet.py) was inching toward — now first-class:
gsheets write $ID --range "Review!A1" --csv fixtures.csv --value-input-option USER_ENTERED
gsheets format-header $ID --tab Review --bg "#0b8043" --fg "#ffffff" --filter
gsheets resize-cols $ID --tab Review --cols A:Z --auto
gsheets conditional-format $ID --range "Review!D2:D" \
--condition TEXT_EQ --value "fail" --bg "#fce8e6" --fg "#b71c1c"
gsheets conditional-format $ID --range "Review!D2:D" \
--condition TEXT_EQ --value "pass" --bg "#e6f4ea" --fg "#137333"
Use USER_ENTERED (the default) so numbers, dates, and formulas parse like the user typed them in the UI. Use RAW only if you specifically want literal strings (e.g., to preserve a leading zero on phone numbers).
gsheets add-tab $ID --title "Summary" --index 0 --color "#1a73e8"
gsheets add-tab $ID --title "Raw" --index 1
gsheets add-tab $ID --title "Notes" --index 2 --color "#fbbc04"
gsheets write $ID --range "Raw!A1" --csv raw.csv
gsheets format-header $ID --tab Raw --filter
gsheets write $ID --range "Summary!A1" --values-json '[["KPI","Value"],["MRR","=SUM(Raw!C:C)"]]'
gsheets format-header $ID --tab Summary
gsheets freeze $ID --tab Summary --rows 1 --cols 1
The =SUM(Raw!C:C) only works because --value-input-option defaults to USER_ENTERED — Sheets evaluates the formula.
Simple boolean rules use the convenience flags:
gsheets conditional-format $ID --range "Sales!E2:E" \
--condition NUMBER_GREATER --value 10000 --bg "#fff8e1" --bold
For a gradient (color scale), use the escape hatch with a raw gradientRule:
cat > /tmp/heat.json <<'JSON'
[{
"addConditionalFormatRule": {
"index": 0,
"rule": {
"ranges": [{"sheetId": <SHEET_ID>, "startColumnIndex": 4, "endColumnIndex": 5, "startRowIndex": 1}],
"gradientRule": {
"minpoint": {"type": "MIN", "colorStyle": {"rgbColor": {"red": 0.98, "green": 0.94, "blue": 0.94}}},
"midpoint": {"type": "PERCENTILE", "value": "50", "colorStyle": {"rgbColor": {"red": 0.98, "green": 0.73, "blue": 0.41}}},
"maxpoint": {"type": "MAX", "colorStyle": {"rgbColor": {"red": 0.85, "green": 0.20, "blue": 0.20}}}
}
}
}
}]
JSON
# look up the sheetId first
gsheets list-tabs $ID # → copy the sheetId for "Sales", paste into the JSON
gsheets batch-update $ID --requests @/tmp/heat.json
The script doesn't auto-apply RTL paragraph direction (Sheets handles bidirectional text per-cell). For a full RTL sheet, set the tab's right-to-left flag via the escape hatch:
gsheets batch-update $ID --requests '[{
"updateSheetProperties": {
"properties": {"sheetId": <SHEET_ID>, "rightToLeft": true},
"fields": "rightToLeft"
}
}]'
Sensible defaults for headers — all WCAG-AA contrast against white text:
| Vibe | Header bg | Header fg | Banding second-row |
|---|---|---|---|
| Google blue (default) | #1a73e8 | #ffffff | #f1f3f4 |
| Forest green | #0b8043 | #ffffff | #e6f4ea |
| Warm amber | #f9ab00 | #202124 (dark) | #fef7e0 |
| Deep slate | #202124 | #ffffff | #f8f9fa |
| Muted teal | #129eaf | #ffffff | #e0f7fa |
If you change the header bg, sanity-check contrast at webaim.org/resources/contrastchecker. Stakeholder Sheets get printed and screenshotted — accessible colors matter.
Two paths. Service account is recommended (same reasons as gdoc-sync — works in CI, no consent-screen friction, shareable). See reference/auth-setup.md for full setup including the "this app is blocked" workaround and the org-policy-blocks-key-creation case.
The full list lives in reference/operations.md. The big ones:
write is destructive within its range. It doesn't merge with existing data; it overwrites. To extend a table, use append.clear keeps formatting; delete-rows deletes the row. Choose based on whether you want the rows gone or just their values gone.values.* writes do NOT trigger formatting updates. Setting a header's background is a separate format / format-header call.batch-update (which all structure/styling commands use under the hood) is atomic — either every request commits or none do. Use this to your advantage: bundle several structural changes into one batch-update call to avoid partial-state races and to save quota.A:Z) include every row in the sheet — fine for clear or add-filter, expensive for format (formats a million cells). Bound the rows when formatting.| Scenario | Use instead |
|---|---|
| Round-trip editing (Sheet → local CSV) | read covers the read direction; pair with your favorite CSV writer. |
| Real-time bidirectional sync | Build a proper integration (Apps Script trigger + webhook). One-shot scripts aren't the right shape. |
| Heavy data analysis on Sheet contents | Pull the data with read, analyze in pandas / DuckDB, write back results. Sheets is a UI, not a query engine. |
| Charts / pivots / data validation lists / protected ranges / named ranges | First-class wrappers not provided — use batch-update with raw JSON; see reference/batch-update.md. |
| Sync a CSV to Google Docs | gdoc-sync. |
Sync a .pptx to Google Slides | gslides-sync. |
gsheets/
├── SKILL.md # this file
├── scripts/
│ ├── gsheets.py # the CLI (single Python file)
│ └── README.md # per-flag reference
└── reference/
├── auth-setup.md # SA + gcloud setup, "this app is blocked" fix
├── operations.md # per-operation gotchas + nuances
└── batch-update.md # raw batchUpdate cookbook (charts, validation, etc.)
urllib.request)pip install google-auth
pip install cryptography — the script mints JWTs itselfgcloud CLI on $PATH.pptx → Google Slides.All three share the same auth model: service-account JSON shared as Editor on the target file, with gcloud ADC as a friction-heavy fallback.
Provides CDSS development patterns for drug interaction checking, dose validation, clinical scoring (NEWS2, qSOFA), and alert classification integrated into EMR workflows.
npx claudepluginhub shaharsha/claude-skills --plugin utilities