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

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

Operations

Each row shows when to use a subcommand and a one-line example. Run scripts/gsheets.py <cmd> --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 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 <<'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

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": <SHEET_ID>, "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 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. 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.
Sync a CSV to Google Docs	gdoc-sync.
Sync a .pptx to Google Slides	gslides-sync.

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 — markdown → Google Doc.
• gslides-sync — .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.