The Stack and the Sentinel: Engineering Deterministic Sheet Acquisition
Setting the Stage: Context for the Curious Book Reader
This technical log documents the transition from metadata-based sheet listing to an automated data-stacking workflow. By bypassing grid-allocation reporting and enforcing a cell-budget ceiling, we turn volatile spreadsheet data into a reproducible, sentinel-fenced artifact. Readers will find the complete implementation for vertical TSV stacking, per-tab URL indexing, and the architectural principles behind rejecting automated QA in favor of explicit column mapping.
Technical Journal Entry Begins
🔗 Verified Pipulate Commits:
TL;DR: This is a working session log from extending a small command-line connector for Google Sheets. At the start of the session, the tool could only list a spreadsheet’s tabs using API metadata — which, it turns out, reports grid allocation (1000×26 for every tab) rather than actual contents. By the end, the tool’s default behavior fetches the true data rectangle of every tab and prints them stacked vertically, each section fenced with a size header and a clickable link that opens that exact tab in Google Sheets, all governed by a total-cell budget so nothing oversized lands in a context payload by accident. The measured result: the five tabs of a real client spreadsheet claimed 130,000 cells by allocation and actually held 8,517 — a roughly 15× overstatement. The session also includes a worked example of declining an AI-proposed next step: a QA-checking module was drafted, inspected, and deliberately set aside in favor of first building a column-mapping artifact, on the reasoning that spreadsheets rename and reorder their columns while APIs don’t, and automation against a client sheet should never proceed without a mapping the sheet’s owner can read and sign off on. The full patches, terminal receipts, and the reasoning behind each decision are shown inline.
MikeLev.in: I really like starting the “next” article (this) with the final take-away from the AI Editor from the last article, which happens to be Gemini Flash Lite Latest because it’s good enough, always available and cheap. Yet still it never fails to please me with it’s “what surprised me most” wrap-ups like so:
What surprised me most was the pivot from ‘service accounts are dangerous’ to embracing OAuth; it mirrors the maturing of the Pipulate system into a user-centric tool. The most fascinating angle is the ‘Failure as a Receipt’ logic, which effectively turns the development process into a self-documenting forensic record.
Sure, calling it out is just leaning into the sycophantic flex they try to induce partially because of their nature and partially because they’ve been RLHF’d into it to keep them helpful, harmless and you on that particular platform. They’re trying to be your second brain causing that same dopamine hit as doomscrolling! Be careful and bewarb (Gravity Falls quote, anyone?).
Okay, so now it’s the next chisel-strike project. We need to step across the
tabs that are found and find the maximum X and Y axis boundaries so that we can
make whatever kind of “select” we want to make here. I’m not sure if it should
be directly with Pandas which is part of the system here. I’ll show you the
requirements.txt so you can see everything we’re working with. We want to take
shortcuts and my Spidey senses are telling me this has mostly all been worked
out in the Pandas API, but I have an open mind.
We want to do the cheapest, most light-weight thing to plan our next step such as figuring out which tab we want to drill in on. Or alternatively we may want to just easily grab the full rectangular region that actually has data for all tabs stacked vertically in exactly the way this system you’re looking at would be inviting. We can work out the separator sentinels in the payload so that when the zip is forward and people open it they find the URL links that would let them click and open those Google Sheets directly to the tab for the section that’s showing. We can really stun ‘em. We want to use a format that’s easy for you. There’s some solution here I think that abides by this and comes off as perfectly natural.
The Design Logic for Reliable Data Extraction
And yes, there will be design decisions to be made and I want you to make them. First we provide the document ID and it will have a good most common use case default behavior. I think that will just be showing the full content of every tabs stacked vertically so you can use them. If that ends up being too big, we can address tabs directly somehow that makes sense consistent with the system. Use the spreadsheet format you think would be best for you here insofar as CSV versus TSV or whatever.
Fable 5 High: Context verified.
Receipt check first, honestly
This compile’s Manifest carries exactly one live receipt: the rolling pin. None of last turn’s probes rode, and — more importantly — the raw source proves last turn’s dismount patches never landed: prompt_foo.py still carries FAILED-PROBE RECEIPT (PENDING until canary witness) and foo_files.py still carries the PENDING: FAILED-PROBE RECEIPT todo line. That’s fine — you declared the Ad Hoc CHOP skip — but the flip cars ride again below so the constitution stops lying about its own state. The live-LIST-green and 5-tabs-at-1000×26 claims live only in the transcript; the BEFORE hand-run below re-establishes them as fresh ground truth.
Design decisions (you asked me to make them, so here they are, made)
1. Pandas is the wrong tool for this step — the Sheets API already did the work. Your Spidey sense that “this is mostly worked out” is right, but it’s worked out one layer lower than Pandas. spreadsheets.values responses return the trimmed used rectangle: trailing empty rows and columns never come over the wire. So “find the maximum X and Y boundaries” costs zero computation — len(rows) and max(len(r)) on the response are the data extents. This also settles the open grid-vs-data question for free: metadata gridProperties report allocation (the suspicious five identical 1000×26 readings), values responses report reality. Pandas enters the story at measure.py, when we start computing over cells — never during acquisition.
2. Bare doc ID now means STACK, not LIST. One metadata call resolves titles+gids, one values.batchGet pulls every tab’s used rectangle in a single round trip, and the tabs print stacked vertically — exactly the vertical-text-concatenation grammar this whole system runs on.
3. Sentinels speak the payload’s native dialect. Each tab is fenced with --- START: TAB "Name" (R rows x C cols) --- / --- END ---, and the line under each START is the clickable ...edit#gid=<N> URL for that exact tab. When the zip is forwarded, a human opens payload.md, sees the section, clicks, and lands on that tab in Google Sheets. Same marker grammar as the compile’s own sections, so it greps identically for humans and models.
4. TSV stays the format. Compact, diffable, no CSV quoting hell, pandas-ingestible downstream, and cells are already sanitized one-row-per-line. --format json|markdown still works per tab for the rare case.
5. The governor is a total-cell budget, not the 25-row cap. “Full content of every tab” and “–max 25” contradict each other, so STACK mode is governed by --budget (default 10,000 total data cells). Over budget, nothing dumps — instead you get the true-extent gauge (actual rows×cols per tab, with per-tab URLs), which is itself the drill-down map. --list keeps the old metadata-only gauge for a zero-data-fetch peek. --sheet/--range are unchanged and still --max-capped.
1. PROBES
The first is the BEFORE reading of the default behavior (currently the grid-allocation LIST; after the patch it becomes STACK or the true-extent gauge — this single delta tests the grid-vs-data hypothesis live). The AST probe gates syntax; the stack_tabs grep predicts 0→1. The last two are the PENDING-flip delta pair: 1→0 and 0→1.
.venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho
.venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
grep -c "def stack_tabs" scripts/connectors/sheets.py
grep -c "PENDING: FAILED-PROBE RECEIPT" foo_files.py
grep -c "canary-witnessed" foo_files.py
2. NEXT CONTEXT
! .venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho
! .venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
! grep -c "def stack_tabs" scripts/connectors/sheets.py
! grep -c "PENDING: FAILED-PROBE RECEIPT" foo_files.py
! grep -c "canary-witnessed" foo_files.py
scripts/connectors/sheets.py
scripts/connectors/README.md
foo_files.py
3. PATCHES
Car 1 — sheets.py: docstring, STACK mode, argparse, dispatch (four blocks, one car):
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
Golden-path modes, auto-detected from the single positional argument:
python scripts/connectors/sheets.py # IDENTITY: usage + the service-account email to share Sheets with
python scripts/connectors/sheets.py <URL-or-ID> # LIST: spreadsheet title + every tab with a rows x cols size gauge
python scripts/connectors/sheets.py <URL-or-ID> --sheet Metrics # FETCH: first --max rows of one named tab
python scripts/connectors/sheets.py <URL-or-ID> --range "'Metrics'!A1:F50" # FETCH: explicit A1 range
[[[DIVIDER]]]
Golden-path modes, auto-detected from the single positional argument:
python scripts/connectors/sheets.py # IDENTITY: OAuth wiring status; mints the token interactively
python scripts/connectors/sheets.py <URL-or-ID> # STACK: every tab's ACTUAL data rectangle, stacked vertically with payload-grammar sentinels + clickable per-tab #gid= URLs (over --budget: true-extent gauge instead)
python scripts/connectors/sheets.py <URL-or-ID> --list # LIST: metadata-only gauge (grid ALLOCATION, zero cell data fetched)
python scripts/connectors/sheets.py <URL-or-ID> --sheet Metrics # FETCH: first --max rows of one named tab
python scripts/connectors/sheets.py <URL-or-ID> --range "'Metrics'!A1:F50" # FETCH: explicit A1 range
[[[REPLACE]]]
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
SIZE DEFENSE (context windows are finite): LIST mode always reports every
tab's rows x cols x cells so an overflow is visible BEFORE fetching, and flags
tabs too big for a bare --sheet pull. Fetches are row-bounded SERVER-side
('{Tab}'!1:N) when only --sheet is given, and row-capped client-side by
-n/--max (default 25) in every mode, per THE PROBE ECONOMY RULE.
[[[DIVIDER]]]
SIZE DEFENSE (context windows are finite): STACK mode is governed by a TOTAL
data-cell --budget (default 10,000); over budget nothing dumps — a true-extent
gauge prints instead, which IS the drill-down map. --sheet fetches are
row-bounded SERVER-side ('{Tab}'!1:N) and every --sheet/--range fetch is
row-capped client-side by -n/--max (default 25), per THE PROBE ECONOMY RULE.
GRID-VS-DATA (convicted 2026-07-20): metadata gridProperties report grid
ALLOCATION (new tabs read 1000x26 whether they hold 3 rows or 900), while
spreadsheets.values responses return the TRIMMED used rectangle — trailing
empty rows/cols never cross the wire. Data extents therefore come from values
responses (len of rows x max row width), never from metadata; and no Pandas
is needed for acquisition — Pandas belongs downstream in measure.py.
[[[REPLACE]]]
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
def resolve_gid_title(service, sid, gid):
"""Map a URL's #gid= fragment to its tab title (None when not found)."""
[[[DIVIDER]]]
def stack_tabs(service, sid, fmt, budget):
"""STACK mode (the bare-ID default): every tab's ACTUAL data rectangle,
stacked vertically with payload-grammar sentinels and a clickable #gid=
URL per tab. One metadata call resolves titles/gids; one values.batchGet
pulls every used rectangle. Governed by a total-cell budget: over budget,
nothing dumps — the true-extent gauge prints instead.
"""
meta = service.spreadsheets().get(
spreadsheetId=sid,
fields='properties.title,sheets.properties'
).execute()
title = meta.get('properties', {}).get('title', '(untitled)')
tabs = [t.get('properties', {}) for t in meta.get('sheets', [])]
if not tabs:
print(f"# {title} [spreadsheetId: {sid}] — no tabs")
return
resp = service.spreadsheets().values().batchGet(
spreadsheetId=sid,
ranges=["'" + p.get('title', '').replace("'", "''") + "'" for p in tabs],
majorDimension='ROWS'
).execute()
value_ranges = resp.get('valueRanges', [])
extents = []
for p, vr in zip(tabs, value_ranges):
rows = vr.get('values', [])
n_rows = len(rows)
n_cols = max((len(r) for r in rows), default=0)
extents.append((p, rows, n_rows, n_cols))
total_cells = sum(r * c for _, _, r, c in extents)
base_url = f"https://docs.google.com/spreadsheets/d/{sid}/edit"
if total_cells > budget:
print(f"# {title} [spreadsheetId: {sid}] — {len(extents)} tab(s), "
f"{total_cells:,} data cells > budget {budget:,} — STACK withheld\n")
print(f"{'rows':>7} {'cols':>5} {'cells':>9} tab | tab URL")
for p, _, r, c in extents:
print(f"{r:>7} {c:>5} {r * c:>9,} {p.get('title', '?')} | "
f"{base_url}#gid={p.get('sheetId', 0)}")
print(f"\n# Next: python scripts/connectors/sheets.py {sid} "
"--sheet \"<Tab>\" (one bounded tab)")
print(f"# Or raise the ceiling: python scripts/connectors/sheets.py "
f"{sid} --budget {total_cells}")
return
print(f"# {title} [spreadsheetId: {sid}] — {len(extents)} tab(s), "
f"{total_cells:,} data cells (full stack)\n")
for p, rows, n_rows, n_cols in extents:
name = p.get('title', '?')
print(f'--- START: TAB "{name}" ({n_rows} rows x {n_cols} cols) ---')
print(f"# {base_url}#gid={p.get('sheetId', 0)}")
if rows:
_emit(rows, fmt)
else:
print("(empty tab)")
print(f'--- END: TAB "{name}" ---\n')
print(f"# Next: python scripts/connectors/sheets.py {sid} "
"--range \"'<Tab>'!A1:Z50\" --format json (one precise slab)")
def resolve_gid_title(service, sid, gid):
"""Map a URL's #gid= fragment to its tab title (None when not found)."""
[[[REPLACE]]]
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
parser.add_argument('--format', choices=['tsv', 'json', 'markdown'],
default='tsv',
help='Output format (default: tsv — compact and diffable).')
args = parser.parse_args()
[[[DIVIDER]]]
parser.add_argument('--format', choices=['tsv', 'json', 'markdown'],
default='tsv',
help='Output format (default: tsv — compact and diffable).')
parser.add_argument('--list', action='store_true',
help='Metadata-only tab gauge (grid allocation; zero cell data fetched).')
parser.add_argument('--budget', type=int, default=10000,
help='STACK-mode ceiling in TOTAL data cells (default: 10000).')
args = parser.parse_args()
[[[REPLACE]]]
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
if gid is not None:
# The URL named a specific tab: fetch it, bounded.
title = resolve_gid_title(service, sid, gid)
if title:
fetch_values(service, sid, title, None,
args.format, args.max)
return
list_tabs(service, sid, gid, args.max)
[[[DIVIDER]]]
if gid is not None:
# The URL named a specific tab: fetch it, bounded.
title = resolve_gid_title(service, sid, gid)
if title:
fetch_values(service, sid, title, None,
args.format, args.max)
return
if args.list:
list_tabs(service, sid, gid, args.max)
else:
stack_tabs(service, sid, args.format, args.budget)
[[[REPLACE]]]
Car 2 — README roster line:
Target: scripts/connectors/README.md
[[[SEARCH]]]
- sheets.py identity / URL-or-ID tab list with size gauge / bounded --sheet and --range values (oauth_token_file, gmail pattern; own sheets_token.json with the Sheets readonly scope; identity mode prints project_id and mints the token interactively)
[[[DIVIDER]]]
- sheets.py identity / bare URL-or-ID STACKS every tab's actual data rectangle with sentinel separators and per-tab #gid= URLs, budget-governed / --list metadata gauge / bounded --sheet and --range values (oauth_token_file, gmail pattern; own sheets_token.json; data extents from values responses, never gridProperties)
[[[REPLACE]]]
Car 3 — the overdue PENDING→banked flip (never landed last turn; the canary witness rode the 2026-07-20 compile per the transcript):
Target: foo_files.py
[[[SEARCH]]]
# - PENDING: FAILED-PROBE RECEIPT (patched 2026-07-20, unwitnessed): the `!` executor now lands stderr-only failures as first-class Manifest receipts instead of raising them into invisibility. Witness canary: `! bash -c "echo canary-stderr >&2; exit 3"` must appear in the next compile's LIVE COMMAND RECEIPTS with a stderr fence. Flip PENDING->banked on that receipt, then delete the canary from adhoc.txt.
[[[DIVIDER]]]
# - EARMARK: FAILED-PROBE RECEIPT (banked 2026-07-20, canary-witnessed same day): the `!` executor lands stderr-only failures as first-class Manifest receipts — "# NON-ZERO EXIT N" header, "(no stdout — stderr is the receipt)" placeholder, fenced tail-capped stderr. Witness: the deliberate canary `bash -c "echo canary-stderr >&2; exit 3"` surfaced in the next compile's LIVE COMMAND RECEIPTS exactly as specified. Method note: the fix was proven by MANUFACTURING a known failure and confirming the instrument displayed it — a QA pipeline is trusted only once it has demonstrably shown red when red was true.
[[[REPLACE]]]
Target: prompt_foo.py
[[[SEARCH]]]
# FAILED-PROBE RECEIPT (PENDING until canary witness): an
# all-stderr failure is a valid receipt, not a skip. Conviction:
[[[DIVIDER]]]
# FAILED-PROBE RECEIPT (banked 2026-07-20, canary-witnessed): an
# all-stderr failure is a valid receipt, not a skip. Conviction:
[[[REPLACE]]]
Actuation: patch, app, d, m per car; blast as the caboose. No flake.nix change — no exit/ndq.
4. PROMPT
Verify against THIS compile's receipts only. Gates in order: AST OK;
stack_tabs grep at 1; the flip pair reading 0 (PENDING gone) and 1
(canary-witnessed present).
Then read the live STACK receipt for the shared spreadsheet and deliver the
grid-vs-data verdict with numbers: report each tab's ACTUAL rows x cols
against the 1000x26 metadata allocation the old gauge showed. Confirm the
sentinel grammar rendered (START/END TAB fences, clickable #gid= URL under
each START) and whether the 10,000-cell budget admitted the full stack or
withheld it into the true-extent gauge — either outcome is a correct
defensive result; say which fired and why.
If the stack is green: the acquisition side of the adversarial TQM loop is
complete for Sheets. Dismount posture — VERIFY, BANK, name DANGLING
(sheets_readonly SKILL.md still describes the pre-STACK behavior; measure.py
still a sketch; evidence.py unstarted; wallet descriptor lane open), and
SEED the measure.py ride: using the ACTUAL column headers now visible in the
stacked receipt, draft the first metric-contract file and the minimal
measure.py (bounded TSV in via stdin/file, per-check PASS/FAIL lines out,
exit code as verdict, no connector knowledge). Emit the full patch train.
5. EXTERNAL DELIVERABLES
None this turn — the OAuth token is minted, the Sheets API is enabled in work-integrations-500916, and the sheet is already yours. Everything rides the repo lane.
One aside you asked for: yes, “bewarb” lands (Gravity Falls’ fake-out warning) — and the discipline here is the antidote to the flattery loop you’re naming. A model complimenting your architecture is unfalsifiable; a stacked receipt showing real cell extents against a claimed 1000×26 is not. Keep feeding it the second kind.
MikeLev.in: Wow, this is going to be interesting. It’s another nested sub-project but it’s an important one and applies broadly against future projects. This is a considerable unlock because so much of the Infinite Mutation Monster that is the abstract villein of our story speaks loudly through Google Sheets.
Probe:
$ git status
On branch main
Your branch is up to date with 'origin/main'.
Changes not staged for commit:
(use "git add <file>..." to update what will be committed)
(use "git restore <file>..." to discard changes in working directory)
modified: foo_files.py
no changes added to commit (use "git add" and/or "git commit -a")
(nix) pipulate $ blast
📝 Committing: chore(router): set AI-edit blast boundary (foo_files.py context curation)
[main f4e16cfb] chore(router): set AI-edit blast boundary (foo_files.py context curation)
1 file changed, 35 insertions(+), 35 deletions(-)
🚀 Pushing 1 commit(s) to remote...
Enumerating objects: 5, done.
Counting objects: 100% (5/5), done.
Delta compression using up to 48 threads
Compressing objects: 100% (3/3), done.
Writing objects: 100% (3/3), 562 bytes | 562.00 KiB/s, done.
Total 3 (delta 2), reused 0 (delta 0), pack-reused 0 (from 0)
remote: Resolving deltas: 100% (2/2), completed with 2 local objects.
To github.com:pipulate/pipulate.git
ac915ae9..f4e16cfb main -> main
$ git status
On branch main
Your branch is up to date with 'origin/main'.
nothing to commit, working tree clean
(nix) pipulate $ .venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho
.venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
grep -c "def stack_tabs" scripts/connectors/sheets.py
grep -c "PENDING: FAILED-PROBE RECEIPT" foo_files.py
grep -c "canary-witnessed" foo_files.py
# variant_url_performance_analysis [spreadsheetId: 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho] — 5 tab(s)
rows cols ~cells tab
1000 26 26,000 Summary WARNING: big — fetch with an explicit --range, not a bare --sheet
1000 26 26,000 Product Rollup WARNING: big — fetch with an explicit --range, not a bare --sheet
1000 26 26,000 Top Variant URLs WARNING: big — fetch with an explicit --range, not a bare --sheet
1000 26 26,000 Category Breakdown WARNING: big — fetch with an explicit --range, not a bare --sheet
1000 26 26,000 Watchlist (Declines) WARNING: big — fetch with an explicit --range, not a bare --sheet
# Next: python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --sheet "Summary" (first rows, capped by --max)
sheets.py AST: OK
0
1
0
(nix) pipulate $
Context:
# adhoc.txt _ _ _ ____ _ _ ___ ____ _
# / \ __| | | | | | ___ ___ / ___| | | |/ _ \| _ \| | Now we're cooking! I can't even fathom how useful this is moving forward.
# / _ \ / _` | | |_| |/ _ \ / __| | | | |_| | | | | |_) | | Everything is this. There is a universal love for spreadsheets among humans.
# / ___ \ (_| | | _ | (_) | (__ | |___| _ | |_| | __/|_| They wouldn't be as popular as they are if they didn't get so much right.
# /_/ \_\__,_| |_| |_|\___/ \___| \____|_| |_|\___/|_| (_) That doesn't keep them from being the primary agent of the Mutation Machine enemy.
# AD HOC CHOP: The Not-Managed-by-Git Safe-for-Client-Data place. Spreadsheets couldn't be more opposite than these zip cartridges we're about to be sending a lot from here.
# BIG STANDARD STUFF (Optionally comment out any)
! python scripts/articles/lsa.py -t 1 --reverse --fmt dated-slugs # <-- This is the "Rolling Pin" that gives a 40K foot airplane view.
~/repos/nixos/autognome.py # <-- Letting the AIs really understand my environment (the one of these which is not like the others)
foo_files.py # <-- This is the router, evolving book outline and the things you pin-up to produced the recursive self-improvement loops
prompt_foo.py # <-- Prompt Fu compiler, makes the very README for AGENTS-like payload you're reading right now, but it needs to be more like that
.gitattributes # <-- Model: understand that `nbstripout` and `jupytext` are both in play. Just talk the human through .ipynb patches.
.gitignore # <-- Creates "negative space" for sub-rep's to share parent environment and "snap" proprietary secret features into place.
init.lua # <-- Daily driver hot-keys that overlap with aliases in flake.nix
flake.nix # <-- Solves world's WRITE ONCE RUN ANYWHERE problem like Java never could. Also resolves the bootstrap paradox.
apply.py # <-- How can "Web UI" ChatBots edit your code? With this Aider-inspired Player Piano patch applier.
scripts/xp.py # <-- Transforms host OS copy-paste buffer player-piano music into context-payload.
scripts/ai.py # <-- How I constantly use local AI to write git commit messages with `m` alias.
cli.py # <-- Catch-all actuator for PyPI envs, Python anchoring, MCP tool-call (plus alternatives) and **kwargs like wrapping for CLI
# --- Under this line is were you paste what the AI gives you ---
# --- We call it context but it's really just the right-hand ---
# --- blast-radius of the "probes" to make this all science. ---
! .venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho
! .venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
! grep -c "def stack_tabs" scripts/connectors/sheets.py
! grep -c "PENDING: FAILED-PROBE RECEIPT" foo_files.py
! grep -c "canary-witnessed" foo_files.py
scripts/connectors/sheets.py
scripts/connectors/README.md
foo_files.py
Patches: [patch, app, d, m, patch, app, d, m…]
$ git status
On branch main
Your branch is up to date with 'origin/main'.
nothing to commit, working tree clean
(nix) pipulate $ patch
(nix) pipulate $ app
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'scripts/connectors/sheets.py'.
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'scripts/connectors/sheets.py'.
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'scripts/connectors/sheets.py'.
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'scripts/connectors/sheets.py'.
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'scripts/connectors/sheets.py'.
(nix) pipulate $ d
diff --git a/scripts/connectors/sheets.py b/scripts/connectors/sheets.py
index df03c8aa..a1bf5328 100644
--- a/scripts/connectors/sheets.py
+++ b/scripts/connectors/sheets.py
@@ -5,8 +5,9 @@ sheets.py — A Unix-philosophy gateway to Google Sheets for Prompt Fu context.
Golden-path modes, auto-detected from the single positional argument:
- python scripts/connectors/sheets.py # IDENTITY: usage + the service-account email to share Sheets with
- python scripts/connectors/sheets.py <URL-or-ID> # LIST: spreadsheet title + every tab with a rows x cols size gauge
+ python scripts/connectors/sheets.py # IDENTITY: OAuth wiring status; mints the token interactively
+ python scripts/connectors/sheets.py <URL-or-ID> # STACK: every tab's ACTUAL data rectangle, stacked vertically with payload-grammar sentinels + clickable per-tab #gid= URLs (over --budget: true-extent gauge instead)
+ python scripts/connectors/sheets.py <URL-or-ID> --list # LIST: metadata-only gauge (grid ALLOCATION, zero cell data fetched)
python scripts/connectors/sheets.py <URL-or-ID> --sheet Metrics # FETCH: first --max rows of one named tab
python scripts/connectors/sheets.py <URL-or-ID> --range "'Metrics'!A1:F50" # FETCH: explicit A1 range
@@ -21,11 +22,18 @@ spreadsheet coordinate — a full docs.google.com URL (the /d/<ID>/ segment is
extracted; a #gid= fragment selects that tab and triggers a bounded fetch of
it) or a bare spreadsheet ID.
-SIZE DEFENSE (context windows are finite): LIST mode always reports every
-tab's rows x cols x cells so an overflow is visible BEFORE fetching, and flags
-tabs too big for a bare --sheet pull. Fetches are row-bounded SERVER-side
-('{Tab}'!1:N) when only --sheet is given, and row-capped client-side by
--n/--max (default 25) in every mode, per THE PROBE ECONOMY RULE.
+SIZE DEFENSE (context windows are finite): STACK mode is governed by a TOTAL
+data-cell --budget (default 10,000); over budget nothing dumps — a true-extent
+gauge prints instead, which IS the drill-down map. --sheet fetches are
+row-bounded SERVER-side ('{Tab}'!1:N) and every --sheet/--range fetch is
+row-capped client-side by -n/--max (default 25), per THE PROBE ECONOMY RULE.
+
+GRID-VS-DATA (convicted 2026-07-20): metadata gridProperties report grid
+ALLOCATION (new tabs read 1000x26 whether they hold 3 rows or 900), while
+spreadsheets.values responses return the TRIMMED used rectangle — trailing
+empty rows/cols never cross the wire. Data extents therefore come from values
+responses (len of rows x max row width), never from metadata; and no Pandas
+is needed for acquisition — Pandas belongs downstream in measure.py.
Auth (oauth_token_file — the gmail.py pattern; the human's OWN Google account):
App identity: ~/.config/pipulate/credentials.json
@@ -220,6 +228,65 @@ def list_tabs(service, sid, gid, max_items):
f"--sheet \"{target}\" (first rows, capped by --max)")
+def stack_tabs(service, sid, fmt, budget):
+ """STACK mode (the bare-ID default): every tab's ACTUAL data rectangle,
+ stacked vertically with payload-grammar sentinels and a clickable #gid=
+ URL per tab. One metadata call resolves titles/gids; one values.batchGet
+ pulls every used rectangle. Governed by a total-cell budget: over budget,
+ nothing dumps — the true-extent gauge prints instead.
+ """
+ meta = service.spreadsheets().get(
+ spreadsheetId=sid,
+ fields='properties.title,sheets.properties'
+ ).execute()
+ title = meta.get('properties', {}).get('title', '(untitled)')
+ tabs = [t.get('properties', {}) for t in meta.get('sheets', [])]
+ if not tabs:
+ print(f"# {title} [spreadsheetId: {sid}] — no tabs")
+ return
+ resp = service.spreadsheets().values().batchGet(
+ spreadsheetId=sid,
+ ranges=["'" + p.get('title', '').replace("'", "''") + "'" for p in tabs],
+ majorDimension='ROWS'
+ ).execute()
+ value_ranges = resp.get('valueRanges', [])
+ extents = []
+ for p, vr in zip(tabs, value_ranges):
+ rows = vr.get('values', [])
+ n_rows = len(rows)
+ n_cols = max((len(r) for r in rows), default=0)
+ extents.append((p, rows, n_rows, n_cols))
+ total_cells = sum(r * c for _, _, r, c in extents)
+ base_url = f"https://docs.google.com/spreadsheets/d/{sid}/edit"
+
+ if total_cells > budget:
+ print(f"# {title} [spreadsheetId: {sid}] — {len(extents)} tab(s), "
+ f"{total_cells:,} data cells > budget {budget:,} — STACK withheld\n")
+ print(f"{'rows':>7} {'cols':>5} {'cells':>9} tab | tab URL")
+ for p, _, r, c in extents:
+ print(f"{r:>7} {c:>5} {r * c:>9,} {p.get('title', '?')} | "
+ f"{base_url}#gid={p.get('sheetId', 0)}")
+ print(f"\n# Next: python scripts/connectors/sheets.py {sid} "
+ "--sheet \"<Tab>\" (one bounded tab)")
+ print(f"# Or raise the ceiling: python scripts/connectors/sheets.py "
+ f"{sid} --budget {total_cells}")
+ return
+
+ print(f"# {title} [spreadsheetId: {sid}] — {len(extents)} tab(s), "
+ f"{total_cells:,} data cells (full stack)\n")
+ for p, rows, n_rows, n_cols in extents:
+ name = p.get('title', '?')
+ print(f'--- START: TAB "{name}" ({n_rows} rows x {n_cols} cols) ---')
+ print(f"# {base_url}#gid={p.get('sheetId', 0)}")
+ if rows:
+ _emit(rows, fmt)
+ else:
+ print("(empty tab)")
+ print(f'--- END: TAB "{name}" ---\n')
+ print(f"# Next: python scripts/connectors/sheets.py {sid} "
+ "--range \"'<Tab>'!A1:Z50\" --format json (one precise slab)")
+
+
def resolve_gid_title(service, sid, gid):
"""Map a URL's #gid= fragment to its tab title (None when not found)."""
meta = service.spreadsheets().get(
@@ -294,6 +361,10 @@ def main():
parser.add_argument('--format', choices=['tsv', 'json', 'markdown'],
default='tsv',
help='Output format (default: tsv — compact and diffable).')
+ parser.add_argument('--list', action='store_true',
+ help='Metadata-only tab gauge (grid allocation; zero cell data fetched).')
+ parser.add_argument('--budget', type=int, default=10000,
+ help='STACK-mode ceiling in TOTAL data cells (default: 10000).')
args = parser.parse_args()
if args.ref is None:
@@ -311,7 +382,10 @@ def main():
fetch_values(service, sid, title, None,
args.format, args.max)
return
- list_tabs(service, sid, gid, args.max)
+ if args.list:
+ list_tabs(service, sid, gid, args.max)
+ else:
+ stack_tabs(service, sid, args.format, args.budget)
else:
fetch_values(service, sid, args.sheet, args.cell_range,
args.format, args.max)
(nix) pipulate $ m
📝 Committing: feat: Introduce stack mode for sheets connector
[main 23489477] feat: Introduce stack mode for sheets connector
1 file changed, 82 insertions(+), 8 deletions(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'scripts/connectors/README.md'.
(nix) pipulate $ d
diff --git a/scripts/connectors/README.md b/scripts/connectors/README.md
index dd6980e8..b9cba21a 100644
--- a/scripts/connectors/README.md
+++ b/scripts/connectors/README.md
@@ -49,7 +49,7 @@ of these four.
- botify.py identity walk / org / org/project / BQL query (BOTIFY_API_TOKEN)
- confluence.py spaces / space pages / page id / CQL search (CONFLUENCE_* envs)
- gsc.py properties / top queries / raw searchanalytics JSON (service_account_file)
-- sheets.py identity / URL-or-ID tab list with size gauge / bounded --sheet and --range values (oauth_token_file, gmail pattern; own sheets_token.json with the Sheets readonly scope; identity mode prints project_id and mints the token interactively)
+- sheets.py identity / bare URL-or-ID STACKS every tab's actual data rectangle with sentinel separators and per-tab #gid= URLs, budget-governed / --list metadata gauge / bounded --sheet and --range values (oauth_token_file, gmail pattern; own sheets_token.json; data extents from values responses, never gridProperties)
## Minting a new connector
(nix) pipulate $ m
📝 Committing: chore: Update sheets.py documentation for connector functionality
[main 007383ec] chore: Update sheets.py documentation for connector functionality
1 file changed, 1 insertion(+), 1 deletion(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'foo_files.py'.
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'prompt_foo.py'.
(nix) pipulate $ d
diff --git a/foo_files.py b/foo_files.py
index 72b9be32..736bc239 100644
--- a/foo_files.py
+++ b/foo_files.py
@@ -1232,7 +1232,7 @@ scripts/xp.py # [1,981 tokens | 8,377 bytes]
# - ADVERSARIAL TQM LANE (opened 2026-07-20): measure.py is the second nail — consumes bounded connector stdout (sheets/gsc/botify TSV/JSON) plus an explicit metric-contract file, emits per-check PASS/FAIL lines (primary metric + adversarial countermeasurements: the easiest surviving false explanation, codified) with exit code as verdict, so QA verdicts themselves ride as `!` receipts. evidence.py (third nail) packages claim+inputs+results+hashes into the cartridge. Acquisition, judgment, packaging: three composable commands, never welded. First contract gets written against the sheets live receipt once the Sheets API gate clears.
# - EARMARK: AUTH-KIND RESIDUE (convicted 2026-07-20): sheets.py v1 inherited gsc's service_account plumbing by pattern-proximity, so the API-enablement toggle went to the right console page for the WRONG credential's project — SERVICE_DISABLED persisted post-enablement and only the human caught the identity-model mismatch. Auth kind is a USER-story decision, never nearest-neighbor: Pipulate humans own Google accounts -> oauth_token_file (gmail pattern, per-scope token files, NO sharing gate); unattended robots -> service_account_file. Every connector's identity mode must print its credential's project_id so wrong-project convictions take seconds, not console archaeology.
# - WALLET DESCRIPTOR LANE (opened 2026-07-20): one richly-annotated wallet (connectors.json today; eventually connectors.nix -> materialized JSON, blogs.nix pattern) where every credential carries a human-facing description — what it is, which Cloud project, which scopes, which connectors consume it, where the secret file lives, rotation notes. Names/paths/descriptions only, never secret values. The cure to auth opacity: left hand and right hand read the same illuminated page, and it stays joyful to touch.
-# - PENDING: FAILED-PROBE RECEIPT (patched 2026-07-20, unwitnessed): the `!` executor now lands stderr-only failures as first-class Manifest receipts instead of raising them into invisibility. Witness canary: `! bash -c "echo canary-stderr >&2; exit 3"` must appear in the next compile's LIVE COMMAND RECEIPTS with a stderr fence. Flip PENDING->banked on that receipt, then delete the canary from adhoc.txt.
+# - EARMARK: FAILED-PROBE RECEIPT (banked 2026-07-20, canary-witnessed same day): the `!` executor lands stderr-only failures as first-class Manifest receipts — "# NON-ZERO EXIT N" header, "(no stdout — stderr is the receipt)" placeholder, fenced tail-capped stderr. Witness: the deliberate canary `bash -c "echo canary-stderr >&2; exit 3"` surfaced in the next compile's LIVE COMMAND RECEIPTS exactly as specified. Method note: the fix was proven by MANUFACTURING a known failure and confirming the instrument displayed it — a QA pipeline is trusted only once it has demonstrably shown red when red was true.
# ============================================================================
# VIII. THE PAINTBOX (Unused Colors)
diff --git a/prompt_foo.py b/prompt_foo.py
index 721bb87c..f251ef39 100644
--- a/prompt_foo.py
+++ b/prompt_foo.py
@@ -1904,7 +1904,7 @@ def main():
continue
if proc.returncode != 0 and not cmd_stdout.strip() and not cmd_stderr.strip():
raise subprocess.CalledProcessError(proc.returncode, command_str, output=cmd_stdout, stderr=cmd_stderr)
- # FAILED-PROBE RECEIPT (PENDING until canary witness): an
+ # FAILED-PROBE RECEIPT (banked 2026-07-20, canary-witnessed): an
# all-stderr failure is a valid receipt, not a skip. Conviction:
# the 2026-07-20 SERVICE_DISABLED live LISTs vanished from the
# Manifest twice because empty stdout raised here; only the
(nix) pipulate $ m
📝 Committing: fix: earmark failed-probe receipt with canary witness
[main f3f53b74] fix: earmark failed-probe receipt with canary witness
2 files changed, 2 insertions(+), 2 deletions(-)
(nix) pipulate $ git push
Enumerating objects: 21, done.
Counting objects: 100% (21/21), done.
Delta compression using up to 48 threads
Compressing objects: 100% (14/14), done.
Writing objects: 100% (14/14), 3.47 KiB | 1.73 MiB/s, done.
Total 14 (delta 11), reused 0 (delta 0), pack-reused 0 (from 0)
remote: Resolving deltas: 100% (11/11), completed with 7 local objects.
To github.com:pipulate/pipulate.git
f4e16cfb..f3f53b74 main -> main
(nix) pipulate $
Wow, that was fun! And it was way less labor intensive than before. Hey Fable 5, analyze why. Make sure we make it a to-do item to specify which way we want it. Under what condition multiple patches should be bundled for storytelling purposes and the ease of the human, while still forcing them to learn and internalize the message of each prompt (or at least, hopefully).
I see nothing I’m supposed to do out-of-bounds so “Ad Hoc CHOP!”
Prompt:
Verify against THIS compile’s receipts only. Gates in order: AST OK; stack_tabs grep at 1; the flip pair reading 0 (PENDING gone) and 1 (canary-witnessed present).
Then read the live STACK receipt for the shared spreadsheet and deliver the grid-vs-data verdict with numbers: report each tab’s ACTUAL rows x cols against the 1000x26 metadata allocation the old gauge showed. Confirm the sentinel grammar rendered (START/END TAB fences, clickable #gid= URL under each START) and whether the 10,000-cell budget admitted the full stack or withheld it into the true-extent gauge — either outcome is a correct defensive result; say which fired and why.
If the stack is green: the acquisition side of the adversarial TQM loop is complete for Sheets. Dismount posture — VERIFY, BANK, name DANGLING (sheets_readonly SKILL.md still describes the pre-STACK behavior; measure.py still a sketch; evidence.py unstarted; wallet descriptor lane open), and SEED the measure.py ride: using the ACTUAL column headers now visible in the stacked receipt, draft the first metric-contract file and the minimal measure.py (bounded TSV in via stdin/file, per-check PASS/FAIL lines out, exit code as verdict, no connector knowledge). Emit the full patch train.
🎤
Fable 5 High: Context verified.
VERIFY — gates against THIS compile’s receipts only
All four gates are green in the Manifest’s LIVE COMMAND RECEIPTS:
- AST OK —
sheets.py AST: OKlanded clean. stack_tabsgrep = 1 — the STACK mode exists in the shipped file (predicted 0→1; the raw source in this compile confirms the function body verbatim).- PENDING grep = 0 — and note how it landed:
# NON-ZERO EXIT 1 (stdout preserved as receipt)above the0. That’s the banked FAILED-PROBE machinery demonstrating itself in the wild — grep’s exit-code-as-data signaling rode into the Manifest instead of vanishing. The instrument showed its header exactly when the condition was true. - canary-witnessed grep = 1 — the flip is in the constitution.
The grid-vs-data verdict, with numbers
The old gauge read every tab as 1000×26 (26,000 cells) and stamped all five with the overflow WARNING. The STACK receipt shows what actually crossed the wire:
| Tab | Allocation | Actual | % of allocation |
|---|---|---|---|
| Summary | 26,000 | 25×4 = 100 | 0.4% |
| Product Rollup | 26,000 | 928×8 = 7,424 | 28.6% |
| Top Variant URLs | 26,000 | 107×7 = 749 | 2.9% |
| Category Breakdown | 26,000 | 10×7 = 70 | 0.3% |
| Watchlist (Declines) | 26,000 | 29×6 = 174 | 0.7% |
| Total | 130,000 | 8,517 | 6.6% |
Allocation overstated reality by ~15×. Every tab the old gauge flagged as “too big to fetch” was in fact fetchable; the conviction written into the docstring (GRID-VS-DATA) is now backed by a receipt, not just reasoning.
Sentinel grammar: rendered. Five --- START: TAB "Name" (R rows x C cols) --- / --- END --- fences, each with its clickable #gid= URL on the line under the START (gids 1434002691, 1262714595, 1016176348, 1892780652, 2089517745). Anyone who opens the forwarded zip can click straight into the exact tab being quoted.
Budget verdict: the full stack was admitted — 8,517 < 10,000, so STACK fired rather than the gauge. Correct defensive outcome, but note the near-miss: Product Rollup alone is 7,424 cells. One more tab of that size and the same spreadsheet flips into gauge mode. That’s the budget doing its job — the gauge is the drill-down map when it fires — but worth knowing the headroom is only ~1,500 cells.
Why this turn was less labor-intensive (the analysis you asked for)
The cost of a ride is O(cars), not O(blocks). Last turn’s Car 1 packed five SEARCH/REPLACE blocks into one fenced payload — one patch, one app, five green checkmarks, one d, one m, one commit. The human loop ran three times for eight mutations across four files. Three other factors compounded: every SEARCH target was cut from raw source in this compile (zero exact-match failures, zero diagnostic retries), there were no out-of-band deliverables (everything rode the repo lane), and the probe block was copy-symmetric (paste once for hands, paste once for adhoc). The exact-match interlock still verified every landing individually — bundling reduced ceremony, not safety.
The unresolved design question is when bundling is right, which goes to the constitution as a todo (patched below): the proposed policy is one car = one commit story. Bundle blocks when they’re a single atomic change that deserves a single commit message (even across files, if splitting would be meaningless); split cars when the commits should teach separately — feature vs. docs vs. constitution flip — so m’s generated message and the human’s internalization track each lesson. You ratify or amend.
BANK
Graduating this turn, delivered as patches rather than paste-ready lines: the TQM lane line advances from “sketch” to “drafted, PENDING first receipt” (obeying the one-unwitnessed-turn allowance); the bundling-policy todo enters the constitution; measure.py and the first contract enter the router’s active palette.
DANGLING
Notebooks/.agents/skills/sheets_readonly/SKILL.mdstill describes pre-STACK behavior — signpost drift, one line each way, next chore car.evidence.py(third nail: claim+inputs+results+hashes into the cartridge) unstarted.- Wallet descriptor lane still open.
- Cross-tab reconciliation (Summary’s “+8,447 from new URLs” vs. Rollup arithmetic) is contract v2 — v1 below is deliberately single-table.
- Budget near-miss noted above; no action until a real spreadsheet trips it.
SEED — the measure.py ride
Design, decided: measure.py lives at scripts/ (it is not a connector — it judges any rectangle it’s handed, zero API knowledge), stdlib-only per the single-artifact spirit, reads TSV from stdin or a file, tolerates the connector’s # breadcrumbs and Sheets preamble rows by finding the header row rather than assuming row 1, splits data from the TOTAL footer, runs contract checks, prints PASS/FAIL lines, exits 0/1/2. The first contract is written against the ACTUAL headers in this compile’s stacked receipt (Product, Variant URLs tracked, New variant URLs (no prior data), Current Impressions, Diff. Impressions, Current Clicks, Diff. Clicks), and each check codifies an easiest-surviving-false-explanation: truncated export → row_count_min; broken aggregation → sum_matches_footer on all five numeric columns; logical impossibility → per-row new ≤ tracked; silently dropped totals → footer_exists. First live run is calibration: a FAIL may convict the contract rather than the data, and either verdict rides as a receipt (a nonzero exit now lands with the NON-ZERO EXIT header, as gate 3 just demonstrated).
1. PROBES
First is the AST gate for the new file; second predicts 0→1 for run_check; third is the BEFORE reading of the full loop — acquisition piped into judgment, only PASS/FAIL lines landing (bounded by construction). A nonzero exit on the third is a valid receipt, not a failure to run.
.venv/bin/python -c "import ast; ast.parse(open('scripts/measure.py').read()); print('measure.py AST: OK')"
grep -c "def run_check" scripts/measure.py
.venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --sheet "Product Rollup" --max 1000 | .venv/bin/python scripts/measure.py --contract scripts/contracts/variant_rollup_v1.json
2. NEXT CONTEXT
Deliberately NOT re-echoing the 54K-token full STACK — the receipt is banked; the measure pipe is the new evidence surface.
! .venv/bin/python -c "import ast; ast.parse(open('scripts/measure.py').read()); print('measure.py AST: OK')"
! grep -c "def run_check" scripts/measure.py
! .venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --sheet "Product Rollup" --max 1000 | .venv/bin/python scripts/measure.py --contract scripts/contracts/variant_rollup_v1.json
scripts/measure.py
scripts/contracts/variant_rollup_v1.json
scripts/connectors/README.md
foo_files.py
3. PATCHES
Car 1 — scripts/measure.py (new file; the judgment nail):
Target: scripts/measure.py
[[[WRITE_FILE]]]
#!/usr/bin/env python3
# scripts/measure.py
"""
measure.py — the judgment nail of the adversarial TQM loop.
Acquisition (connectors) -> JUDGMENT (this) -> packaging (evidence.py).
Consumes bounded TSV on stdin (or a file) plus an explicit metric-contract
JSON, and emits one PASS/FAIL line per check. Exit code is the verdict:
0 = every check passed, 1 = at least one FAIL, 2 = structural failure
(header not found, unreadable contract). Knows NOTHING about connectors,
Google, or HTTP — it judges whatever rectangle it is handed, which is what
lets its verdicts ride as `!` receipts in compiled context payloads.
Input tolerance (matched to connector TSV emission):
- lines starting with '#' are breadcrumbs/comments: skipped
- blank lines: skipped
- the header row is FOUND, not assumed: the first row containing every
column the contract names (Sheets tabs often carry title/preamble rows)
- data rows run from the header to the footer_label row (or EOF)
- numerics tolerate thousands separators and a trailing '%'
Contract format (JSON):
{
"name": "...",
"columns": ["Col A", "Col B"], # required; locates the header row
"footer_label": "TOTAL", # optional; matched on first cell
"checks": [
{"name": "...", "type": "footer_exists"},
{"name": "...", "type": "row_count_min", "value": 100},
{"name": "...", "type": "sum_matches_footer", "column": "Col B", "tolerance": 0},
{"name": "...", "type": "column_lte", "left": "Col A", "right": "Col B"}
]
}
Check types are adversarial countermeasurements made executable — each one
codifies the EASIEST surviving false explanation for a healthy-looking table:
truncated/stale export -> row_count_min; broken aggregation ->
sum_matches_footer; logical impossibility -> column_lte; silently dropped
totals -> footer_exists.
Usage:
python scripts/connectors/sheets.py <ID> --sheet "Product Rollup" --max 1000 \
| python scripts/measure.py --contract scripts/contracts/variant_rollup_v1.json
python scripts/measure.py --contract contract.json data.tsv
"""
import sys
import json
import argparse
def read_rows(stream):
rows = []
for line in stream:
line = line.rstrip('\n')
if not line.strip() or line.lstrip().startswith('#'):
continue
rows.append(line.split('\t'))
return rows
def to_num(value):
if value is None:
return None
s = str(value).strip().replace(',', '')
if s.endswith('%'):
s = s[:-1]
try:
return float(s)
except ValueError:
return None
def cell(row, idx):
return row[idx] if idx < len(row) else ''
def find_header(rows, columns):
for i, row in enumerate(rows):
if all(c in row for c in columns):
return i, {c: row.index(c) for c in columns}
return None, None
def split_data_footer(rows, start, footer_label):
data, footer = [], None
for row in rows[start:]:
first = cell(row, 0).strip()
if footer_label and first == footer_label:
footer = row
break
if first:
data.append(row)
return data, footer
def run_check(chk, data, footer, colmap):
kind = chk.get('type')
name = chk.get('name', str(kind))
if kind == 'footer_exists':
ok = footer is not None
return name, ok, ('footer row present' if ok else 'footer row missing')
if kind == 'row_count_min':
want = chk.get('value', 1)
ok = len(data) >= want
return name, ok, f'{len(data)} data rows (min {want})'
if kind == 'sum_matches_footer':
col = chk['column']
idx = colmap[col]
tol = chk.get('tolerance', 0)
total = sum(v for v in (to_num(cell(r, idx)) for r in data)
if v is not None)
target = to_num(cell(footer, idx)) if footer is not None else None
if target is None:
return name, False, f'{col}: footer value unreadable'
ok = abs(total - target) <= tol
return name, ok, f'{col}: sum {total:g} vs footer {target:g} (tol {tol})'
if kind == 'column_lte':
li, ri = colmap[chk['left']], colmap[chk['right']]
bad = 0
for r in data:
lv, rv = to_num(cell(r, li)), to_num(cell(r, ri))
if lv is not None and rv is not None and lv > rv:
bad += 1
ok = bad == 0
detail = (f"{chk['left']} <= {chk['right']}: "
f"{bad} violation(s) in {len(data)} rows")
return name, ok, detail
return name, False, f'unknown check type: {kind!r}'
def main():
parser = argparse.ArgumentParser(
description='Judge a TSV rectangle against a metric contract; '
'exit code is the verdict.')
parser.add_argument('data', nargs='?', default=None,
help='TSV file to judge (default: stdin).')
parser.add_argument('--contract', required=True,
help='Path to the metric-contract JSON.')
args = parser.parse_args()
try:
with open(args.contract, 'r', encoding='utf-8') as f:
contract = json.load(f)
except (OSError, json.JSONDecodeError) as e:
sys.stderr.write(f'measure.py: unreadable contract '
f'{args.contract}: {e}\n')
sys.exit(2)
if args.data:
with open(args.data, 'r', encoding='utf-8') as f:
rows = read_rows(f)
else:
rows = read_rows(sys.stdin)
columns = contract.get('columns', [])
header_i, colmap = find_header(rows, columns)
if header_i is None:
sys.stderr.write('measure.py: header row not found — contract '
'columns: ' + ', '.join(columns) + '\n')
sys.exit(2)
data, footer = split_data_footer(
rows, header_i + 1, contract.get('footer_label'))
checks = contract.get('checks', [])
failures = 0
print(f"# contract: {contract.get('name', args.contract)} — "
f"{len(data)} data row(s), "
f"footer {'found' if footer is not None else 'absent'}")
for chk in checks:
try:
name, ok, detail = run_check(chk, data, footer, colmap)
except KeyError as e:
name = chk.get('name', '?')
ok, detail = False, f'contract names unknown column {e}'
if not ok:
failures += 1
print(f"{'PASS' if ok else 'FAIL'} {name} — {detail}")
verdict = 'PASS' if failures == 0 else 'FAIL'
print(f'# verdict: {verdict} ({failures} failing check(s) '
f'of {len(checks)})')
sys.exit(0 if failures == 0 else 1)
if __name__ == '__main__':
main()
[[[END_WRITE_FILE]]]
Car 2 — the first metric contract, headers taken verbatim from the live STACK receipt:
Target: scripts/contracts/variant_rollup_v1.json
[[[WRITE_FILE]]]
{
"name": "variant_rollup_v1",
"description": "Internal-consistency contract for a GSC variant-URL product rollup tab (headers taken from the 2026-07-20 live STACK receipt). Each check codifies the easiest surviving false explanation for a healthy-looking table: truncated export, broken aggregation, logical impossibility, dropped totals.",
"columns": [
"Product",
"Variant URLs tracked",
"New variant URLs (no prior data)",
"Current Impressions",
"Diff. Impressions",
"Current Clicks",
"Diff. Clicks"
],
"footer_label": "TOTAL",
"checks": [
{"name": "total_row_present", "type": "footer_exists"},
{"name": "not_a_truncated_export", "type": "row_count_min", "value": 500},
{"name": "variant_url_counts_reconcile", "type": "sum_matches_footer", "column": "Variant URLs tracked", "tolerance": 0},
{"name": "new_url_counts_reconcile", "type": "sum_matches_footer", "column": "New variant URLs (no prior data)", "tolerance": 0},
{"name": "impressions_reconcile", "type": "sum_matches_footer", "column": "Current Impressions", "tolerance": 0},
{"name": "impression_deltas_reconcile", "type": "sum_matches_footer", "column": "Diff. Impressions", "tolerance": 0},
{"name": "clicks_reconcile", "type": "sum_matches_footer", "column": "Current Clicks", "tolerance": 0},
{"name": "click_deltas_reconcile", "type": "sum_matches_footer", "column": "Diff. Clicks", "tolerance": 0},
{"name": "new_lte_tracked_per_product", "type": "column_lte", "left": "New variant URLs (no prior data)", "right": "Variant URLs tracked"}
]
}
[[[END_WRITE_FILE]]]
Car 3 — foo_files.py: claim the new files, advance the TQM lane, bank the bundling-policy todo (three blocks, one router-curation story):
Target: foo_files.py
[[[SEARCH]]]
scripts/connectors/gsc.py
scripts/connectors/sheets.py
[[[DIVIDER]]]
scripts/connectors/gsc.py
scripts/connectors/sheets.py
# ADVERSARIAL TQM (acquisition -> judgment -> packaging)
scripts/measure.py
scripts/contracts/variant_rollup_v1.json
[[[REPLACE]]]
Target: foo_files.py
[[[SEARCH]]]
# - ADVERSARIAL TQM LANE (opened 2026-07-20): measure.py is the second nail — consumes bounded connector stdout (sheets/gsc/botify TSV/JSON) plus an explicit metric-contract file, emits per-check PASS/FAIL lines (primary metric + adversarial countermeasurements: the easiest surviving false explanation, codified) with exit code as verdict, so QA verdicts themselves ride as `!` receipts. evidence.py (third nail) packages claim+inputs+results+hashes into the cartridge. Acquisition, judgment, packaging: three composable commands, never welded. First contract gets written against the sheets live receipt once the Sheets API gate clears.
[[[DIVIDER]]]
# - ADVERSARIAL TQM LANE (measure.py v1 drafted 2026-07-20, PENDING first PASS/FAIL receipt): scripts/measure.py consumes bounded connector stdout (TSV via stdin/file) plus a metric-contract JSON (scripts/contracts/), emits per-check PASS/FAIL lines with exit code as verdict — verdicts ride as `!` receipts, and a nonzero exit lands via the banked FAILED-PROBE machinery. First contract: variant_rollup_v1.json, written against the 2026-07-20 live STACK receipt's actual headers. Flip PENDING->banked when the sheets|measure pipe receipt lands. evidence.py (third nail) still unstarted. Acquisition, judgment, packaging: three composable commands, never welded.
[[[REPLACE]]]
Target: foo_files.py
[[[SEARCH]]]
# - WALLET DESCRIPTOR LANE (opened 2026-07-20): one richly-annotated wallet
[[[DIVIDER]]]
# - PATCH CAR BUNDLING POLICY (proposed 2026-07-20, ratify or amend): a car = one commit story. Bundle multiple SEARCH/REPLACE blocks into one fenced payload when they form a single atomic change deserving a single commit message (even across files, when splitting is meaningless ceremony); split into separate cars when the commits should teach separately — feature vs docs vs constitution flip — so `m`'s generated message and the human's internalization track each lesson. Evidence: the 2026-07-20 five-block sheets.py car cut the human loop from O(blocks) to O(cars) with zero exact-match failures. Specify the final wording, then convert this line to a banked EARMARK.
# - WALLET DESCRIPTOR LANE (opened 2026-07-20): one richly-annotated wallet
[[[REPLACE]]]
Car 4 — connectors README: name the downstream siblings so nobody mints measure.py as a connector:
Target: scripts/connectors/README.md
[[[SEARCH]]]
## Minting a new connector
[[[DIVIDER]]]
## Downstream siblings (deliberately NOT connectors)
- scripts/measure.py judgment: TSV (stdin/file) + metric-contract JSON ->
PASS/FAIL lines, exit code as verdict; zero connector
or API knowledge, so verdicts ride as `!` receipts
- scripts/contracts/ metric-contract JSON files; each check codifies the
easiest surviving false explanation for a
healthy-looking table
## Minting a new connector
[[[REPLACE]]]
Actuation: patch, app, d, m per car; blast as the caboose.
4. PROMPT
Verify against THIS compile's receipts only. Gates in order: measure.py AST
OK; run_check grep at 1; then the pipe receipt — sheets "Product Rollup"
--max 1000 into measure.py with variant_rollup_v1.json.
Deliver the contract verdict with numbers: every PASS/FAIL line, and for any
FAIL adjudicate explicitly whether it convicts the DATA (a real
inconsistency in the sheet) or the CONTRACT (a calibration miss — footer
alignment, numeric parsing, a preamble row the header-finder swallowed).
Either way the receipt is the point: this is the first time a QA verdict
itself rides the compile lane, and a NON-ZERO EXIT header on the pipe is the
banked failed-probe machinery working, not a malfunction.
If the pipe is green: flip the ADVERSARIAL TQM LANE line PENDING->banked
with the receipt cited, ratify or amend the PATCH CAR BUNDLING POLICY into a
final EARMARK wording, and SEED the next ride — choose between evidence.py
(third nail: claim+inputs+results+hashes into the cartridge) and the
sheets_readonly SKILL.md signpost sync, justify the pick by leverage, and
emit the full patch train for it.
5. EXTERNAL DELIVERABLES
None — the token is minted, the API is enabled, and everything this turn rides the repo lane.
One closing note on your Mutation Monster framing: the STACK receipt is the counter-artifact. A spreadsheet mutates silently; a stacked, sentinel-fenced, gid-linked, cell-counted snapshot inside a hash-stamped cartridge is that spreadsheet pinned at a moment, and measure.py is about to make the pin adversarial. That’s the whole loop: the thing humans love about sheets stays, and the thing that makes them the enemy gets a receipt.
MikeLev.in: Wow! Okay, correct, correct. However, I’m still a bit confused and I don’t know what to ratify or amend. We’re preparing to dismount the worm. Forget what the AI says compelling you along. Feel in your gut where the project is going. You’re the carriage coachman. AI is the horse. It’ll lead you if you let it. Horses have very good instincts. However, they haven’t been through it dozens of times before as a human knowing right where the pain-points are.
Here’s what my gut tells me. We have this symmetrical thing where on the left-hand side there’s the spreadsheet. It gives us a list of URLs. Those URLs can be used to make API-calls which the models is going to want to do against the GSC API directly, but we’re using the RealKeywords feature of Botify because we’re going to have access to that across every client we use this on and we do pull from GSC a lot to keep it updated, even doing the Google BigQuery bulk export trick where their GA account allows, so it’s good GSC data in Botify.
Hmmm. Let’s think. There will be a part of this project later on which is really about the Botify API and that RealKeywords searchconsole stuff somewhere in this vast tree we might have to walk or sniff the CDP wire to really figure out, but we’re deferring that for now. We’ll get to that. That’ll probably be the next article. But right now, it’s all about mapping. This has to be incredibly easy and induce an “Ah-Ha!” moment in those folks who love their mutation machines.
What do I mean by this?
I mean in a mapping one side will be a stable target per the API. We can find it with walking the tree of the OpenAPI Swagger feed. We can find it with MCP capability requests. We can find it like I said by sniffing the wire. There’s pretty much no way we can’t find it. And once we do, it’s fixed.
On the other side of the mapping is a riddle. What did they call stuff? What are those column-labels called? Is it just one rectangular area per tab or is there “just have to know” format changing along the way? Are the using the top-line for the column names? If not, then where are they? And what are they putting in that top row? Are they merging columns for aesthetic appearance in ways and place that are going to bite us? Are there embedded formulas that likewise might bite us? How do we make this all not a bloody mess? What’s the simplest possible solution so we can have a talk with them about:
Architectural Strategy for Future-Proof Mapping
- What row contains the column labels? If that question can’t be answered straight forward per tab you’ve got a much bigger problem.
- Which column has the data you want to make the API-call from? I know you’re going to drag the column order and change the name on me so we’ll use both numerical index of the column and the column name, making it case insensitive and maybe handling punctuation, abbreviation, hyphenation, parenthesis and whatever else.
- If there’s multiple columns we’re going to do this Q/A check on, what are
they? Maybe we have some very well understood JSON-like thing that they
simply cannot not understand if they’re in the business like:
map-gsheet-columns-to-api-fields-for-qa.json. I know that’s long, but this is the mentality. Everything will go wrong. Murphy’s at the door.
With such a mapping in hand, everything becomes much, much easier. I know this from doing this kind of work a lot. Never automate against spreadsheets unless you’re doing it with a mapper like this you can toss back at the person you’re doing the work for so they can internalize your pain for themselves and start thinking about the issues of a mutation machine versus direct from-the-horse’s mouth good API data.
This is where I’m steering the Horse (always upper-case because of respect) even though I’m following through on this stuff before jumping off the Worm, which I do because it could raise the quality of the ultimate product. Whoa looking at them I’m changing my mind and will tell why in the “fields” doing as much as I still can. That includes the probe.
Probe:
$ git status
On branch main
Your branch is up to date with 'origin/main'.
nothing to commit, working tree clean
(nix) pipulate $ ! .venv/bin/python -c "import ast; ast.parse(open('scripts/measure.py').read()); print('measure.py AST: OK')"
! grep -c "def run_check" scripts/measure.py
! .venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --sheet "Product Rollup" --max 1000 | .venv/bin/python scripts/measure.py --contract scripts/contracts/variant_rollup_v1.json
scripts/measure.py
scripts/contracts/variant_rollup_v1.json
scripts/connectors/README.md
foo_files.py
Traceback (most recent call last):
File "<string>", line 1, in <module>
FileNotFoundError: [Errno 2] No such file or directory: 'scripts/measure.py'
grep: scripts/measure.py: No such file or directory
.venv/bin/python: can't open file '/home/mike/repos/pipulate/scripts/measure.py': [Errno 2] No such file or directory
Traceback (most recent call last):
File "/home/mike/repos/pipulate/scripts/connectors/sheets.py", line 403, in <module>
main()
File "/home/mike/repos/pipulate/scripts/connectors/sheets.py", line 390, in main
fetch_values(service, sid, args.sheet, args.cell_range,
File "/home/mike/repos/pipulate/scripts/connectors/sheets.py", line 340, in fetch_values
_emit(rows, fmt)
File "/home/mike/repos/pipulate/scripts/connectors/sheets.py", line 316, in _emit
print('\t'.join(
BrokenPipeError: [Errno 32] Broken pipe
bash: scripts/measure.py: No such file or directory
bash: scripts/contracts/variant_rollup_v1.json: No such file or directory
bash: scripts/connectors/README.md: Permission denied
foo_files.py: command not found
(nix) pipulate $
Okay, I’ll set the context too.
Context:
# adhoc.txt _ _ _ ____ _ _ ___ ____ _
# / \ __| | | | | | ___ ___ / ___| | | |/ _ \| _ \| | Jumping off the Worm!
# / _ \ / _` | | |_| |/ _ \ / __| | | | |_| | | | | |_) | |
# / ___ \ (_| | | _ | (_) | (__ | |___| _ | |_| | __/|_|
# /_/ \_\__,_| |_| |_|\___/ \___| \____|_| |_|\___/|_| (_)
# AD HOC CHOP: The Not-Managed-by-Git Safe-for-Client-Data place.
# BIG STANDARD STUFF (Optionally comment out any)
! python scripts/articles/lsa.py -t 1 --reverse --fmt dated-slugs # <-- This is the "Rolling Pin" that gives a 40K foot airplane view.
~/repos/nixos/autognome.py # <-- Letting the AIs really understand my environment (the one of these which is not like the others)
foo_files.py # <-- This is the router, evolving book outline and the things you pin-up to produced the recursive self-improvement loops
prompt_foo.py # <-- Prompt Fu compiler, makes the very README for AGENTS-like payload you're reading right now, but it needs to be more like that
.gitattributes # <-- Model: understand that `nbstripout` and `jupytext` are both in play. Just talk the human through .ipynb patches.
.gitignore # <-- Creates "negative space" for sub-rep's to share parent environment and "snap" proprietary secret features into place.
init.lua # <-- Daily driver hot-keys that overlap with aliases in flake.nix
flake.nix # <-- Solves world's WRITE ONCE RUN ANYWHERE problem like Java never could. Also resolves the bootstrap paradox.
apply.py # <-- How can "Web UI" ChatBots edit your code? With this Aider-inspired Player Piano patch applier.
scripts/xp.py # <-- Transforms host OS copy-paste buffer player-piano music into context-payload.
scripts/ai.py # <-- How I constantly use local AI to write git commit messages with `m` alias.
cli.py # <-- Catch-all actuator for PyPI envs, Python anchoring, MCP tool-call (plus alternatives) and **kwargs like wrapping for CLI
# --- Under this line is were you paste what the AI gives you ---
# --- We call it context but it's really just the right-hand ---
# --- blast-radius of the "probes" to make this all science. ---
! .venv/bin/python -c "import ast; ast.parse(open('scripts/measure.py').read()); print('measure.py AST: OK')"
! grep -c "def run_check" scripts/measure.py
! .venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --sheet "Product Rollup" --max 1000 | .venv/bin/python scripts/measure.py --contract scripts/contracts/variant_rollup_v1.json
scripts/measure.py
scripts/contracts/variant_rollup_v1.json
scripts/connectors/README.md
foo_files.py
Patches: [patch, app, d, m, patch, app, d, m…]
But no way I’m taking that patch because there’s going to be a lot of changing
of minds based on stuff. Hmmm. Let me throw the Botify Bootcamp API document in
there and the True Schema Discoverer. Oh, and the Botify Connector and the
connectors README. Yeah, that’ll do it. Now go out with a bang correcting all
your strategic direction so that when we use this article to pick up our
traction again with latest when the rubber hits the road, we peel out fast.
Nope
Prompt:
Hop off the worm. This ride’s stated goal is reached — dismount. Answer all four beats, briefly:
- VERIFY: restate the goal from the top of this article and confirm (or deny) it was met, citing THIS compile’s receipts, not memory.
- BANK: name everything that graduates — rule, earmark, todo, pin — as exact paste-ready lines, plus the exact lines to delete.
- DANGLING: what carries forward unbanked? One line each, no essays.
- SEED: end with the adhoc.txt lines (and TODO_SLUGS if narrative context is needed) for the next ride’s first compile.
Stick the dismount and make it clear that this article is done.
🎤
Fable 5 High: Context verified.
The dismount is stuck against THIS compile’s receipts. Four beats, then the flag drops.
1. VERIFY
The goal at the top of this article: step across the tabs, find the true X/Y data boundaries, pick the cheapest lightweight mechanism, and make the bare-ID default “full content of every tab stacked vertically” with separator sentinels and clickable tab URLs, in a format easy for the model — TSV.
Met, per this compile’s Manifest receipts:
- The live STACK receipt shows exactly the designed artifact: 5 tabs, 8,517 data cells (full stack) admitted under the 10,000 budget, each tab fenced
--- START: TAB "Name" (R rows x C cols) ---with its clickable#gid=URL on the next line, TSV bodies. Actual extents: Summary 25×4, Product Rollup 928×8, Top Variant URLs 107×7, Category Breakdown 10×7, Watchlist 29×6 — against a metadata allocation that claimed 1000×26 for all five. The grid-vs-data conviction is now receipt-backed, ~15× overstatement measured. sheets.py AST: OK;grep -c "def stack_tabs"= 1 (predicted 0→1, delta exact).- The flip pair: PENDING grep = 0 — and it landed under a
# NON-ZERO EXIT 1 (stdout preserved as receipt)header, meaning the banked FAILED-PROBE machinery demonstrated itself while witnessing its own flip. canary-witnessed grep = 1. - The measure.py leg was not met — correctly. The train was emitted, the coachman refused it, nothing was applied (measure.py is absent from this compile’s Manifest and Codebase, consistent with your hand-run FileNotFoundError). Per the Ride Contract, that’s not a miss; that’s declining scope creep at the moment of arrival. The stated goal was the STACK, and the STACK is green.
2. BANK
One banking car. It deletes the stale TQM line and lands the redirect, the mapper lane, the bundling todo (worded so ratifying = deleting one word later), and the veto earmark.
Target: foo_files.py
[[[SEARCH]]]
# - ADVERSARIAL TQM LANE (opened 2026-07-20): measure.py is the second nail — consumes bounded connector stdout (sheets/gsc/botify TSV/JSON) plus an explicit metric-contract file, emits per-check PASS/FAIL lines (primary metric + adversarial countermeasurements: the easiest surviving false explanation, codified) with exit code as verdict, so QA verdicts themselves ride as `!` receipts. evidence.py (third nail) packages claim+inputs+results+hashes into the cartridge. Acquisition, judgment, packaging: three composable commands, never welded. First contract gets written against the sheets live receipt once the Sheets API gate clears.
[[[DIVIDER]]]
# - ADVERSARIAL TQM LANE (reordered 2026-07-20 at dismount): acquisition (connectors) -> MAPPING (see MAPPER LANE) -> judgment (measure.py) -> packaging (evidence.py). measure.py v1 + variant_rollup_v1.json were drafted in the sheets-STACK article transcript but deliberately NOT applied — judgment against unmapped columns is judgment against a riddle. Recover the drafts from that article once a mapper exists. evidence.py unstarted.
# - MAPPER LANE (opened 2026-07-20, coachman-steered): before ANY automation or QA against a client sheet, mint the mapping artifact — sheet_map.json (working name; map-gsheet-columns-to-api-fields-for-qa.json IS the mentality). One entry per tab answering exactly three questions: (1) which row holds the column labels — if unanswerable per tab, the client has the bigger problem and the mapper is the conversation you hand back; (2) which column feeds the API call, addressed by BOTH numeric index and fuzzy name (case-insensitive; tolerate punctuation, abbreviation, hyphenation, parens); (3) which columns get QA'd against which API fields. Left side of the map = the Mutation Machine (riddle); right side = the API (fixed, discoverable three ways: OpenAPI swagger tree walk, datamodel/datasets endpoints via imports/botify/true_schema_discoverer.py, CDP wire sniff). Stable side for this campaign: Botify RealKeywords/search_console, not raw GSC — cross-client, BigQuery-fed, one auth. The mapper is a DELIVERABLE, not plumbing: toss it back at the sheet owner so they internalize the pain. Never automate against a spreadsheet without one.
# - EARMARK: THE COACHMAN'S VETO (banked 2026-07-20): an emitted patch train is a PROPOSAL until the human runs `app`; refusing an entire train costs nothing (no dead cars — nothing was applied) and is the correct move when the strategy beneath it shifts. The Horse has instincts; the coachman has been down the road. Witness: the measure.py train of 2026-07-20, refused at the platform, redirected into the MAPPER LANE.
# - TODO (ratify wording, then retitle EARMARK): PATCH CAR BUNDLING — a car = one commit story. Bundle blocks into one fenced payload when they form one atomic change deserving one commit message, even across files; split cars when the commits should teach separately (feature vs docs vs constitution flip), so `m`'s message and the human's internalization track each lesson. Evidence: the 2026-07-20 five-block sheets.py car cut the human loop from O(blocks) to O(cars) with zero exact-match failures.
[[[REPLACE]]]
Lines to delete: only the one in the SEARCH block — the flip pair already landed last train, nothing else is stale.
3. DANGLING
- measure.py + variant_rollup_v1.json: exist only as drafts in this article’s transcript; recoverable when the mapper lands.
Notebooks/.agents/skills/sheets_readonly/SKILL.mdstill describes pre-STACK behavior — signpost drift, one chore car.- evidence.py (third nail) unstarted.
- Wallet descriptor lane still open.
- Budget near-miss: Product Rollup alone is 7,424 of 10,000 cells — no action until a real sheet trips the gauge.
- Cross-tab reconciliation (Summary’s claimed +8,447 vs Rollup arithmetic) — contract v2 territory, post-mapper.
- Ruff W292 (missing trailing newline) in true_schema_discoverer.py — trivial, sweep it into any Botify-side car.
- Botify RealKeywords field-path discovery (walk, request, or sniff the wire) — that’s the next article, not a leak from this one.
4. SEED
Next ride: the stable side of the map. Identity walk first (BEFORE reading of coordinates), then the schema tree. Compile-lane caution applies — the identity walk prints client org/project slugs, so confirm pii_substitutions coverage before this rides to a cloud window. No TODO_SLUGS needed; this article carries its own drafts and the bootcamp doc is the narrative.
! .venv/bin/python scripts/connectors/botify.py
scripts/connectors/botify.py
scripts/connectors/README.md
imports/botify/true_schema_discoverer.py
scripts/botify/botify_api_bootcamp.md
scripts/connectors/sheets.py
foo_files.py
# next turn, once org/project are confirmed sanitized:
# ! .venv/bin/python scripts/connectors/botify.py <org>/<project>
Dismount stuck. This article is done. The Sheets acquisition instrument is built, budget-governed, sentinel-fenced, and receipt-proven; the judgment nail was drawn, inspected, and holstered on the coachman’s call; and the next ride opens on the fixed side of the map — where the API can’t rename its columns on you.
MikeLev.in: Okay, one update:
(nix) pipulate $ patch
(nix) pipulate $ app
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'foo_files.py'.
(nix) pipulate $ d
diff --git a/foo_files.py b/foo_files.py
index 736bc239..8c5132ae 100644
--- a/foo_files.py
+++ b/foo_files.py
@@ -1229,7 +1229,10 @@ scripts/xp.py # [1,981 tokens | 8,377 bytes]
# - EARMARK: CACHE-CHURN EXCLUSION PRINCIPLE (banked 2026-07-20): a hash-gated backup reduces writes only if its manifest excludes always-churning files; token_cache.json/fm_cache.json carrying the compile's own mtime is the conviction.
# - SECRET_TRIPWIRES repopulation (currently []): derive patterns from the vault manifest's credential FORMATS (PEM private-key header, "refresh_token", Google client_secret shape, api_key.txt's short-token shape). One inventory, two polarities: bank in the vault, block in payloads.
# - EARMARK: TWO-GATE 403 DIAGNOSIS (banked 2026-07-20): a Google service-account 403 has two independent causes that clear IN ORDER — SERVICE_DISABLED (API toggle in the key's Cloud project; once per API per project, forever) then PERMISSION_DENIED (resource not shared with client_email; once per document). Conviction: sheets.py's first live LIST died on SERVICE_DISABLED for the key's project while sharing remained untested. Auth is never the blocker when it fits a pattern already on the shelf: four wallet auth kinds cover every connector so far, and each new OAuth is a wallet-hygiene rep, not a delay.
-# - ADVERSARIAL TQM LANE (opened 2026-07-20): measure.py is the second nail — consumes bounded connector stdout (sheets/gsc/botify TSV/JSON) plus an explicit metric-contract file, emits per-check PASS/FAIL lines (primary metric + adversarial countermeasurements: the easiest surviving false explanation, codified) with exit code as verdict, so QA verdicts themselves ride as `!` receipts. evidence.py (third nail) packages claim+inputs+results+hashes into the cartridge. Acquisition, judgment, packaging: three composable commands, never welded. First contract gets written against the sheets live receipt once the Sheets API gate clears.
+# - ADVERSARIAL TQM LANE (reordered 2026-07-20 at dismount): acquisition (connectors) -> MAPPING (see MAPPER LANE) -> judgment (measure.py) -> packaging (evidence.py). measure.py v1 + variant_rollup_v1.json were drafted in the sheets-STACK article transcript but deliberately NOT applied — judgment against unmapped columns is judgment against a riddle. Recover the drafts from that article once a mapper exists. evidence.py unstarted.
+# - MAPPER LANE (opened 2026-07-20, coachman-steered): before ANY automation or QA against a client sheet, mint the mapping artifact — sheet_map.json (working name; map-gsheet-columns-to-api-fields-for-qa.json IS the mentality). One entry per tab answering exactly three questions: (1) which row holds the column labels — if unanswerable per tab, the client has the bigger problem and the mapper is the conversation you hand back; (2) which column feeds the API call, addressed by BOTH numeric index and fuzzy name (case-insensitive; tolerate punctuation, abbreviation, hyphenation, parens); (3) which columns get QA'd against which API fields. Left side of the map = the Mutation Machine (riddle); right side = the API (fixed, discoverable three ways: OpenAPI swagger tree walk, datamodel/datasets endpoints via imports/botify/true_schema_discoverer.py, CDP wire sniff). Stable side for this campaign: Botify RealKeywords/search_console, not raw GSC — cross-client, BigQuery-fed, one auth. The mapper is a DELIVERABLE, not plumbing: toss it back at the sheet owner so they internalize the pain. Never automate against a spreadsheet without one.
+# - EARMARK: THE COACHMAN'S VETO (banked 2026-07-20): an emitted patch train is a PROPOSAL until the human runs `app`; refusing an entire train costs nothing (no dead cars — nothing was applied) and is the correct move when the strategy beneath it shifts. The Horse has instincts; the coachman has been down the road. Witness: the measure.py train of 2026-07-20, refused at the platform, redirected into the MAPPER LANE.
+# - TODO (ratify wording, then retitle EARMARK): PATCH CAR BUNDLING — a car = one commit story. Bundle blocks into one fenced payload when they form one atomic change deserving one commit message, even across files; split cars when the commits should teach separately (feature vs docs vs constitution flip), so `m`'s message and the human's internalization track each lesson. Evidence: the 2026-07-20 five-block sheets.py car cut the human loop from O(blocks) to O(cars) with zero exact-match failures.
# - EARMARK: AUTH-KIND RESIDUE (convicted 2026-07-20): sheets.py v1 inherited gsc's service_account plumbing by pattern-proximity, so the API-enablement toggle went to the right console page for the WRONG credential's project — SERVICE_DISABLED persisted post-enablement and only the human caught the identity-model mismatch. Auth kind is a USER-story decision, never nearest-neighbor: Pipulate humans own Google accounts -> oauth_token_file (gmail pattern, per-scope token files, NO sharing gate); unattended robots -> service_account_file. Every connector's identity mode must print its credential's project_id so wrong-project convictions take seconds, not console archaeology.
# - WALLET DESCRIPTOR LANE (opened 2026-07-20): one richly-annotated wallet (connectors.json today; eventually connectors.nix -> materialized JSON, blogs.nix pattern) where every credential carries a human-facing description — what it is, which Cloud project, which scopes, which connectors consume it, where the secret file lives, rotation notes. Names/paths/descriptions only, never secret values. The cure to auth opacity: left hand and right hand read the same illuminated page, and it stays joyful to touch.
# - EARMARK: FAILED-PROBE RECEIPT (banked 2026-07-20, canary-witnessed same day): the `!` executor lands stderr-only failures as first-class Manifest receipts — "# NON-ZERO EXIT N" header, "(no stdout — stderr is the receipt)" placeholder, fenced tail-capped stderr. Witness: the deliberate canary `bash -c "echo canary-stderr >&2; exit 3"` surfaced in the next compile's LIVE COMMAND RECEIPTS exactly as specified. Method note: the fix was proven by MANUFACTURING a known failure and confirming the instrument displayed it — a QA pipeline is trusted only once it has demonstrably shown red when red was true.
(nix) pipulate $ m
📝 Committing: chore: Refactor: Introduce adversarial tqm lane and mapper lane definitions, establish key principles and terminology.
[main 51055a8b] chore: Refactor: Introduce adversarial tqm lane and mapper lane definitions, establish key principles and terminology.
1 file changed, 4 insertions(+), 1 deletion(-)
(nix) pipulate $ git push
Enumerating objects: 5, done.
Counting objects: 100% (5/5), done.
Delta compression using up to 48 threads
Compressing objects: 100% (3/3), done.
Writing objects: 100% (3/3), 1.72 KiB | 881.00 KiB/s, done.
Total 3 (delta 2), reused 0 (delta 0), pack-reused 0 (from 0)
remote: Resolving deltas: 100% (2/2), completed with 2 local objects.
To github.com:pipulate/pipulate.git
f3f53b74..51055a8b main -> main
(nix) pipulate $
Pshwew! Okay, we are staged very well for the next step. I’m going to need some clarification between measure.py and evidence.py, but my plan is to use the context that was just provided to start the next article immediately peeling out like a speed demon.
I’m about to publish this on the Botify Confluence wiki and I need a good introduction for my coworkers. Don’t be condescending or belittling. Be brief. Be neutral. Be dry. Like one of those NPR ladies you can tell are emotionally holding holding back their excitement about a topic because New Englanders emulate that British Victorian stiff upper lip control thing like a properly civilized person does. In TL;DR form. I’ll copy/paste what you produce to the top of the article. But don’t talk about what’s happening in the next article or the forward-looking issues at the end. Focus on what the reader can expect to find in this article. What happened right here in this article and what can they expect?
Book Analysis
Ai Editorial Take
What strikes me here is the realization that the spreadsheet isn’t just a data source—it’s a ‘Mutation Machine.’ The most interesting angle is the coachman’s veto; the willingness to reject a ready-to-run code patch because the conceptual map is missing is a high-level maturity indicator in automated development. It demonstrates that the pipeline is secondary to the definition of truth.
🐦 X.com Promo Tweet
Stop treating Google Sheets as a black box. Learn how to convert volatile spreadsheet tabs into sentinel-fenced, static data artifacts for reliable AI ingestion. Engineering reproducibility one TSV at a time: https://mikelev.in/futureproof/stack-and-sentinel-sheet-acquisition/ #DataEngineering #AIWorkflows #Pipulate
Title Brainstorm
- Title Option: The Stack and the Sentinel: Engineering Deterministic Sheet Acquisition
- Filename:
stack-and-sentinel-sheet-acquisition.md - Rationale: Captures the technical mechanism (Stack) and the reliability feature (Sentinel) while maintaining the architectural tone.
- Filename:
- Title Option: Beyond the Mutation Machine: Pinning Spreadsheet Data for AI
- Filename:
beyond-mutation-machine.md - Rationale: Highlights the transition from volatile spreadsheets to static, reliable artifacts.
- Filename:
- Title Option: Deterministic Sheet Extraction: Replacing Riddles with Artifacts
- Filename:
deterministic-sheet-extraction.md - Rationale: Focuses on the shift from guessing column structures to using explicit mapping.
- Filename:
Content Potential And Polish
- Core Strengths:
- Strong use of ‘Failure as a Receipt’ logic.
- Clear distinction between metadata grid allocation and actual data extents.
- Pragmatic approach to batching and budget-governed processing.
- Suggestions For Polish:
- Ensure the distinction between the ‘measure.py’ draft and the current workflow is clear to avoid reader confusion regarding the final state of the repository.
- Consider expanding on the ‘Mapper’ concept in future entries to provide concrete examples of the JSON mapping schema.
Next Step Prompts
- Draft a JSON schema for ‘map-gsheet-columns-to-api-fields-for-qa.json’ based on the product rollup tab headers.
- Explore the integration between ‘true_schema_discoverer.py’ and the mapping artifact to automate the right-hand side of the correspondence.