Gsheets

gsheets

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.

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.

Quick start

# One-time: share the target Sheet with your service account as Editor.
# Then:
scripts/gsheets.py info  --sa-key ~/sa.json
scripts/gsheets.py write  --range "Sheet1!A1" \
  --values-json '[["name","status"],["alpha","ok"],["beta","fail"]]' \
  --sa-key ~/sa.json
scripts/gsheets.py format-header  --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//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](reference/auth-setup.md).

Conceptual model

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//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.

Operations

Each row shows when to use a subcommand and a one-line example. Run scripts/gsheets.py --help for full flags.

Data

| 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).

Tabs

| 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)" |

Structure

| 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 |

Styling

| 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" |

Filter / sort

| 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" |

Escape hatch

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](reference/batch-update.md) for worked examples (data validation dropdown, protected range, chart, pivot).

Best-practice recipes

1. "Make this Sheet look professional" — header + freeze + filter + autofit + banding

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"

2. CSV-driven review Sheet for stakeholders

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).

3. Multi-tab dashboard

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.

4. Conditional formatting via the convenience flags vs raw rule JSON

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 , "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

5. Hebrew / RTL data

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": , "rightToLeft": true},
    "fields": "rightToLeft"
  }
}]'

Color cheatsheet

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.

Auth

Two paths. Service account is recommended (same reasons as gdoc-sync — works in CI, no consent-screen friction, shareable). See [reference/auth-setup.md](reference/auth-setup.md) for full setup including the "this app is blocked" workaround and the org-policy-blocks-key-creation case.

Gotchas

The full list lives in [reference/operations.md](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.
• Per-batch atomicity. Anything you do via 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.
• Quota: 60 writes/user/minute, 300 writes/project/minute. Each batched call counts as 1. The script auto-retries 429s with exponential backoff + jitter.
• A1 column-only ranges (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.

When NOT to use this skill

| 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](reference/batch-update.md). | | Sync a CSV to Google Docs | [gdoc-sync](../gdoc-sync/SKILL.md). | | Sync a .pptx to Google Slides | [gslides-sync](../gslides-sync/SKILL.md). |

Script layout

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.)

Dependencies

• Python 3.9+ (stdlib only for HTTP via urllib.request)
• Service-account auth (recommended): pip install google-auth
• Fallback: pip install cryptography — the script mints JWTs itself
• gcloud ADC auth: gcloud CLI on $PATH

Related skills

• [gdoc-sync](../gdoc-sync/SKILL.md) — markdown → Google Doc.
• [gslides-sync](../gslides-sync/SKILL.md) — .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.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: shaharsha
• Source: shaharsha/claude-skills
• License: MIT

Install and usage instructions live in the source repository linked above.