Gsheet model

/gsheet-model — author & format a live Google Sheet model via Composio REST

Stand up or rebuild a calculator / financial model directly on the user's live multi-tab Google Sheet, fully automated, no copy-paste. This became possible once we learned to drive Composio through its REST API directly instead of the MCP "Tool Router" connect flow (which is broken vendor-side). Everything here is the hardened recipe from the ExampleBrand build — read memory composio-connection-broken for the source of truth; this skill is the operational playbook.

When to use vs siblings:

• This skill — author/format/rebuild tabs on a live sheet via Composio REST. Default when Composio is working.
• /gsheet-bridge — the Apps-Script-paste fallback for when Composio is down. Same job, clunkier path.
• /ecom-data-analyst — read + analyse a catalog (doesn't author formatted tabs).
• /sheet-create — make a brand-new blank sheet.

0. Auth (do this first, every run)

• API key: never hardcode it. Pull the live key at runtime: claude mcp get composio → the X-API-Key: header (or the composio entry in ~/.claude.json). The helper does this for you.
• Endpoint base: https://backend.composio.dev/api/v3 (v1 is retired).
• Connected account (ExampleBrand Google): ca_xxxxxxxxxxxx, user_id pg-test-xxxxxxxxxxxx. BOTH user_id and connected_account_id are required on every execute call (omitting user_id → error 1811). For a different account, re-list connections.
• Every tool call: POST /tools/execute/{TOOL_SLUG} with body {"user_id":..., "connected_account_id":..., "arguments":{...}}.
• POST via curl, not python urllib — this Mac's python has no CA bundle (SSLCertVerificationError). Build the JSON in python, write to a tmp file, curl --data @file. The helper (scripts/gsheet.py) already does this.

1. The operations (tool slugs)

Need	Tool	Key args
Read values	GOOGLESHEETS_BATCH_GET	spreadsheet_id, ranges:[ "Tab!A1:Z40" ]
Read FORMULAS	GOOGLESHEETS_GET_SPREADSHEET_BY_DATA_FILTER	spreadsheetId, includeGridData:true, dataFilters:[{a1Range}] → each cell's userEnteredValue.formulaValue
Write a block	GOOGLESHEETS_BATCH_UPDATE	spreadsheet_id, sheet_name, first_cell_location (A1), valueInputOption:"USER_ENTERED", values (2D)
Format cells	GOOGLESHEETS_FORMAT_CELL	spreadsheet_id, worksheet_id (sheetId), 0-based start/end_row/col_index, + red/green/blue (0-1), bold, italic, fontSize
Add a tab	GOOGLESHEETS_ADD_SHEET	spreadsheetId, properties:{title}
Insert rows/cols	GOOGLESHEETS_INSERT_DIMENSION	insert_dimension:{range:{sheetId,dimension:"ROWS",startIndex,endIndex}, inheritFromBefore:false}
Clear values	GOOGLESHEETS_CLEAR_VALUES	spreadsheet_id, range
Tab list + sheetIds	GOOGLESHEETS_GET_SPREADSHEET_INFO	spreadsheet_id → sheets[].properties.{title,sheetId}

GOOGLESHEETS_FORMAT_CELL does ONLY background colour + bold/italic/underline/fontSize. No borders, no number format ($/%/comma), no text colour, no column width. Those four are manual one-clicks the user does (tell them).

2. The build workflow

1. Read first. Pull the target tab's structure (values, and FORMULAS via includeGridData if splicing into an existing model). Map columns/rows by their LABELS, never by remembered addresses.
2. Build the grid in Python with row positions as VARIABLES (R_TOTAL=23, DATA0=80...) and derive every formula reference off those vars. Hardcoded row numbers break the moment a section shifts. Keep ONE shared assumptions block and reference it cross-tab (tab-qualified, e.g. 'COGS + BEROAS'!$T$3) so all tabs move in lockstep.
3. Avoid the merge trap. A rebuild on an existing tab inherits leftover merged cells (CLEAR_VALUES does NOT remove merges) — writes land only in a merge's top-left and the rest read blank. So for a fresh layout, ADD_SHEET a clean tab. To add INTO a live model, INSERT_DIMENSION blank rows (Google auto-adjusts same-sheet AND cross-sheet refs, so nothing downstream breaks) then populate.
4. Write the block with BATCH_UPDATE. Batch many cells into ONE values block — don't loop single-cell writes (per-minute read quota throttles you at ~15; 429 → wait 30-60s, retry).
5. Verify by behaviour, not by eyeballing the script. Read back: totals correct, no #DIV/0!/#ERROR!, and INJECT a sample data row to confirm computed columns actually flow. Don't "fix" working formulas off a readback hunch — test it.
6. Format with restrained, consistent colour bands (one palette, not rainbow): section-header band, highlight input cells (one accent), highlight the answer row, light tint for auto-calculated cells. Then tell the user the manual finish: auto-fit columns, add borders, apply #,##0 number format.

3. Gotcha checklist (every one cost real time on ExampleBrand)

• AND()/OR() do NOT short-circuit → AND(C>0, D/C>=x) still divides on blank rows → #DIV/0! that poisons SUM. Guard: =IF(C>0, IF(D/C>=x,1,0), 0).
• A label written USER_ENTERED that STARTS with = + - @ is parsed as a formula → #ERROR!. Use a plain bold label for subtotals, not "= Revenue gap".
• zsh: never name a shell var UID (reserved → "bad math expression"); use EID.
• CHECK BEFORE OVERWRITING a column: read the exact target range first; don't assume empty because you only read up to column L. (We overwrote Target CAC/ROAS this way — recover via Sheets File ▸ Version history; there's no API for it.)
• Never rename the tab a cross-tab formula points to.
• Returns/chargebacks etc.: keep assumptions in ONE block, reference cross-tab; tab-qualify or refs silently resolve local.

4. Helper

scripts/gsheet.py (stdlib only, shells to curl) wraps the operations: read_values, read_formulas, write_block, format_cell, add_sheet, insert_rows, clear, plus sheet_ids(). It auto-pulls the API key from claude mcp get composio. Import it or copy the calls. Default account/user are the ExampleBrand ones — pass account=/user= to override.

from gsheet import write_block, read_formulas, format_cell
write_block(SHEET_ID, "META PLANNER", "A1", grid)          # grid = 2D list
read_formulas(SHEET_ID, "FINANCIAL MODELING!B22:C40")      # {A1: formula}
format_cell(SHEET_ID, worksheet_id, 3,4, 1,6, bold=True, rgb=(0.85,0.73,0.40))

Always end a build by reading back and reporting the verified numbers, then naming the manual finish (borders / number format / column widths) since the API can't do them.