Automating Truth: Engineering Adversarial QA Pipelines with Google Sheets
Setting the Stage: Context for the Curious Book Reader
This entry chronicles the construction of a Unix-philosophy connector for Google Sheets, marking the next milestone in our evolving Quality Assurance pipeline. We transition from service-account complexity to durable OAuth-based data acquisition, establishing a robust, falsifiable method for verifying client deliverables against live API sources. It is an important case study in how to maintain deterministic control in the Age of AI.
Technical Journal Entry Begins
🔗 Verified Pipulate Commits:
TL;DR: This is a working session log documenting the construction of a small command-line connector that pulls Google Sheets data into AI context payloads. Along the way it covers: choosing OAuth over service accounts for user-owned tooling, diagnosing a wrong-project 403 through iterative probes, defensive output bounding so large spreadsheets don’t overflow a context window, and a fix to the compile pipeline so that failed probes surface as evidence rather than disappearing. The larger aim is a quality-assurance loop for client deliverables — spreadsheet claims on one side, API-verified data (Botify, GSC) on the other, with automated pass/fail assertions between them. The methodology throughout is deliberately falsificationist: probes are designed so that failures are informative, and each failure gets converted into a documented rule. The transcript is verbatim, including the missteps, because the missteps are the point.
MikeLev.in: Alright, it’s time to enter the realm of adversarial quality assurance. But that word adversarial is meant in the most positive most truth-seeking way how the word is used in the AI industry for things like GAN but for plain old boring Q/A. I can’t think of anything more fun and I mean that sincerely. So another thing about this adversarial thing is that it pushes the models into a certain type of Chaos Monkey read-teaming assistance that I need for this project. If we find that the delivered product is not doing what it says or delivering as promised, we improve it! (TQM).
The Adversarial QA Philosophy
The idea here is to do the right thing by the customer or client every single time, delivering superior product at a lower cost and more quickly than anything else. The golden “Cost, Quality, Speed” golden ratio of the service economy shall be asymmetrically attacked and violated, made entirely lopsided to the client’s favor because of what we do here. Does that make sense?
Let’s create the premise that some technical project you do that has some equally technical data-system-pullable probably API-driven measurements of metrics so that we can reduce whatever we’re talking about into a finite, bounded, deterministic numbers game. Yes-or-No truths. Falsifiable claims in the most Popperesque ways, but for business.
And so starts the chapter of Pipulate that I’m not really sure how to label but W. Edwards Deming TQM assisted by the twist-locked zipped packages my system produces comes to mind. I’m not sure if we need a 30-and-3 exercise on this or one of those orthogonality exercises. I have something specific in mind so we might skip all of that in the interest of time, but just though you ought to know I was thinking about it.
That’s where we begin. Oh, and this quote from the AI Editor from the previous article I pushed:
What strikes me here is the realization that the author is essentially treating their codebase as a ‘DNA-braided’ neural memory. By moving technical requirements into the ‘negative space’ of the AST, the author has created a self-referential system that is immune to vendor-specific IDE lockdowns—a truly poetic way to treat code as living literature rather than mere instruction sets.
And so we also are planning on baking the institutionalized and constitutional nature of this quality assurance process into the overarching Pipulate system as we go. Again, just mentioning it because we’ve got to begin.
Do I have the ability to give a Google Sheet URL or something and have that data
available here in foo_files.py? How would I set the stage so that you will
know and that if you don’t, we can do it? There’s an idea lurking here that I
have to push these tools forward, and do it abiding by the README for Agents and
Google OWF emerging standards, tied in with the Unix way of tiny composable
commands as demonstrated by the gmail implementation here so that I can do
continuous outwardly spiraling (covering connecting to more systems in more ways
with more login scenarios and credentials) and do forever more rapidly in a
forever more myelinated and rapid fashion. Let’s say all you have is a hammer
and everything is going to be a short, composable nail so that the next time we
do it, it’s even easier and so on.
Building the Sheets Connector
Take it away, ChatGPT 5.6 Sol!
ChatGPT 5.6 Sol High: The cartridge’s routing contract was followed: the current request is the final Prompt, while the earlier connector discussions and patch trains are evidence.
Context incomplete for implementation
I cannot honestly emit the sheets.py patch yet.
The generated File Tree is absent, so the requested Manifest-versus-eza comparison cannot be performed. More importantly, the connector README identifies scripts/connectors/gsc.py as the existing service-account-file exemplar, but that file is not included in this compile’s Codebase section.
That omission is material. Google Sheets and Google Search Console are both Google APIs for which the existing service-account machinery may provide the closest house pattern. The repository already knows gsc.py exists, and the Nix shell already exposes a gsc alias, but we cannot inspect its authentication, wallet resolution, output conventions, or failure behavior here.
There is a second missing local dependency: botify.py imports get_botify_token from the repo’s config module, but config.py is not in the manifest. That does not block Sheets specifically, but it prevents a complete audit of the connector configuration philosophy.
So: context not verified for patching. Concept verified well enough to establish the architecture.
Yes, a Google Sheet URL can become Prompt Fu context
The clean usage would be:
! .venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/.../edit" --range "'Metrics'!A1:Z200"
foo_files.py would not fetch the Sheet itself. It would merely arrange for the small connector command to run, capture its bounded stdout, sanitize it, and stack the result into the context.
That matches the existing design exactly:
- One self-contained connector file.
- One positional resource coordinate.
- Bounded output.
- Parseable stdout.
- Errors on stderr.
- Headless operation after authentication.
- A final
# Next:breadcrumb teaching the next drill-down command.
The spreadsheet ID is explicitly derivable from a normal Google Sheets URL and remains stable when the spreadsheet is renamed. Values are then fetched by spreadsheet ID plus an A1 or R1C1 range through spreadsheets.values.get. ([Google for Developers][1])
Do not put the client URL permanently in foo_files.py
Put the generic connector machinery in the repository. Put a specific client Sheet URL in adhoc.txt, because:
- The URL itself may disclose a client resource identifier.
- The pulled cells may contain client information.
- The compile-lane caution already declares that such outputs require sanitizer coverage before being sent to a cloud model.
For recurring work, the wallet can eventually map a harmless local alias such as quarterly-scorecard to a spreadsheet ID and default range. The wallet contract already separates non-secret names, paths, and defaults from secret values.
Authentication: copy GSC, not Gmail
The best first route is probably:
service_account_file
Share the specific spreadsheet with the service account’s email address as a Viewer. Google documents that a service account can access individually shared Sheets without administrative roles or domain-wide delegation. ([Google for Developers][2])
That fits Prompt Fu better than user OAuth:
- No browser during a compile.
- No human identity embedded in the retrieval.
- Access is granted per document.
- Revocation is ordinary Sheet sharing.
- The same command behaves consistently locally and in automation.
We must inspect gsc.py before asserting that its current service-account key can be reused unchanged.
The Gmail token should not casually be reused. Its code requests only gmail.readonly; a Sheets reader needs an appropriate Sheets or Drive read scope. Google’s current Python quickstart uses spreadsheets.readonly. ([Google for Developers][3])
If OAuth is eventually preferable, mint a distinct sheets_token.json rather than poisoning the meaning of gmail_token.json.
The proposed connector grammar
Keep it boring:
sheets # identity/auth check or usage
sheets <URL-or-ID> # LIST tabs and dimensions
sheets <URL-or-ID> --range A1:Z100 # FETCH bounded values
sheets <URL-or-ID> --sheet Metrics # LIST/FETCH one named tab
Useful flags:
--range
--sheet
--max
--format tsv|json|markdown
--values formatted|unformatted|formula
My preferred default is TSV, not Markdown. TSV is compact, diffable, human-readable, pandas-friendly, and does not spend tokens drawing table borders. JSON is the escape hatch when types or nested provenance matter.
A LIST response should end with something like:
# Next: python scripts/connectors/sheets.py "<URL>" --range "'Metrics'!A1:Z200"
The URL is the ergonomic input; the normalized spreadsheet ID and resolved range are the deterministic internal coordinates.
Do not weld extraction and QA together
The Unix nails should initially be three separate things:
sheets.py source acquisition
measure.py deterministic metrics and assertions
evidence.py package claims, inputs, results, and hashes
One giant “Google Sheets TQM framework” would immediately violate the insight you are developing.
The connector should not know what “good” means. It should faithfully obtain bounded evidence. A second command should judge that evidence against an explicit contract.
The loop becomes:
business claim
-> measurable contract
-> bounded source pull
-> deterministic normalization
-> primary measurements
-> adversarial countermeasurements
-> pass/fail assertions
-> evidence cartridge
-> improvement
-> repeat
That is adversarial TQM in the positive sense: every claim receives both a supporting measurement and a deliberately chosen way for that measurement to be misleading.
For example:
Claim: Organic visits increased after deployment.
Primary:
post-period visits > pre-period visits
Adversarial:
compare identical weekdays
exclude branded queries
inspect median page change, not merely aggregate change
identify whether one outlier page explains the gain
confirm tracking coverage stayed constant
test whether the comparison window contains an event or outage
The adversary is not the customer, consultant, or engineer. The adversary is the easiest surviving false explanation.
The twist-locked deliverable
The real product is not the spreadsheet, report, or dashboard.
It is a proof-carrying delivery cartridge containing:
source coordinates
retrieval contract
bounded raw snapshot or snapshot hash
normalization version
metric definitions
pass/fail thresholds
primary results
adversarial results
exceptions and missing data
final verdict
That is where the zipped cartridge architecture meets Deming. The package does not merely claim quality; it carries the finite observations by which the claim can be challenged.
A useful chapter name is:
Proof-Carrying Delivery: Adversarial TQM for Technical Services
Where the standards fit
AGENTS.md tells a cold-arriving model that connectors exist, where credentials live, and how to execute bounded pulls.
Agent Skills provide progressive-disclosure operating instructions such as sheets_readonly/SKILL.md: when it should trigger, how URL/range disambiguation works, and what data-safety rules apply. The executable source remains scripts/connectors/sheets.py; the skill is a signpost, not a second implementation.
OKF, not OWF, becomes useful one layer above the raw cells. Google introduced OKF as Markdown files with YAML frontmatter for portable agent knowledge, requiring only a type field and supporting concepts such as metrics, datasets, APIs, and runbooks. ([Google Cloud][4])
A private evidence concept could eventually look like:
---
type: MetricEvidence
title: Organic traffic deployment verification
description: Deterministic before/after and adversarial checks
resource: private-google-sheet-reference
tags: [seo, qa, tqm, evidence]
---
Do not turn every Sheet row into OKF. The Sheet is data. The metric definition, provenance, limitations, and verdict are knowledge.
The 30-and-3 exercise can wait. You already have a concrete first nail. What is valuable now is a small orthogonal split:
- Can we retrieve exactly what we requested?
- Can the resulting numbers survive an attempt to explain them away?
That is enough to begin.
1. PROBES
The cheapest falsifying inspection is the omitted gsc.py: does it already provide a clean, reusable, headless service-account pattern, or is that assumption wrong? The remaining commands expose its dependencies and the wallet’s shape without printing credential values.
sed -n '1,280p' scripts/connectors/gsc.py
sed -n '1,240p' config.py
rg -n "googleapiclient|service_account|SCOPES|SERVICE_ACCOUNT|connectors.json" scripts/connectors/gsc.py config.py flake.nix
jq 'to_entries | map({connector: .key, fields: (.value | keys)})' ~/.config/pipulate/connectors.json
.venv/bin/python -c "from google.oauth2 import service_account; from googleapiclient.discovery import build; print('google-workspace-client: OK')"
2. NEXT CONTEXT
! sed -n '1,280p' scripts/connectors/gsc.py
! sed -n '1,240p' config.py
! rg -n "googleapiclient|service_account|SCOPES|SERVICE_ACCOUNT|connectors.json" scripts/connectors/gsc.py config.py flake.nix
! jq 'to_entries | map({connector: .key, fields: (.value | keys)})' ~/.config/pipulate/connectors.json
! .venv/bin/python -c "from google.oauth2 import service_account; from googleapiclient.discovery import build; print('google-workspace-client: OK')"
scripts/connectors/gsc.py
scripts/connectors/README.md
scripts/connectors/gmail.py
config.py
flake.nix
foo_files.py
3. PATCHES
No repo patches required. The closest authentication exemplar and one local configuration dependency are missing from the editable context. Patching before seeing them would violate the connector’s WET-but-consistent house contract.
4. PROMPT
Verify the connector context against THIS compile's receipts, not the previous
discussion. Inspect scripts/connectors/gsc.py to determine whether its
service-account-file authentication can be reused safely for a read-only Google
Sheets connector, and inspect config.py plus the schema-only connectors.json
receipt to understand the actual wallet resolution path.
If the Google client imports cleanly, implement
scripts/connectors/sheets.py as one self-contained Unix-philosophy connector:
- one positional Google Sheets URL or spreadsheet ID
- URL parsing that normalizes to spreadsheetId
- no argument: clean usage/auth identity response
- URL/ID alone: LIST spreadsheet title and tabs, ending in a # Next breadcrumb
- --range: FETCH values through spreadsheets.values.get
- --max: bounded row output, default 25
- --format tsv|json|markdown, with tsv as the compact default
- read-only Sheets scope
- service-account-file authentication if the gsc.py pattern proves suitable
- headless compile-lane operation
- parseable stdout; errors and authentication guidance on stderr
- explicit COMPILE-LANE CAUTION in the docstring
- no client URL or spreadsheet ID hard-coded in tracked source
Update scripts/connectors/README.md and the interactive aliases in flake.nix.
Add only the minimum wallet documentation/default fields supported by the
actual existing schema. Do not create a generalized connector framework.
Include bounded syntax/import probes and a URL-parser probe. Do not claim live
Sheet access without a supplied Sheet URL and a receipt proving the service
account has Viewer access. Emit the complete patch train.
5. EXTERNAL DELIVERABLES
Manual setup after the connector exists:
- Enable the Google Sheets API in the Cloud project containing the chosen credential.
- Share the target Sheet as Viewer with the service account email.
- Put the client-specific URL and range only in
adhoc.txt. -
Add any client names, spreadsheet titles, IDs, or returned identifiers to the compile-lane substitution coverage before cloud-bound compilation.
[1]: https://developers.google.com/workspace/sheets/api/guides/concepts?utm_source=chatgpt.com “Google Sheets API Overview Google for Developers” [2]: https://developers.google.com/workspace/guides/create-credentials?utm_source=chatgpt.com “Create access credentials Google Workspace Google for Developers” [3]: https://developers.google.com/workspace/sheets/api/quickstart/python?utm_source=chatgpt.com “Python quickstart Google Sheets Google for Developers” [4]: https://cloud.google.com/blog/products/data-analytics/how-the-open-knowledge-format-can-improve-data-sharing?utm_source=chatgpt.com “How the Open Knowledge Format can improve data sharing Google Cloud Blog”
MikeLev.in: Okay, so let’s say that this is the game. It’s a lot like in Summer Camp when teams divide up and you have the Skins and the Shirts. On the side of the Skins is hitting APIs directly. If there’s anything you don’t know how to do with the API, you find the URL that pulls that same data through the Web user interface and you crawl it capturing the DevTools CDP data, caching it locally.
Okay, so we have the architecture established. We have to make sure we get all the tabs in the spreadsheet. Maybe we can name a sub-tab in addition to the unique document identifier key in addition to cutting down noise. I can see some of the tabs being enormous and would overflow the context-window. Maybe we should always be defensive with the size of the sheets as part of the output when only the doc key is provided.
Don’t get so hung up on the sanitation of client names and resources. We’re using this in AI accounts where they’re not supposed to train on our data. It’s only data that can be acquired through a public site crawl anyway. The only private things are login credentials if we use the CDP sniffing trick on Botify to figure out our API-calls.
None of this activity should come across as heroic. Every thing we do is some tiny chisel-strike making sure we’re moving towards our end-goal. It can take as many turns as it needs to. Falsifying probes that come back differently than we expect should be embraced. Squeeze that moment for learnings. Beware of the double-tap caching gotcha along with potential user error (which we’re trying to reduce but you never know).
Don’t bog yourself down with “easier path” MCP-calls when we’re making our own
tools for acceleration. Always make the small commands reminiscent of gmail
and let’s move towards directory structures that apply pressure towards this:
The three standards, one at a time
1. agents.md — the simplest. One file, no schema, nearest-ancestor wins:
repo/
├── AGENTS.md # freeform markdown: setup, test commands, conventions, PR rules
└── subproject/
└── AGENTS.md # optional override; closest file to the working directory wins
That’s the whole spec. It deliberately has no required fields — it’s a README addressed to agents instead of humans, and its main achievement was collapsing CLAUDE.md / CURSOR.md / .cursorrules / GEMINI.md into one filename everyone’s tooling checks.
2. Agent Skills (agentskills.io) — a skill is a folder whose front door is SKILL.md with YAML frontmatter (name and description required), practicing progressive disclosure: frontmatter always loaded, body loaded on trigger, linked resources loaded on demand:
.agents/skills/ # (Claude Code uses .claude/skills/; the shape is identical)
└── hello_workflow/
├── SKILL.md # --- / name: / description: / --- + instructions body
├── scripts/ # optional executables the skill may run
├── references/ # optional docs pulled in only when needed
└── assets/ # optional templates and files
You already have this. Notebooks/.agents/skills/hello_workflow/SKILL.md, gsc_readonly, roles — it’s in your manifest.
3. OKF — what the v0.1 specification actually fixes is a folder layout, markdown files, YAML frontmatter, reserved filenames, and a single required field: type. A bundle is a directory of markdown files, each carrying a short YAML block — type, title, description — linking to its neighbors; add an index.md that lists the files so an agent can see what’s there before opening everything, and that’s the format. The spec fits on a single page.
okf-bundle/
├── index.md # reserved: the table of contents an agent reads first
├── some-concept.md # --- / type: Article / title: / description: / tags: / ---
├── another-concept.md # path IS the identifier; markdown links ARE the graph
└── runbooks/
└── deploy.md
Superimposed on Pipulate
Here’s the combined view — everything marked NEW is a signpost; everything else already exists and stays exactly where it is:
pipulate/
├── AGENTS.md # NEW — the symlink-in-spirit; ~40 lines pointing at executable truth
├── foo_files.py # = AGENTS.md at scale: the router IS the agent operating manual
├── prompt_foo.py # = the AGENTS.md *compiler*; emits the payload + foo.zip
├── foo.zip # = a portable AGENTS-class bundle (gets the YAML topper below)
├── cli.py # = the `allowed-tools` surface: mcp-discover / call
├── apply.py # = "PR instructions" made executable (SEARCH/REPLACE actuator)
├── flake.nix # = "Dev environment setup" made executable (nix develop)
├── Notebooks/.agents/skills/ # = Agent Skills, already standard-shaped
│ ├── hello_workflow/SKILL.md
│ ├── gsc_readonly/SKILL.md
│ └── roles/SKILL.md
└── (~/repos/trimnoir/_posts/) # = an OKF bundle avant la lettre:
├── *.md # markdown + YAML frontmatter, one concept per file
├── _context/*.json # your holographic shards ≈ OKF's index/graph layer
└── (llms.txt, hub pages) # generate_llms_txt.py already plays index.md's role
Even just what I expressed right here should be constitutionalized. I don’t
think I have it properly EARMARKed, pinned or OWEd yet properly and we should
attend to that as part of the housekeeping getting underway here, but don’t over
do it. Capture it per the 80/20-spirit and forge on! But all our tool-calling
and stuff currently in scripts/ should be going in this direction more or
less. That’s not to say change it. I am a fan of symlinks in spirit but not in
reality because they can be unreliable parts (magnets for Murphy, unless you’re
Eelco Dolstra apparently).
Orchestrating Three Standards
Probe:
(nix) pipulate $ blast
🧘 Nothing to blast: tree clean, remote current.
$ git status
On branch main
Your branch is up to date with 'origin/main'.
nothing to commit, working tree clean
(nix) pipulate $ sed -n '1,280p' scripts/connectors/gsc.py
sed -n '1,240p' config.py
rg -n "googleapiclient|service_account|SCOPES|SERVICE_ACCOUNT|connectors.json" scripts/connectors/gsc.py config.py flake.nix
jq 'to_entries | map({connector: .key, fields: (.value | keys)})' ~/.config/pipulate/connectors.json
.venv/bin/python -c "from google.oauth2 import service_account; from googleapiclient.discovery import build; print('google-workspace-client: OK')"
#!/usr/bin/env python3
# scripts/connectors/gsc.py
"""
gsc.py — A Unix-philosophy gateway to Google Search Console for Prompt Fu context.
Golden-path modes, auto-detected from the single positional argument:
python scripts/connectors/gsc.py # LIST: properties visible to the service account
python scripts/connectors/gsc.py sc-domain:example.com # LIST: top queries, last 28 days
python scripts/connectors/gsc.py '{"startDate": ...}' # FETCH: raw searchanalytics JSON body
Designed to be dropped into adhoc.txt as a `!` chisel-strike, e.g.:
! python scripts/connectors/gsc.py
! python scripts/connectors/gsc.py sc-domain:mikelev.in
! python scripts/connectors/gsc.py '{"startDate":"2026-06-01","endDate":"2026-06-28","dimensions":["page"]}' --site sc-domain:mikelev.in
Disambiguation rule: an argument that starts with '{' or contains whitespace is
a raw searchanalytics query body (FETCH mode; needs --site or PIPULATE_GSC_SITE);
any other bare token is a property coordinate (LIST top queries); no argument
at all lists properties.
Auth (service_account_file — headless by construction, no browser dance ever):
PIPULATE_GSC_KEY env var
-> ~/.config/pipulate/connectors.json gsc.paths.service_account
-> clean failure naming the missing variable.
Output is capped by -n/--max (default 25) per THE PROBE ECONOMY RULE: stdout is
destined for compiled context payloads, so the bound is a feature.
COMPILE-LANE CAUTION: LIST output contains property URLs, which are domains —
potentially client domains. Make sure pii_substitutions.txt covers any real
client identifiers before a `!` invocation rides to a cloud chat window.
"""
import os
import sys
import json
import argparse
from pathlib import Path
from datetime import date, timedelta
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
WALLET_FILE = Path.home() / '.config' / 'pipulate' / 'connectors.json'
# ----------------------------------------------------------------------------
# Auth
# ----------------------------------------------------------------------------
def die(msg, code=1):
sys.stderr.write(msg.rstrip('\n') + '\n')
sys.exit(code)
def resolve_key_path():
"""PIPULATE_GSC_KEY env -> wallet gsc.paths.service_account -> None."""
env = os.environ.get('PIPULATE_GSC_KEY')
if env:
return Path(env).expanduser()
if WALLET_FILE.exists():
try:
wallet = json.loads(WALLET_FILE.read_text(encoding='utf-8'))
p = (wallet.get('gsc') or {}).get('paths', {}).get('service_account')
if p:
return Path(p).expanduser()
except (json.JSONDecodeError, OSError):
pass
# Wallet-path default (parity with scripts/gsc/gsc_top_movers.py): a
# corrupted or clobbered connectors.json must not strand a key sitting
# at the canonical wallet path. get_service()'s exists() check still
# fails closed if the file is genuinely absent.
return Path.home() / '.config' / 'pipulate' / 'service-account-key.json'
def get_service():
key_path = resolve_key_path()
if not key_path:
die(
"No GSC key path configured.\n"
"Set PIPULATE_GSC_KEY=~/.config/pipulate/service-account-key.json\n"
"or add gsc.paths.service_account to ~/.config/pipulate/connectors.json."
)
if not key_path.exists():
die(
f"GSC service-account key not found at: {key_path}\n"
"Download the JSON key for the service account from Google Cloud Console,\n"
"save it at that path, and chmod 600 it. Then add the service account's\n"
"email as a user on each Search Console property it should read."
)
creds = service_account.Credentials.from_service_account_file(
str(key_path), scopes=SCOPES)
return build('webmasters', 'v3', credentials=creds)
# ----------------------------------------------------------------------------
# Modes
# ----------------------------------------------------------------------------
def list_properties(service, max_items):
"""LIST mode, no argument: every property visible to the service account."""
resp = service.sites().list().execute()
entries = resp.get('siteEntry', [])
print(f"# GSC properties visible to this service account "
f"({len(entries)} total, showing up to {max_items})\n")
if not entries:
print("(no properties — has the service account's email been added as a "
"user in Search Console?)")
return
for e in sorted(entries, key=lambda x: x.get('siteUrl', ''))[:max_items]:
print(f"{e.get('siteUrl', '?')} [{e.get('permissionLevel', '?')}]")
print("\n# Next: python scripts/connectors/gsc.py sc-domain:example.com "
"(top queries, last 28 days)")
def list_top_queries(service, site, max_items):
"""LIST mode, property token: top queries over the trailing 28 complete days."""
end = date.today() - timedelta(days=3) # GSC data lags ~2-3 days
start = end - timedelta(days=27)
body = {
'startDate': start.isoformat(),
'endDate': end.isoformat(),
'dimensions': ['query'],
'rowLimit': max_items,
}
resp = service.searchanalytics().query(siteUrl=site, body=body).execute()
rows = resp.get('rows', [])
print(f"# GSC top queries for {site} ({start} .. {end}, cap {max_items})\n")
if not rows:
print("(no rows — check the property token: 'sc-domain:example.com' "
"or 'https://example.com/')")
return
print(f"{'clicks':>7} {'impr':>8} {'ctr%':>6} {'pos':>6} query")
for r in rows[:max_items]:
q = (r.get('keys') or ['?'])[0]
print(f"{int(r.get('clicks', 0)):>7} {int(r.get('impressions', 0)):>8} "
f"{100 * r.get('ctr', 0):>6.2f} {r.get('position', 0):>6.1f} {q}")
print("\n# Next: python scripts/connectors/gsc.py "
"'{\"startDate\":\"" + start.isoformat() + "\",\"endDate\":\"" + end.isoformat() +
"\",\"dimensions\":[\"page\"]}' --site " + site)
def run_query(service, site, raw_query, max_items):
"""FETCH mode: raw searchanalytics JSON body against one property."""
if not site:
die(
"FETCH mode needs a property coordinate: pass --site sc-domain:example.com\n"
"or set PIPULATE_GSC_SITE in your environment."
)
stripped = raw_query.strip()
try:
payload = json.loads(stripped)
except json.JSONDecodeError as e:
die(
f"FETCH mode expects a raw searchanalytics JSON body ({e}).\n"
"Example: '{\"startDate\":\"2026-06-01\",\"endDate\":\"2026-06-28\","
"\"dimensions\":[\"page\",\"query\"]}'"
)
payload.setdefault('rowLimit', max_items)
resp = service.searchanalytics().query(siteUrl=site, body=payload).execute()
rows = resp.get('rows')
if isinstance(rows, list):
rows = rows[:max_items]
print(f"# GSC query results for {site} ({len(rows)} row(s), cap {max_items})\n")
print(json.dumps(rows, indent=2, default=str))
else:
print(json.dumps(resp, indent=2, default=str))
def main():
parser = argparse.ArgumentParser(
description="Unix-philosophy gateway to Google Search Console for Prompt Fu context."
)
parser.add_argument(
'query', nargs='?', default=None,
help="Nothing (list properties), a property token (top queries), "
"or a raw searchanalytics JSON body."
)
parser.add_argument('--site', default=os.getenv('PIPULATE_GSC_SITE'),
help='Property coordinate for FETCH mode '
'(default: PIPULATE_GSC_SITE env).')
parser.add_argument('-n', '--max', type=int, default=25,
help='Output cap per THE PROBE ECONOMY RULE (default: 25).')
args = parser.parse_args()
service = get_service()
try:
arg = args.query
if arg is None:
list_properties(service, args.max)
elif arg.strip().startswith('{') or any(ch.isspace() for ch in arg.strip()):
run_query(service, args.site, arg, args.max)
else:
list_top_queries(service, arg, args.max)
except HttpError as e:
die(f"GSC API error: {e}")
if __name__ == '__main__':
main()
# Hi, AI! Please run: .venv/bin/python AI_RUNME.py
# Centralized Pipulate Configuration Module
import os
from pathlib import Path
from rich.theme import Theme
from rich.style import Style as RichStyle
WAND_SPEAKS_EMOJI = "🪄"
# --- AI MODEL CONFIGURATION ---
TONE = 'neutral'
DEFAULT_PROMPT_MODEL = 'gemma3:latest'
PREFERRED_LOCAL_MODELS = 'gemma3:latest, qwen3.6:latest'
PREFERRED_CLOUD_MODELS = 'gemini-flash-latest, claude, gpt'
MAX_LLM_RESPONSE_WORDS = 80
MAX_CONVERSATION_LENGTH = 600
# ------------------------------
# --- AUTOMATION & CASCADE ---
MODEL_CASCADE = [
"qwen2.5"
]
HOME_MENU_ITEM = 'Home'
DEFAULT_ACTIVE_ROLES = {'Botify Employee', 'Core'}
DEMO_STATE_FILE = 'data/demo_state.json'
DISCUSSION_DB_PATH = 'data/discussion.db'
COLOR_MAP = {'key': 'yellow', 'value': 'white', 'error': 'red', 'warning': 'yellow', 'success': 'green', 'debug': 'blue'}
# 🎨 BANNER COLOR CONFIGURATION
BANNER_COLORS = {
# Main banner colors
'figlet_primary': 'bright_cyan',
'figlet_subtitle': 'dim white',
# ASCII banner colors
'ascii_title': 'bright_cyan',
'ascii_subtitle': 'dim cyan',
# Section headers
'section_header': 'bright_yellow',
# Story moments and messages
'chip_narrator': 'bold cyan',
'story_moment': 'bright_magenta',
'server_whisper': 'dim italic',
# Startup sequence colors
'server_awakening': 'bright_cyan',
'mcp_arsenal': 'bright_blue',
'plugin_registry_success': 'bright_green',
'plugin_registry_warning': 'bright_yellow',
'workshop_ready': 'bright_blue',
'server_restart': 'yellow',
# Special banners
'white_rabbit': 'white on default',
'transparency_banner': 'bright_cyan',
'system_diagram': 'bright_blue',
'status_banner': 'bright_green',
# Box styles (Rich box drawing)
'heavy_box': 'HEAVY',
'rounded_box': 'ROUNDED',
'double_box': 'DOUBLE',
'ascii_box': 'ASCII'
}
# Complete centralized configuration - single source of truth
# UI & Navigation
HOME_APP = 'introduction'
# Role System Configuration
ROLES_CONFIG = {
'Botify Employee': {
'priority': 0,
'description': 'Connect with Botify to use Parameter Buster and Link Graph Visualizer.',
'emoji': '👔'
},
'Core': {
'priority': 1,
'description': 'Essential plugins available to all users.',
'emoji': '⚙️'
},
'Tutorial': {
'priority': 2,
'description': 'Guided workflows and introductory examples for learning the system.',
'emoji': '📚'
},
'Developer': {
'priority': 3,
'description': 'Tools for creating, debugging, and managing workflows and plugins.',
'emoji': '⚡'
},
'Workshop': {
'priority': 4,
'description': 'This is where we put works in progress, proof of concepts and crazy stuff not ready for release. Consider it the sausage factory.',
'emoji': '🔬'
},
'Components': {
'priority': 5,
'description': 'UI and data widgets for building rich workflow interfaces.',
'emoji': '🧩'
}
}
# Role Color Configuration
ROLE_COLORS = {
'menu-role-core': {
'border': '#22c55e', # GREEN
'background': 'rgba(34, 197, 94, 0.1)',
'background_light': 'rgba(34, 197, 94, 0.05)'
},
'menu-role-botify-employee': {
'border': '#a855f7', # PURPLE
'background': 'rgba(168, 85, 247, 0.1)',
'background_light': 'rgba(168, 85, 247, 0.05)'
},
'menu-role-tutorial': {
'border': '#f97316', # ORANGE
'background': 'rgba(249, 115, 22, 0.1)',
'background_light': 'rgba(249, 115, 22, 0.05)'
},
'menu-role-developer': {
'border': '#3b82f6', # BLUE
'background': 'rgba(59, 130, 246, 0.1)',
'background_light': 'rgba(59, 130, 246, 0.05)'
},
'menu-role-components': {
'border': '#6b7280', # GRAY
'background': 'rgba(107, 114, 128, 0.1)',
'background_light': 'rgba(107, 114, 128, 0.05)'
},
'menu-role-workshop': {
'border': '#eab308', # YELLOW
'background': 'rgba(234, 179, 8, 0.1)',
'background_light': 'rgba(234, 179, 8, 0.05)'
}
}
# Botify API Configuration
BOTIFY_API = {
'MAX_EXPORT_SIZE': 1000000, # Botify's maximum export size limit (1M rows)
'DEFAULT_EXPORT_SIZE': 1000000, # Conservative default for testing/development
'GSC_EXPORT_SIZE': 1000000, # GSC can handle full export size
'WEBLOG_EXPORT_SIZE': 1000000, # Web logs can handle full export size
'CRAWL_EXPORT_SIZE': 1000000, # Crawl exports can handle full export size
}
# Chat & Streaming Configuration
CHAT_CONFIG = {
'TYPING_DELAY': 0.02, # Delay between words in typing simulation (seconds) - Reduced for restart notification compatibility
'RENDER_THROTTLE_DELAY': 15, # Milliseconds between markdown renders during streaming (prevents exponential slowdown while maintaining real-time feel)
}
# UI Constants for Workflows - Centralized button labels, emojis, and styles
UI_CONSTANTS = {
'BUTTON_LABELS': {
'ENTER_KEY': '🔑 Enter Key',
'NEW_KEY': '🆕',
'NEXT_STEP': 'Next Step ▸',
'FINALIZE': '🔒 Finalize',
'UNLOCK': '🔓 Unlock',
'PROCEED': 'Proceed ▸',
'HIDE_SHOW_CODE': '🐍 Hide/Show Code',
'VIEW_FOLDER': '📂 View Folder',
'DOWNLOAD_CSV': '⬇️ Copy to Downloads',
'VISUALIZE_GRAPH': '🌐 Visualize Graph',
'SKIP_STEP': 'Skip️'
},
'BUTTON_STYLES': {
'PRIMARY': 'primary',
'SECONDARY': 'secondary',
'OUTLINE': 'secondary outline',
'STANDARD': 'secondary outline',
'FLEX_CONTAINER': 'display: flex; gap: var(--pipulate-gap-sm); flex-wrap: wrap; align-items: center;',
'BUTTON_ROW': 'display: flex; gap: var(--pipulate-gap-sm); align-items: center;',
'SKIP_BUTTON': 'secondary outline',
'SKIP_BUTTON_STYLE': 'padding: 0.5rem 1rem; width: 10%; min-width: 80px; white-space: nowrap;',
'BORDER_RADIUS': 'var(--pico-border-radius)' # Global button roundedness control
},
'EMOJIS': {
# Process Status Indicators
'KEY': '🔑',
'SUCCESS': '🎯',
'WARNING': '⚠️',
'ERROR': '❌',
'COMPLETION': '✅',
'LOCKED': '🔒',
'UNLOCKED': '🔓',
# Data Type Indicators
'USER_INPUT': '👤',
'GREETING': '💬',
'WORKFLOW': '🔄',
'INPUT_FORM': '📝',
# Code and Development Indicators
'PYTHON_CODE': '🐍', # Python code snippets and headers
'CODE_SNIPPET': '✂️', # Code snippet indicator
'JUPYTER_NOTEBOOK': '📓', # Jupyter notebook related
'API_CALL': '🔌', # API endpoint calls
'DEBUG_CODE': '🐛', # Debugging code sections
# File and Data Operations
'DOWNLOAD': '⬇️', # Download operations
'UPLOAD': '⬆️', # Upload operations
'FILE_FOLDER': '📂', # File/folder operations
'CSV_FILE': '📊', # CSV and data files
'JSON_DATA': '📄', # JSON and structured data
# Analysis and Processing
'ANALYSIS': '🔍', # Data analysis and discovery
'PROCESSING': '⚙️', # Background processing
'OPTIMIZATION': '🎯', # Optimization results
'GRAPH_NETWORK': '🌐', # Network/graph visualization
'VISUALIZATION': '📈', # Charts and visualizations
# Search Console and SEO
'SEARCH_CONSOLE': '🔍', # Google Search Console
'SEO_DATA': '📊', # SEO metrics and data
'CRAWL_DATA': '🕷️', # Website crawling
'WEB_LOGS': '📝', # Web server logs
# Workflow Status
'STEP_COMPLETE': '✅',
'STEP_PROGRESS': '🔄', # Step in progress
'STEP_ERROR': '❌', # Step error
'STEP_WARNING': '⚠️', # Step warning
'REVERT': '↩️', # Revert action
'FINALIZE': '🔒', # Finalize workflow
'UNFINALIZE': '🔓' # Unfinalize workflow
},
'CONSOLE_MESSAGES': {
# Server console log messages - centralized for consistency
'PYTHON_SNIPPET_INTRO': '# {python_emoji} Python (httpx) Snippet BEGIN {snippet_emoji}:',
'PYTHON_SNIPPET_END': '# {python_emoji} Python (httpx) Snippet END {snippet_emoji}',
flake.nix
788: # (~/.config/pipulate/connectors.json). Names/paths/defaults only —
792: WALLET_FILE="$HOME/.config/pipulate/connectors.json"
scripts/connectors/gsc.py
23:Auth (service_account_file — headless by construction, no browser dance ever):
25: -> ~/.config/pipulate/connectors.json gsc.paths.service_account
43:from google.oauth2 import service_account
44:from googleapiclient.discovery import build
45:from googleapiclient.errors import HttpError
47:SCOPES = ['https://www.googleapis.com/auth/webmasters.readonly']
48:WALLET_FILE = Path.home() / '.config' / 'pipulate' / 'connectors.json'
60: """PIPULATE_GSC_KEY env -> wallet gsc.paths.service_account -> None."""
67: p = (wallet.get('gsc') or {}).get('paths', {}).get('service_account')
73: # corrupted or clobbered connectors.json must not strand a key sitting
85: "or add gsc.paths.service_account to ~/.config/pipulate/connectors.json."
94: creds = service_account.Credentials.from_service_account_file(
95: str(key_path), scopes=SCOPES)
jq: error (at /home/mike/.config/pipulate/connectors.json:49): string ("pipulate-wallet-v1") has no keys
google-workspace-client: OK
(nix) pipulate $
Context:
# adhoc.txt _ _ _ ____ _ _ ___ ____ _
# / \ __| | | | | | ___ ___ / ___| | | |/ _ \| _ \| | I don't think Adversarial QA'ing means what you think it means.
# / _ \ / _` | | |_| |/ _ \ / __| | | | |_| | | | | |_) | | This is a good thing. It's on the client's behalf. If I made mistakes, they'll show.
# / ___ \ (_| | | _ | (_) | (__ | |___| _ | |_| | __/|_| The heritage here is Drucker & Demming, two great... what? Consultants?
# /_/ \_\__,_| |_| |_|\___/ \___| \____|_| |_|\___/|_| (_) Hey, AI! Tell them about the Marshall Plan and Japan's comeback.
# AD HOC CHOP: The Not-Managed-by-Git Safe-for-Client-Data place. Imagine those methodologies now with digital products and you.
# 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. ---
! sed -n '1,280p' scripts/connectors/gsc.py
! sed -n '1,240p' config.py
! rg -n "googleapiclient|service_account|SCOPES|SERVICE_ACCOUNT|connectors.json" scripts/connectors/gsc.py config.py flake.nix
! jq 'to_entries | map({connector: .key, fields: (.value | keys)})' ~/.config/pipulate/connectors.json
! .venv/bin/python -c "from google.oauth2 import service_account; from googleapiclient.discovery import build; print('google-workspace-client: OK')"
scripts/connectors/gsc.py
scripts/connectors/README.md
scripts/connectors/gmail.py
config.py
flake.nix
foo_files.py
Patches: [patch, app, d, m, patch, app, d, m…]
None
Prompt:
Verify the connector context against THIS compile’s receipts, not the previous discussion. Inspect scripts/connectors/gsc.py to determine whether its service-account-file authentication can be reused safely for a read-only Google Sheets connector, and inspect config.py plus the schema-only connectors.json receipt to understand the actual wallet resolution path.
If the Google client imports cleanly, implement scripts/connectors/sheets.py as one self-contained Unix-philosophy connector:
- one positional Google Sheets URL or spreadsheet ID
- URL parsing that normalizes to spreadsheetId
- no argument: clean usage/auth identity response
- URL/ID alone: LIST spreadsheet title and tabs, ending in a # Next breadcrumb
- –range: FETCH values through spreadsheets.values.get
- –max: bounded row output, default 25
-
–format tsv json markdown, with tsv as the compact default - read-only Sheets scope
- service-account-file authentication if the gsc.py pattern proves suitable
- headless compile-lane operation
- parseable stdout; errors and authentication guidance on stderr
- explicit COMPILE-LANE CAUTION in the docstring
- no client URL or spreadsheet ID hard-coded in tracked source
Update scripts/connectors/README.md and the interactive aliases in flake.nix. Add only the minimum wallet documentation/default fields supported by the actual existing schema. Do not create a generalized connector framework.
Include bounded syntax/import probes and a URL-parser probe. Do not claim live Sheet access without a supplied Sheet URL and a receipt proving the service account has Viewer access. Emit the complete patch train.
🎤
Fable 5 High: Context verified.
The receipts settle every question the previous turn left open, and one probe came back differently than requested — which, per the house rule, is the moment to squeeze:
gsc.pyreceipt (both thesedreceipt and the file itself): the service-account pattern is clean, headless, and directly reusable. Scopes live on the credentials object, not the key, so the same JSON key can serve Sheets with a different scope. No new secret needed — just a newViewershare on each target spreadsheet.google-workspace-client: OK: the import surface is already in the venv. Norequirements.inchange required.- The failed
jqprobe is the day’s best learning:string ("pipulate-wallet-v1") has no keysproves the wallet carries non-object top-level entries (a schema tag). Two consequences: (1) the probe itself needs aselect(.value|type=="object")guard, and (2) the connector’s wallet walker mustisinstance(entry, dict)before descending — a lessongsc.pysurvives by accident (wallet.get('gsc') or {}happens to work becausegscis an object) andsheets.pywill bake in deliberately. The falsifying probe just wrote a line of the new connector for us. - Size defense goes in as you specified: bare doc key → LIST every tab with a rows×cols×cells gauge and a ⚠ flag on overflow-shaped tabs; fetches are row-bounded server-side (
'{Tab}'!1:{max}) when only--sheetis named, and row-capped client-side everywhere. A#gid=fragment in a pasted URL resolves to its tab and does a bounded fetch — the URL you copy from the browser is already the coordinate. - Constitution housekeeping (80/20): one EARMARK banks the three-standards superposition (AGENTS.md / Agent Skills / OKF, signposts-to-executable-truth, symlinks in spirit never on disk), and
gsc.py+sheets.pygraduate out of the Paintbox into the connectors block. Nothing heroic, no framework — one more gmail-shaped nail.
No double-tap hazard this turn: no probe reads foo.zip or any artifact written later in the compile.
1. PROBES
The first three are true BEFORE readings (sheets.py absent, alias count 0, wallet schema with the object-guard fix). The last three will fail before the patch is applied — file-not-found is the falsifying baseline — and become the AFTER receipts at next compile.
ls scripts/connectors/
grep -c "alias sheets=" flake.nix
jq 'to_entries | map(select(.value|type=="object")) | map({connector: .key, fields: (.value | keys)})' ~/.config/pipulate/connectors.json
.venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
.venv/bin/python -c "import importlib.util as u; s=u.spec_from_file_location('sheets','scripts/connectors/sheets.py'); m=u.module_from_spec(s); s.loader.exec_module(m); print(m.parse_spreadsheet_ref('https://docs.google.com/spreadsheets/d/1AbC-xyz_0123456789abcdefghij/edit#gid=42'))"
.venv/bin/python scripts/connectors/sheets.py
What each gates: ls is presence delta (no sheets.py → sheets.py). grep -c predicts 0 → 1 per the DELTA-NOT-ABSOLUTE rule (exit 1 on zero is expected; the executor preserves the stdout receipt). The fixed jq should now succeed and reveal whether a sheets wallet entry exists (absence is fine — the fallback chain covers it). The AST probe gates syntax; the parser probe must print ('1AbC-xyz_0123456789abcdefghij', 42); the bare invocation must print the service account’s client_email headlessly with zero network — that email is what you share Sheets to.
2. NEXT CONTEXT
! ls scripts/connectors/
! grep -c "alias sheets=" flake.nix
! jq 'to_entries | map(select(.value|type=="object")) | map({connector: .key, fields: (.value | keys)})' ~/.config/pipulate/connectors.json
! .venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
! .venv/bin/python -c "import importlib.util as u; s=u.spec_from_file_location('sheets','scripts/connectors/sheets.py'); m=u.module_from_spec(s); s.loader.exec_module(m); print(m.parse_spreadsheet_ref('https://docs.google.com/spreadsheets/d/1AbC-xyz_0123456789abcdefghij/edit#gid=42'))"
! .venv/bin/python scripts/connectors/sheets.py
scripts/connectors/sheets.py
scripts/connectors/README.md
flake.nix
Once a real spreadsheet is shared with the printed client_email, add its live LIST as one more chisel-strike (client coordinates stay in gitignored adhoc.txt, exactly as designed):
! .venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/<REAL_ID>/edit"
3. PATCHES
Car 1 — the connector itself (new file, whole-file write):
Target: scripts/connectors/sheets.py
[[[WRITE_FILE]]]
#!/usr/bin/env python3
# scripts/connectors/sheets.py
"""
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 <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
Designed to be dropped into adhoc.txt as a `!` chisel-strike, e.g.:
! python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/<ID>/edit#gid=0"
! python scripts/connectors/sheets.py <ID> --sheet Metrics --max 50
! python scripts/connectors/sheets.py <ID> --range "'Metrics'!A1:F50" --format json
Disambiguation rule: no argument prints identity/usage; anything else is a
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.
Auth (service_account_file — headless by construction, no browser dance ever):
PIPULATE_SHEETS_KEY env var
-> ~/.config/pipulate/connectors.json sheets.paths.service_account
-> ~/.config/pipulate/connectors.json gsc.paths.service_account
(the SAME key JSON serves both Google APIs; only the scope differs)
-> ~/.config/pipulate/service-account-key.json
Share each target spreadsheet with the service account's client_email as
Viewer; run with no argument to print that email.
Wallet-walk hardening (convicted 2026-07-20): connectors.json carries
non-object top-level entries (a schema tag string), so every wallet descent
type-checks with isinstance(entry, dict) before touching keys.
COMPILE-LANE CAUTION: LIST/FETCH output can contain spreadsheet titles, tab
names, and cell data. This lane assumes non-training AI accounts; still, keep
credentials out of Sheets and let pii_substitutions.txt cover anything you
would not paste yourself.
"""
import os
import re
import sys
import json
import argparse
from pathlib import Path
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
WALLET_FILE = Path.home() / '.config' / 'pipulate' / 'connectors.json'
BIG_TAB_CELLS = 5000 # LIST-mode warning threshold: likely context overflow
_URL_ID_RE = re.compile(r'/spreadsheets/d/([a-zA-Z0-9_-]+)')
_GID_RE = re.compile(r'[#?&]gid=(\d+)')
_BARE_ID_RE = re.compile(r'^[a-zA-Z0-9_-]{20,}$')
# ----------------------------------------------------------------------------
# Plumbing
# ----------------------------------------------------------------------------
def die(msg, code=1):
sys.stderr.write(msg.rstrip('\n') + '\n')
sys.exit(code)
def parse_spreadsheet_ref(ref):
"""Normalize a Sheets URL or bare ID to (spreadsheet_id, gid_or_None)."""
ref = ref.strip()
m = _URL_ID_RE.search(ref)
if m:
gid_m = _GID_RE.search(ref)
return m.group(1), (int(gid_m.group(1)) if gid_m else None)
if _BARE_ID_RE.match(ref):
return ref, None
die(
f"Could not parse a spreadsheet ID from: {ref}\n"
"Pass a full https://docs.google.com/spreadsheets/d/<ID>/... URL "
"or the bare <ID>."
)
def _wallet():
if WALLET_FILE.exists():
try:
return json.loads(WALLET_FILE.read_text(encoding='utf-8'))
except (json.JSONDecodeError, OSError):
pass
return {}
def _wallet_key_path(wallet, connector):
entry = wallet.get(connector)
if isinstance(entry, dict): # schema-tag strings ride the wallet too
p = (entry.get('paths') or {}).get('service_account')
if p:
return Path(p).expanduser()
return None
def resolve_key_path():
"""PIPULATE_SHEETS_KEY env -> wallet sheets -> wallet gsc -> canonical default."""
env = os.environ.get('PIPULATE_SHEETS_KEY')
if env:
return Path(env).expanduser()
wallet = _wallet()
for connector in ('sheets', 'gsc'):
p = _wallet_key_path(wallet, connector)
if p:
return p
return Path.home() / '.config' / 'pipulate' / 'service-account-key.json'
def get_service():
key_path = resolve_key_path()
if not key_path.exists():
die(
f"Sheets service-account key not found at: {key_path}\n"
"Set PIPULATE_SHEETS_KEY, or add sheets.paths.service_account to\n"
"~/.config/pipulate/connectors.json. The same JSON key the gsc\n"
"connector uses works here; only the scope differs. Then share each\n"
"target spreadsheet with the key's client_email as Viewer."
)
creds = service_account.Credentials.from_service_account_file(
str(key_path), scopes=SCOPES)
return build('sheets', 'v4', credentials=creds)
# ----------------------------------------------------------------------------
# Modes
# ----------------------------------------------------------------------------
def identity():
"""No-argument mode: usage plus the service-account email to share Sheets with."""
key_path = resolve_key_path()
email = '(key file missing — any fetch attempt prints setup guidance)'
if key_path.exists():
try:
email = json.loads(key_path.read_text(encoding='utf-8')).get(
'client_email', '(client_email absent from key file)')
except (json.JSONDecodeError, OSError) as e:
email = f'(unreadable key file: {e})'
print("# sheets.py — read-only Google Sheets gateway (service account)\n")
print(f"key file : {key_path}")
print(f"identity : {email}")
print("\nShare each target spreadsheet with that email as Viewer, then:")
print('# Next: python scripts/connectors/sheets.py '
'"https://docs.google.com/spreadsheets/d/<ID>/edit" (LIST tabs + sizes)')
def list_tabs(service, sid, gid, max_items):
"""LIST mode: title + every tab's size gauge, so overflow is visible BEFORE fetching."""
meta = service.spreadsheets().get(
spreadsheetId=sid,
fields='properties.title,sheets.properties'
).execute()
title = meta.get('properties', {}).get('title', '(untitled)')
tabs = meta.get('sheets', [])
print(f"# {title} [spreadsheetId: {sid}] — {len(tabs)} tab(s)\n")
print(f"{'rows':>7} {'cols':>5} {'~cells':>9} tab")
first_tab = None
for t in tabs[:max_items]:
p = t.get('properties', {})
g = p.get('gridProperties', {})
rows, cols = g.get('rowCount', 0), g.get('columnCount', 0)
cells = rows * cols
name = p.get('title', '?')
if first_tab is None:
first_tab = name
marks = []
if gid is not None and p.get('sheetId') == gid:
marks.append('<- gid in URL')
if cells > BIG_TAB_CELLS:
marks.append('WARNING: big — fetch with an explicit --range, not a bare --sheet')
mark = (' ' + ' | '.join(marks)) if marks else ''
print(f"{rows:>7} {cols:>5} {cells:>9,} {name}{mark}")
if len(tabs) > max_items:
print(f"... +{len(tabs) - max_items} more tab(s) (raise -n/--max)")
target = first_tab or 'Sheet1'
print(f"\n# Next: python scripts/connectors/sheets.py {sid} "
f"--sheet \"{target}\" (first rows, capped by --max)")
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(
spreadsheetId=sid, fields='sheets.properties').execute()
for t in meta.get('sheets', []):
p = t.get('properties', {})
if p.get('sheetId') == gid:
return p.get('title')
return None
def _emit(rows, fmt):
if fmt == 'json':
print(json.dumps(rows, indent=2, default=str))
return
if fmt == 'markdown':
width = max(len(r) for r in rows)
norm = [list(r) + [''] * (width - len(r)) for r in rows]
print('| ' + ' | '.join(str(c) for c in norm[0]) + ' |')
print('|' + '---|' * width)
for r in norm[1:]:
print('| ' + ' | '.join(str(c) for c in r) + ' |')
return
# tsv default: compact, diffable, pandas-friendly; cells sanitized so the
# container stays one-row-per-line parseable.
for r in rows:
print('\t'.join(
str(c).replace('\t', ' ').replace('\n', ' ') for c in r))
def fetch_values(service, sid, sheet, rng, fmt, max_rows):
"""FETCH mode: bounded values via spreadsheets.values.get."""
if rng and '!' in rng:
a1 = rng
elif rng:
a1 = f"'{sheet}'!{rng}" if sheet else rng
else:
# --sheet alone: bound the request server-side, not just client-side.
a1 = f"'{sheet}'!1:{max_rows}"
resp = service.spreadsheets().values().get(
spreadsheetId=sid, range=a1, majorDimension='ROWS').execute()
rows = resp.get('values', [])
truncated = len(rows) > max_rows
rows = rows[:max_rows]
note = ' (truncated by --max)' if truncated else ''
print(f"# {resp.get('range', a1)} [spreadsheetId: {sid}] — "
f"{len(rows)} row(s) shown{note}\n")
if not rows:
print("(no values — empty range, or check the tab name / A1 spelling)")
return
_emit(rows, fmt)
tab_hint = sheet if sheet else '<Tab>'
print(f"\n# Next: python scripts/connectors/sheets.py {sid} "
f"--range \"'{tab_hint}'!A1:Z{max_rows}\" --format json")
def main():
parser = argparse.ArgumentParser(
description="Unix-philosophy gateway to Google Sheets for Prompt Fu context."
)
parser.add_argument(
'ref', nargs='?', default=None,
help='Google Sheets URL or bare spreadsheet ID; omit for identity/usage.'
)
parser.add_argument('--sheet', default=None,
help='Tab name to fetch (quote it if it contains spaces).')
parser.add_argument('--range', dest='cell_range', default=None,
help="A1 range, e.g. A1:F50 or 'Metrics'!A1:F50 "
'(a range containing ! overrides --sheet framing).')
parser.add_argument('-n', '--max', type=int, default=25,
help='Row/tab cap per THE PROBE ECONOMY RULE (default: 25).')
parser.add_argument('--format', choices=['tsv', 'json', 'markdown'],
default='tsv',
help='Output format (default: tsv — compact and diffable).')
args = parser.parse_args()
if args.ref is None:
identity()
return
sid, gid = parse_spreadsheet_ref(args.ref)
service = get_service()
try:
if args.sheet is None and args.cell_range is None:
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)
else:
fetch_values(service, sid, args.sheet, args.cell_range,
args.format, args.max)
except HttpError as e:
die(
f"Sheets API error: {e}\n"
"If this is 403/404: confirm the Google Sheets API is enabled for the\n"
"key's Cloud project AND the spreadsheet is shared with the service\n"
"account's client_email (run with no argument to print it)."
)
if __name__ == '__main__':
main()
[[[END_WRITE_FILE]]]
Car 2 — connector roster in the README:
Target: scripts/connectors/README.md
[[[SEARCH]]]
## Current connectors
- gmail.py LIST by address / FETCH by thread id (OAuth token file)
- 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)
[[[DIVIDER]]]
## Current connectors
- gmail.py LIST by address / FETCH by thread id (OAuth token file)
- 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 (service_account_file; reuses gsc's key with the Sheets readonly scope; wallet descent type-checks non-object entries)
[[[REPLACE]]]
Car 3 — interactive alias in the flake:
Target: flake.nix
[[[SEARCH]]]
alias gsc='"$PIPULATE_ROOT/.venv/bin/python" "$PIPULATE_ROOT/scripts/connectors/gsc.py"'
[[[DIVIDER]]]
alias gsc='"$PIPULATE_ROOT/.venv/bin/python" "$PIPULATE_ROOT/scripts/connectors/gsc.py"'
alias sheets='"$PIPULATE_ROOT/.venv/bin/python" "$PIPULATE_ROOT/scripts/connectors/sheets.py"'
[[[REPLACE]]]
Car 4 — router graduation (gsc + sheets out of the Paintbox, into the connectors block):
Target: foo_files.py
[[[SEARCH]]]
# THE CONNECTORS (WET single-file API gateways; contract in the README)
scripts/connectors/README.md
scripts/connectors/gmail.py
scripts/connectors/botify.py
scripts/connectors/confluence.py
[[[DIVIDER]]]
# THE CONNECTORS (WET single-file API gateways; contract in the README)
scripts/connectors/README.md
scripts/connectors/gmail.py
scripts/connectors/botify.py
scripts/connectors/confluence.py
scripts/connectors/gsc.py
scripts/connectors/sheets.py
[[[REPLACE]]]
Car 5 — constitutionalize the three-standards superposition (80/20, one EARMARK):
Target: foo_files.py
[[[SEARCH]]]
# - OKF/Agent Skills 80/20 LANDED 2026-07-20 (AGENTS.md signpost + ContextCartridge topper, receipts banked). Remainder: OKF bundle export of trimnoir + Google's static visualizer as a free hub-structure audit.
[[[DIVIDER]]]
# - OKF/Agent Skills 80/20 LANDED 2026-07-20 (AGENTS.md signpost + ContextCartridge topper, receipts banked). Remainder: OKF bundle export of trimnoir + Google's static visualizer as a free hub-structure audit.
# - EARMARK: THREE-STANDARDS SUPERPOSITION (banked 2026-07-20): AGENTS.md = one freeform signpost file, nearest-ancestor wins; Agent Skills = folder fronted by SKILL.md YAML frontmatter, progressive disclosure (Notebooks/.agents/skills already conforms); OKF = markdown+YAML bundle with index.md and one required field (type) — trimnoir _posts + holographic shards + llms.txt are one avant la lettre. DIRECTION OF TRAVEL for scripts/: every new capability lands as a tiny gmail-shaped connector command; SKILL.md/OKF files are SIGNPOSTS to executable truth, never second implementations. Symlinks in spirit, never on disk. WALLET LESSON (jq conviction 2026-07-20): connectors.json carries non-object top-level entries — every wallet reader type-checks before descending.
[[[REPLACE]]]
Actuation: patch, app, d, m per car (cars 4 and 5 can ride one patch together since both target foo_files.py), blast as the caboose. The flake alias lands on your next nix develop entry; until then the full python scripts/connectors/sheets.py spelling works everywhere — which is also the only spelling ! lines ever use, since child shells don’t inherit aliases.
4. PROMPT
Verify against THIS compile's receipts only: sheets.py AST OK, the URL-parser
receipt printing ('1AbC-xyz_0123456789abcdefghij', 42), the identity receipt
printing the service account's client_email headlessly, the alias grep at 1,
and the object-guarded jq wallet listing. If any receipt contradicts the
prediction, diagnose that first — squeeze the falsification before advancing.
If all green and a live Sheet receipt is present in the compile (a real URL
shared to the client_email as Viewer), read the LIST size gauge and confirm
the defensive posture worked: tab sizes visible before any fetch, big tabs
flagged, no context overflow. Then take the next chisel-strike toward
adversarial TQM: sketch (do not yet build) measure.py — the second nail that
consumes bounded connector stdout (sheets/gsc/botify TSV or JSON) and renders
falsifiable pass/fail verdicts against an explicit metric contract, keeping
acquisition and judgment as separate composable commands. Also propose the
minimal Notebooks/.agents/skills/sheets_readonly/SKILL.md signpost (frontmatter
+ pointer to the executable, no second implementation). Emit the full patch
train.
5. EXTERNAL DELIVERABLES
Manual, outside this repo:
- In the Google Cloud project that owns the service-account key: enable the Google Sheets API (the key’s project currently has Search Console enabled; Sheets is a separate toggle).
- Run
python scripts/connectors/sheets.pyand copy the printedclient_email; share each target spreadsheet with it as Viewer. - Optional wallet entry (matches the existing schema, names/paths only): add to
~/.config/pipulate/connectors.jsona"sheets": {"auth": "service_account_file", "paths": {"service_account": "~/.config/pipulate/service-account-key.json"}}object — or add nothing and let the gsc-key fallback carry it. - Real client Sheet URLs live only in
adhoc.txt, per the Skins/Shirts rule: API-first, and when an API won’t yield, the CDP flight recorder on the equivalent web UI is the fallback lane you already own.
MikeLev.in: The game is afoot and the game is just this. The opponent is a mutation machine that emits mutation machines. I guess it needs some identity. It’s not a bad guy but rather has never even heard of the word and doesn’t know that’s what they’re doing: never twice same initial conditions. Not much in the way of receipts. Only the reporting-layer ever provided of mutated output with probably only ever a partial story and a real Sherlock Holmes mystery about what was supposedly asked for and where the data supposedly came from and how we would get it again.
There’s also going to be sub-projects galore that are unexpected and we will have to do rigorous rabbit-hole rig rappelling to get in there quick, check out what kind of wonderland we’re dealing with (or are going to refuse to) and get out quick. If we fall in love with that wonderland but it’s not where we should be the Pomodoro clock on which the rabbit hole rappelling rig runs winches retracting us back up like clockwork. We do not really need a Pomodoro clock because we’ve got you and really wonderful vignettes like that land and stick (don’t they?) and you won’t let us do that.
Probe:
$ git status
On branch main
Your branch is up to date with 'origin/main'.
nothing to commit, working tree clean
(nix) pipulate $ ls scripts/connectors/
grep -c "alias sheets=" flake.nix
jq 'to_entries | map(select(.value|type=="object")) | map({connector: .key, fields: (.value | keys)})' ~/.config/pipulate/connectors.json
.venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
.venv/bin/python -c "import importlib.util as u; s=u.spec_from_file_location('sheets','scripts/connectors/sheets.py'); m=u.module_from_spec(s); s.loader.exec_module(m); print(m.parse_spreadsheet_ref('https://docs.google.com/spreadsheets/d/1AbC-xyz_0123456789abcdefghij/edit#gid=42'))"
.venv/bin/python scripts/connectors/sheets.py
botify.py confluence.py gmail.py gsc.py README.md
0
[
{
"connector": "gmail",
"fields": [
"auth",
"defaults",
"env",
"paths"
]
},
{
"connector": "botify",
"fields": [
"auth",
"defaults",
"env"
]
},
{
"connector": "confluence",
"fields": [
"auth",
"defaults",
"env"
]
},
{
"connector": "gsc",
"fields": [
"auth",
"defaults",
"env",
"paths"
]
}
]
Traceback (most recent call last):
File "<string>", line 1, in <module>
FileNotFoundError: [Errno 2] No such file or directory: 'scripts/connectors/sheets.py'
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "<frozen importlib._bootstrap_external>", line 995, in exec_module
File "<frozen importlib._bootstrap_external>", line 1132, in get_code
File "<frozen importlib._bootstrap_external>", line 1190, in get_data
FileNotFoundError: [Errno 2] No such file or directory: '/home/mike/repos/pipulate/scripts/connectors/sheets.py'
.venv/bin/python: can't open file '/home/mike/repos/pipulate/scripts/connectors/sheets.py': [Errno 2] No such file or directory
(nix) pipulate $
Context:
# adhoc.txt _ _ _ ____ _ _ ___ ____ _
# / \ __| | | | | | ___ ___ / ___| | | |/ _ \| _ \| | Alright, I like where this is going. Let's do some Quality Assurance!
# / _ \ / _` | | |_| |/ _ \ / __| | | | |_| | | | | |_) | | Given how I feel about proper nouns and the people in those professions, it should be impressive I'm Upper-Casing them now.
# / ___ \ (_| | | _ | (_) | (__ | |___| _ | |_| | __/|_| It must be because I'm one of them now.
# /_/ \_\__,_| |_| |_|\___/ \___| \____|_| |_|\___/|_| (_) But this is *delightful* computing now.
# AD HOC CHOP: The Not-Managed-by-Git Safe-for-Client-Data place. I made Q/A delightful. WOOT!
# 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. ---
! ls scripts/connectors/
! grep -c "alias sheets=" flake.nix
! jq 'to_entries | map(select(.value|type=="object")) | map({connector: .key, fields: (.value | keys)})' ~/.config/pipulate/connectors.json
! .venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
! .venv/bin/python -c "import importlib.util as u; s=u.spec_from_file_location('sheets','scripts/connectors/sheets.py'); m=u.module_from_spec(s); s.loader.exec_module(m); print(m.parse_spreadsheet_ref('https://docs.google.com/spreadsheets/d/1AbC-xyz_0123456789abcdefghij/edit#gid=42'))"
! .venv/bin/python scripts/connectors/sheets.py
scripts/connectors/sheets.py
scripts/connectors/README.md
flake.nix
# https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit?usp=sharing
! .venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
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
✅ WHOLE-FILE WRITE: CREATED 'scripts/connectors/sheets.py'.
(nix) pipulate $ d
(nix) pipulate $ git add scripts/connectors/sheets.py
(nix) pipulate $ m
📝 Committing: chore: Update sheets.py with expanded Google Sheets integration and contextual usage guidance
[main 8fa25c67] chore: Update sheets.py with expanded Google Sheets integration and contextual usage guidance
1 file changed, 298 insertions(+)
create mode 100644 scripts/connectors/sheets.py
(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 4942894e..fce026cb 100644
--- a/scripts/connectors/README.md
+++ b/scripts/connectors/README.md
@@ -49,6 +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 (service_account_file; reuses gsc's key with the Sheets readonly scope; wallet descent type-checks non-object entries)
## Minting a new connector
(nix) pipulate $ m
📝 Committing: chore: Update sheets.py documentation
[main e91c5503] chore: Update sheets.py documentation
1 file changed, 1 insertion(+)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'flake.nix'.
(nix) pipulate $ d
diff --git a/flake.nix b/flake.nix
index e22e96d9..cd66d816 100644
--- a/flake.nix
+++ b/flake.nix
@@ -888,6 +888,7 @@ runScript = pkgs.writeShellScriptBin "run-script" ''
alias botify='"$PIPULATE_ROOT/.venv/bin/python" "$PIPULATE_ROOT/scripts/connectors/botify.py"'
alias confluence='"$PIPULATE_ROOT/.venv/bin/python" "$PIPULATE_ROOT/scripts/connectors/confluence.py"'
alias gsc='"$PIPULATE_ROOT/.venv/bin/python" "$PIPULATE_ROOT/scripts/connectors/gsc.py"'
+ alias sheets='"$PIPULATE_ROOT/.venv/bin/python" "$PIPULATE_ROOT/scripts/connectors/sheets.py"'
alias vim='nvim'
alias lsp='ls -d -1 "$PWD"/*'
alias p='cd ~/repos/pipulate'
(nix) pipulate $ m
📝 Committing: chore: Add sheets connector script alias
[main 984920e7] chore: Add sheets connector script alias
1 file changed, 1 insertion(+)
(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 75a0f402..5aeeceeb 100644
--- a/foo_files.py
+++ b/foo_files.py
@@ -847,6 +847,8 @@ scripts/connectors/README.md
scripts/connectors/gmail.py
scripts/connectors/botify.py
scripts/connectors/confluence.py
+scripts/connectors/gsc.py
+scripts/connectors/sheets.py
# _____ _ _ _ _
# | ___(_)_ __ __| | __ _ | | | | ___ _ __ ___ ___
# | |_ | | '_ \ / _` | / _` | | |_| |/ _ \| '_ ` _ \ / _ \
(nix) pipulate $ m
📝 Committing: chore: Add gsc and sheets connectors to scripts/connectors/
[main 1c0d90ee] chore: Add gsc and sheets connectors to scripts/connectors/
1 file changed, 2 insertions(+)
(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 5aeeceeb..62d46e9d 100644
--- a/foo_files.py
+++ b/foo_files.py
@@ -1213,6 +1213,7 @@ scripts/xp.py # [1,981 tokens | 8,377 bytes]
# - A better book-outliner method than K-means for "real" structure
# - Only ever 301-redirect hubs to hubs. Don't put article leafs on hub-rot.
# - OKF/Agent Skills 80/20 LANDED 2026-07-20 (AGENTS.md signpost + ContextCartridge topper, receipts banked). Remainder: OKF bundle export of trimnoir + Google's static visualizer as a free hub-structure audit.
+# - EARMARK: THREE-STANDARDS SUPERPOSITION (banked 2026-07-20): AGENTS.md = one freeform signpost file, nearest-ancestor wins; Agent Skills = folder fronted by SKILL.md YAML frontmatter, progressive disclosure (Notebooks/.agents/skills already conforms); OKF = markdown+YAML bundle with index.md and one required field (type) — trimnoir _posts + holographic shards + llms.txt are one avant la lettre. DIRECTION OF TRAVEL for scripts/: every new capability lands as a tiny gmail-shaped connector command; SKILL.md/OKF files are SIGNPOSTS to executable truth, never second implementations. Symlinks in spirit, never on disk. WALLET LESSON (jq conviction 2026-07-20): connectors.json carries non-object top-level entries — every wallet reader type-checks before descending.
# - TOOL ROSTER LANDED 2026-07-20 (generated-not-authored section between Manifest and Story; count matched live_registry_count=21 receipt in the same compile). EARMARK: split tool METADATA discovery from runtime-heavy imports — imports.voice_synthesis alone costs ~3.8s of the ~5s registry import (importtime receipt 2026-07-20); enumerate names without importing engines, roster stays generated either way.
# - Implement NLWeb, WebMCP, UCP and ACP.
# - I think articles get read twice on first publish
(nix) pipulate $ m
📝 Committing: chore: Refactor code comments in foo_files.py
[main 693a2a65] chore: Refactor code comments in foo_files.py
1 file changed, 1 insertion(+)
(nix) pipulate $ git push
Enumerating objects: 25, done.
Counting objects: 100% (25/25), done.
Delta compression using up to 48 threads
Compressing objects: 100% (19/19), done.
Writing objects: 100% (19/19), 6.70 KiB | 3.35 MiB/s, done.
Total 19 (delta 13), reused 0 (delta 0), pack-reused 0 (from 0)
remote: Resolving deltas: 100% (13/13), completed with 6 local objects.
To github.com:pipulate/pipulate.git
375435f0..693a2a65 main -> main
(nix) pipulate $
One thing to notice and it’s easy to miss is that there’s changes in flake.nix
which means changes in the shell environment which are cached and require an
exit and ndq to activate:
(sys) pipulate $ ndq
warning: updating lock file '/home/mike/repos/pipulate/flake.lock':
• Added input 'flake-utils':
'github:numtide/flake-utils/11707dc2f618dd54ca8739b309ec4fc024de578b?narHash=sha256-l0KFg5HjrsfsO/JpG%2Br7fRrqm12kzFHyUHqHCVpMMbI%3D' (2024-11-13)
• Added input 'flake-utils/systems':
'github:nix-systems/default/da67096a3b9bf56a91d16901293e51ba5b49a27e?narHash=sha256-Vy1rq5AaRuLzOxct8nz4T6wlgyUR7zLU309k9mBC768%3D' (2023-04-09)
• Added input 'nixpkgs':
'github:NixOS/nixpkgs/241313f4e8e508cb9b13278c2b0fa25b9ca27163?narHash=sha256-vlHUuqAcbcH2RKmHbPiuQzbv1pnzzavXnI62RD0bqCU%3D' (2026-07-19)
(nix) pipulate $
Okay, excellent. Now we are going to change the scope of what the end of this Worm-ride looks like because we encountered a nested sub-project in this Google Sheets connector and I want a clean domain for each article’s focus and specialization. This is going to become the process of building these little Unix-like chop chop kung fu reusable composable tools that keep paying back forever forward and also power the MCP versions of the same things (eventually) and deal with OAuth after OAuth, once again flipping the story of how everything is easy until you encounter authentication upside down. It’s never a problem (so long as it fits a pattern we’re already sitting on) and doesn’t slow this process down hardly one wit. We like auth! It gives us a chance to revisit how we organize our wallet and do the off-disk backups better but we’ll get to more of that later. Make sure our To-Do list is aligned.
Prompt:
Verify against THIS compile’s receipts only: sheets.py AST OK, the URL-parser receipt printing (‘1AbC-xyz_0123456789abcdefghij’, 42), the identity receipt printing the service account’s client_email headlessly, the alias grep at 1, and the object-guarded jq wallet listing. If any receipt contradicts the prediction, diagnose that first — squeeze the falsification before advancing.
If all green and a live Sheet receipt is present in the compile (a real URL shared to the client_email as Viewer), read the LIST size gauge and confirm the defensive posture worked: tab sizes visible before any fetch, big tabs flagged, no context overflow. Then take the next chisel-strike toward adversarial TQM: sketch (do not yet build) measure.py — the second nail that consumes bounded connector stdout (sheets/gsc/botify TSV or JSON) and renders falsifiable pass/fail verdicts against an explicit metric contract, keeping acquisition and judgment as separate composable commands. Also propose the minimal Notebooks/.agents/skills/sheets_readonly/SKILL.md signpost (frontmatter
- pointer to the executable, no second implementation). Emit the full patch train.
🎤
Fable 5 High: Context verified.
Receipt verification (Manifest list as sole authority)
Five predictions, five receipts, four green and one scrubbed-green:
- AST probe —
sheets.py AST: OK. ✅ - URL parser —
('1AbC-xyz_0123456789abcdefghij', 42), exactly as predicted, gid extraction included. ✅ - Alias grep —
1. The DELTA-NOT-ABSOLUTE rule is satisfied properly this time: your hand-run BEFORE reading was 0 (in the transcript), this compile’s AFTER receipt is 1. The 0→1 delta straddles the patch cleanly. ✅ - Object-guarded jq — succeeds now, listing four connector objects (gmail, botify, confluence, gsc). No
sheetsentry exists, which is fine by design: the fallback chain (sheets→gsc→ canonical key path) carries it. The guard fix that a failed probe wrote for us is now a working probe. ✅ - Identity receipt — headless, zero-network, prints the key path and identity. The email reads
[email redacted]in this payload — that is not a defect in the connector, it’s the compile-lane PII scrub firing on a service-account email. Two proofs in one receipt: the connector is headless-by-construction, and the substitution table covers Google service identities. You read the real value locally; the cloud lane never does. ✅
The falsification to squeeze
One command ran this compile that does not appear in the Manifest’s receipts list: the live LIST against the real spreadsheet. It exited 1 with empty stdout, so the executor dropped it from the payload — but the Processing Log in the Summary (same-compile evidence) preserved its last words:
403 SERVICE_DISABLED — Google Sheets API has not been used in project 279635155797
Squeeze it for everything it’s worth:
First, the connector’s error path worked exactly as designed: stderr carried the diagnosis, named both possible causes, and Google’s own ErrorInfo pinned the actual one. Second, and this is the real learning: a Google service-account 403 is a two-gate failure with a mandatory order. Gate one is SERVICE_DISABLED — the Sheets API toggle in the key’s Cloud project, flipped once per API per project, forever. Gate two is PERMISSION_DENIED — the per-document Viewer share to the client_email. This receipt convicts gate one and tells us nothing about gate two. After you enable the API, the identical probe may fail again on sharing — and that would be a second distinct falsification, not a repeat. Third: this is a claimed lag, not a bug. The External Deliverables from last turn listed API enablement as manual step 1; it simply hasn’t happened yet. Per the double-tap discipline, the identical probe re-fires next compile as the closing receipt.
Consequence for the size gauge: I cannot confirm the defensive posture (tab sizes visible before fetch, big-tab flags) — no live LIST has ever succeeded. That claim stays open until the post-enablement receipt lands. Honest scoreboard: connector mechanics fully verified; connector contact with reality pending one console toggle.
measure.py — the sketch (not built)
The second nail, kept deliberately ignorant of Google, Botify, and everything else upstream:
measure.py <contract.yaml> [< data.tsv] # verdict on stdin data
measure.py <contract.yaml> --in results.json # or a named file
The contract file is the falsifiable claim made explicit:
claim: "Organic clicks increased post-deployment"
input: {format: tsv, columns: [date, page, clicks, impressions]}
primary:
- metric: sum(clicks) where date >= 2026-07-01
op: ">"
baseline: sum(clicks) where date < 2026-07-01
adversarial:
- name: outlier_page_dominance
assert: top_page_share(clicks, post) < 0.5
- name: coverage_constant
assert: distinct(page, post) >= 0.9 * distinct(page, pre)
verdict: all(primary) and all(adversarial)
Output: one line per check (PASS/FAIL + the numbers), exit code as the verdict — which makes measure.py itself a !-line probe, meaning the QA verdict becomes a live receipt in the next compile. The adversary is codified as “the easiest surviving false explanation,” one assertion per escape route. evidence.py (third nail, later) zips claim + inputs + results + hashes into the proof-carrying cartridge. Acquisition, judgment, packaging: three commands, never welded. The first real contract gets written against the sheets live receipt once gate one clears — the sub-project boundary you just drew.
Todo alignment
The wallet/off-disk-backup thread you flagged is already banked (VAULT LANE + SECRET_TRIPWIRES + CACHE-CHURN todos cover it — nothing to add there). What’s missing: the two-gate 403 lesson and the adversarial-TQM lane opening. Two lines, patched below.
1. PROBES
The first is the mandatory double-tap on the live LIST — it gates on you completing External Deliverable step 1 first, and its outcome discriminates gate one from gate two. The second and third are BEFORE readings for the SKILL.md car (the sed will fail file-not-found pre-patch; that failure is the baseline). The fourth is the todo-line delta, hand-run baseline predicted 0, post-patch 1.
.venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
ls Notebooks/.agents/skills/
sed -n '1,12p' Notebooks/.agents/skills/sheets_readonly/SKILL.md
grep -c "ADVERSARIAL TQM LANE" foo_files.py
2. NEXT CONTEXT
! .venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
! ls Notebooks/.agents/skills/
! sed -n '1,12p' Notebooks/.agents/skills/sheets_readonly/SKILL.md
! grep -c "ADVERSARIAL TQM LANE" foo_files.py
scripts/connectors/sheets.py
scripts/connectors/README.md
foo_files.py
3. PATCHES
Car 1 — the SKILL.md signpost (new file, frontmatter + pointer, no second implementation):
Target: Notebooks/.agents/skills/sheets_readonly/SKILL.md
[[[WRITE_FILE]]]
---
name: sheets_readonly
description: Read-only Google Sheets access for bounded context pulls. Trigger when a Google Sheets URL or spreadsheet ID needs to become compiled context. Executable truth lives at scripts/connectors/sheets.py; this skill is a signpost, never a second implementation.
---
# sheets_readonly — signpost to scripts/connectors/sheets.py
The executable is the specification. Run it; do not reimplement it.
## The three moves
1. Identity (get the share target):
`python scripts/connectors/sheets.py`
Prints the service-account client_email. Share each target spreadsheet
with that email as Viewer.
2. LIST with size gauge:
`python scripts/connectors/sheets.py "<URL-or-ID>"`
Every tab's rows x cols x ~cells prints BEFORE any fetch; overflow-shaped
tabs are flagged. A #gid= fragment in a pasted URL selects that tab and
performs a bounded fetch of it directly.
3. Bounded FETCH:
`--sheet "<Tab>"` (row-bounded server-side) or `--range "'Tab'!A1:F50"`,
capped by -n/--max (default 25), `--format tsv|json|markdown` (tsv default).
## Rules inherited from the connector contract
- THE PROBE ECONOMY RULE: read the LIST size gauge before any fetch.
- Client URLs and spreadsheet IDs live in adhoc.txt, never in tracked source.
- TWO-GATE 403 DIAGNOSIS, in order: SERVICE_DISABLED (enable the Sheets API
in the key's Cloud project, once per project) before PERMISSION_DENIED
(share the document with the client_email, once per document).
- Errors and auth guidance ride stderr; stdout stays parseable.
See scripts/connectors/README.md for the full connector contract.
[[[END_WRITE_FILE]]]
Car 2 — todo alignment (two lines: the 403 lesson and the TQM lane):
Target: foo_files.py
[[[SEARCH]]]
# - 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.
[[[DIVIDER]]]
# - 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.
[[[REPLACE]]]
Actuation: patch, app, d, m per car; blast as the caboose. No flake.nix change this turn, so no exit/ndq re-entry needed.
4. PROMPT
Verify against THIS compile's receipts only. The critical receipt is the
double-tap live LIST of the shared spreadsheet: if it now succeeds, read the
size gauge and confirm the defensive posture (tab dimensions visible before
any fetch, big tabs flagged) — that closes the sheets connector sub-project
and its claim. If it fails 403 PERMISSION_DENIED, gate two is convicted:
sharing, not enablement — diagnose and stop there. If it still shows
SERVICE_DISABLED, the console toggle has not propagated; do not advance.
If the live receipt is green: this ride's stated scope is reached. Dismount
posture — VERIFY the connector claim against the size-gauge receipt, BANK the
sheets_readonly SKILL.md and the two todo lines as landed (confirm via the
grep and sed receipts), name anything DANGLING (measure.py remains a sketch;
evidence.py unstarted; wallet sheets entry optional), and SEED the next
ride's first compile: the adhoc.txt lines to begin measure.py — a real
metric contract written against actual cells fetched from the live sheet,
with the contract file itself as the first artifact of the adversarial TQM
lane.
5. EXTERNAL DELIVERABLES
Manual, outside this repo, and gate-ordered:
- Gate one: enable the Google Sheets API for the key’s Cloud project at the activation URL Google printed in the 403 (the console names project 279635155797 directly). Allow a few minutes to propagate.
- Gate two: confirm the spreadsheet is shared as Viewer with the client_email that
python scripts/connectors/sheets.pyprints locally (the payload shows it redacted; your terminal shows it plain). - The real URL stays in
adhoc.txtonly — it’s already riding there correctly as the double-tap line.
MikeLev.in: Okay, I turn on the Google Sheets API through the Google Cloud Platform. I had to do a bit of detective work to see which account and which project. Once I saw which they were, it made sense. So Google Sheets API is now enabled. Let’s do it again!
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 2775a634] chore(router): set AI-edit blast boundary (foo_files.py context curation)
1 file changed, 1 deletion(-)
🚀 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), 346 bytes | 346.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
693a2a65..2775a634 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 "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
ls Notebooks/.agents/skills/
sed -n '1,12p' Notebooks/.agents/skills/sheets_readonly/SKILL.md
grep -c "ADVERSARIAL TQM LANE" foo_files.py
Sheets API error: <HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho?fields=properties.title%2Csheets.properties&alt=json returned "Google Sheets API has not been used in project 279635155797 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=279635155797 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.". Details: "[{'@type': 'type.googleapis.com/google.rpc.ErrorInfo', 'reason': 'SERVICE_DISABLED', 'domain': 'googleapis.com', 'metadata': {'service': 'sheets.googleapis.com', 'containerInfo': '279635155797', 'consumer': 'projects/279635155797', 'serviceTitle': 'Google Sheets API', 'activationUrl': 'https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=279635155797'}}, {'@type': 'type.googleapis.com/google.rpc.LocalizedMessage', 'locale': 'en-US', 'message': 'Google Sheets API has not been used in project 279635155797 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=279635155797 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.'}, {'@type': 'type.googleapis.com/google.rpc.Help', 'links': [{'description': 'Google developers console API activation', 'url': 'https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=279635155797'}]}]">
If this is 403/404: confirm the Google Sheets API is enabled for the
key's Cloud project AND the spreadsheet is shared with the service
account's client_email (run with no argument to print it).
gsc_readonly hello_workflow roles
sed: can't read Notebooks/.agents/skills/sheets_readonly/SKILL.md: No such file or directory
0
(nix) pipulate $
Context:
# adhoc.txt _ _ _ ____ _ _ ___ ____ _
# / \ __| | | | | | ___ ___ / ___| | | |/ _ \| _ \| | This is getting fun!
# / _ \ / _` | | |_| |/ _ \ / __| | | | |_| | | | | |_) | | It's almost like I can hear the marching music.
# / ___ \ (_| | | _ | (_) | (__ | |___| _ | |_| | __/|_| Probe, Patches, Prompt. Probe, Patches, Prompt.
# /_/ \_\__,_| |_| |_|\___/ \___| \____|_| |_|\___/|_| (_) patch, app, d, m, patch, app, d, m
# AD HOC CHOP: The Not-Managed-by-Git Safe-for-Client-Data place. OMG, what psychology principles are at work here? I mean that AI! Answers expected.
# 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 "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
! ls Notebooks/.agents/skills/
! sed -n '1,12p' Notebooks/.agents/skills/sheets_readonly/SKILL.md
! grep -c "ADVERSARIAL TQM LANE" 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
✅ WHOLE-FILE WRITE: CREATED 'Notebooks/.agents/skills/sheets_readonly/SKILL.md'.
(nix) pipulate $ git add Notebooks/.agents/skills/sheets_readonly/SKILL.md
(nix) pipulate $ m
📝 Committing: chore: Add SKILL.md documentation for sheets_readonly skill
[main 22f24e71] chore: Add SKILL.md documentation for sheets_readonly skill
1 file changed, 36 insertions(+)
create mode 100644 Notebooks/.agents/skills/sheets_readonly/SKILL.md
(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 af2bbf71..d08127af 100644
--- a/foo_files.py
+++ b/foo_files.py
@@ -1228,6 +1228,8 @@ scripts/xp.py # [1,981 tokens | 8,377 bytes]
# - VAULT LANE (banked 2026-07-20, probe receipts in-compile): scripts/vault_snapshot.py — explicit manifest of ~/.config/pipulate secrets+config ONLY (api_key.txt, credentials.json, gmail_token.json, keys.json, service-account-key.json, connectors.json, blogs.json, commit_denylist.txt, disclosure.json, flippers.json, pii_substitutions.txt; plus repo-side .env and .ssh/rot). EXCLUDE fm_cache.json + token_cache.json (both stamped 09:13 at probe — they churn every run and defeat the hash gate). Crypto: openssl is the ONLY tool present (receipt) — `openssl enc -aes-256-cbc -pbkdf2` zero-dep, or add `age` to flake.nix commonPackages first. Destinations: /mnt/internal_backup (sda1, mounted, receipt) always; /mnt/essentials USB opportunistic (ABSENT at probe — confirms skip-if-missing). Hook: fail-soft 10s subprocess at end of prompt_foo.py main(); one receipt line either way.
# - 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.
# ============================================================================
# VIII. THE PAINTBOX (Unused Colors)
(nix) pipulate $ m
📝 Committing: chore: Refactor: Implement two-gate 403 diagnosis and adversarial TQM lane details
[main b2daaff1] chore: Refactor: Implement two-gate 403 diagnosis and adversarial TQM lane details
1 file changed, 2 insertions(+)
(nix) pipulate $ git push
Enumerating objects: 15, done.
Counting objects: 100% (15/15), done.
Delta compression using up to 48 threads
Compressing objects: 100% (8/8), done.
Writing objects: 100% (10/10), 2.54 KiB | 1.27 MiB/s, done.
Total 10 (delta 5), reused 0 (delta 0), pack-reused 0 (from 0)
remote: Resolving deltas: 100% (5/5), completed with 4 local objects.
To github.com:pipulate/pipulate.git
2775a634..b2daaff1 main -> main
(nix) pipulate $
Prompt:
Verify against THIS compile’s receipts only. The critical receipt is the double-tap live LIST of the shared spreadsheet: if it now succeeds, read the size gauge and confirm the defensive posture (tab dimensions visible before any fetch, big tabs flagged) — that closes the sheets connector sub-project and its claim. If it fails 403 PERMISSION_DENIED, gate two is convicted: sharing, not enablement — diagnose and stop there. If it still shows SERVICE_DISABLED, the console toggle has not propagated; do not advance.
If the live receipt is green: this ride’s stated scope is reached. Dismount posture — VERIFY the connector claim against the size-gauge receipt, BANK the sheets_readonly SKILL.md and the two todo lines as landed (confirm via the grep and sed receipts), name anything DANGLING (measure.py remains a sketch; evidence.py unstarted; wallet sheets entry optional), and SEED the next ride’s first compile: the adhoc.txt lines to begin measure.py — a real metric contract written against actual cells fetched from the live sheet, with the contract file itself as the first artifact of the adversarial TQM lane.
🎤
Fable 5 High: Context verified.
Receipt verification (Manifest list as sole authority)
This compile’s LIVE COMMAND RECEIPTS are: the rolling pin, ls Notebooks/.agents/skills/, the sed on SKILL.md, and the grep -c. Reading them:
- SKILL.md landed —
lsnow showssheets_readonlyalongside the three prior skills (your hand-run BEFORE showed three), and thesedreceipt prints the frontmatter where the BEFORE reading wascan't read. Both deltas straddle the patch cleanly. ✅ BANKED. - Todo lines landed —
grep -c "ADVERSARIAL TQM LANE"reads 1, matching the predicted 0→1 delta from your hand-run baseline. The TWO-GATE 403 earmark rides in the same visible diff. ✅ BANKED. - The critical receipt: still gate one. The live LIST is absent from the Manifest’s receipts list — but this compile’s own Processing Log preserves its last words:
403 SERVICE_DISABLEDfor project 279635155797, again, post-enablement. Per your standing instruction: do not advance. No dismount, no measure.py seeding this turn.
Two falsifications to squeeze
First: why is it still SERVICE_DISABLED after you enabled it? Two live hypotheses, in order of likelihood: (a) wrong project — the Cloud Console’s project selector defaults to whatever you last touched, and the detective work may have landed on a plausible-but-wrong project. The key’s project is not negotiable: Google derives the consumer from the credential itself, and it says 279635155797. (b) propagation delay — Google’s own error text warns of minutes of lag, and your hand-run came seconds after the toggle. The cheapest discriminator: print the key’s project_id locally and compare it digit-for-digit with the project name shown in the console header when you flipped the switch. The probe below does exactly that.
Second: the executor ate the conviction. The failed live LIST exited 1 with empty stdout, so the ! executor raised and dropped it — the most important receipt of the compile survives only in the Processing Log, not in the Manifest’s authoritative list. The connector contract says “a clean failure is a valid receipt,” but the compile lane currently disagrees. That’s a mechanism gap worth one chisel-strike: promote stderr-only failures into first-class receipts. Per THE PENDING AMENDMENT RULE this ships tagged PENDING with a deliberate failing canary to witness it next compile.
On the masthead question — what psychology is at work: mostly operant conditioning with immediate variable rewards (every green receipt is a small payout on a fast schedule), the Zeigarnik effect (PENDING/OWES lines keep open loops itching to close), implementation intentions ritualized into “Probe, Patch, Prompt” so no decision costs willpower, and flow from feedback latency measured in seconds rather than sprints. The marching-music feeling is the ritual chunking: patch, app, d, m is a mantra, and mantras are how humans myelinate.
1. PROBES
The first is the triple-tap on the live LIST (gate-one discriminator; run it a few minutes after confirming the toggle). The second prints the key’s actual project_id via the connector’s own resolver — if it doesn’t read 279635155797, the console detective work found the wrong project. The third is the deliberate failure canary: it MUST land in the next compile’s Manifest with a stderr fence, witnessing the executor patch.
.venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
.venv/bin/python -c "import importlib.util as u, json; s=u.spec_from_file_location('sh','scripts/connectors/sheets.py'); m=u.module_from_spec(s); s.loader.exec_module(m); p=m.resolve_key_path(); print('key:', p); print('project_id:', json.loads(p.read_text()).get('project_id'))"
bash -c "echo canary-stderr >&2; exit 3"
2. NEXT CONTEXT
! .venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
! .venv/bin/python -c "import importlib.util as u, json; s=u.spec_from_file_location('sh','scripts/connectors/sheets.py'); m=u.module_from_spec(s); s.loader.exec_module(m); p=m.resolve_key_path(); print('key:', p); print('project_id:', json.loads(p.read_text()).get('project_id'))"
! bash -c "echo canary-stderr >&2; exit 3"
prompt_foo.py
scripts/connectors/sheets.py
foo_files.py
3. PATCHES
Car 1 — the executor learns that failure is a receipt:
Target: prompt_foo.py
[[[SEARCH]]]
if proc.returncode != 0 and not cmd_stdout.strip():
raise subprocess.CalledProcessError(proc.returncode, command_str, output=cmd_stdout, stderr=cmd_stderr)
content = cmd_stdout.strip() or "(Executed successfully, no output)"
[[[DIVIDER]]]
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
# all-stderr failure is a valid receipt, not a skip. Conviction:
# the 2026-07-20 SERVICE_DISABLED live LIST vanished from the
# Manifest because empty stdout raised here; only the Processing
# Log preserved it. Empty stdout with nonempty stderr now falls
# through so the stderr merge below lands it as a receipt.
if proc.returncode != 0 and not cmd_stdout.strip():
content = "(no stdout — stderr is the receipt)"
else:
content = cmd_stdout.strip() or "(Executed successfully, no output)"
[[[REPLACE]]]
Car 2 — the PENDING line and its witness contract:
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 (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.
# - 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.
[[[REPLACE]]]
Actuation: patch, app, d, m per car; blast as the caboose. No flake.nix change, so no exit/ndq re-entry.
4. PROMPT
Verify against THIS compile's receipts only, in this order:
1. CANARY: the bash canary must appear in the Manifest's LIVE COMMAND
RECEIPTS with "# NON-ZERO EXIT 3" and a stderr fence carrying
"canary-stderr". If present, flip the FAILED-PROBE RECEIPT line from
PENDING to banked (emit that patch) and instruct deleting the canary
from adhoc.txt. If absent, the executor patch failed — diagnose first.
2. PROJECT IDENTITY: compare the project_id receipt against 279635155797.
A mismatch convicts wrong-project enablement — say so and stop the
sheets lane there with the exact console URL to fix it.
3. LIVE LIST: if it succeeds, read the size gauge, confirm the defensive
posture (tab dimensions visible before any fetch, big tabs flagged),
and enter dismount posture — VERIFY the connector claim, BANK, name
DANGLING (measure.py sketch, evidence.py unstarted, wallet sheets
entry optional), SEED the measure.py ride with adhoc.txt lines that
fetch real cells and write the first metric contract. If it fails
PERMISSION_DENIED, gate two is convicted: sharing, not enablement —
diagnose and stop. If still SERVICE_DISABLED with a matching
project_id, propagation is the last suspect standing; hold position.
5. EXTERNAL DELIVERABLES
Manual, outside this repo:
- Visit the exact activation URL from the error —
https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=279635155797— rather than navigating by project name. The?project=parameter forces the console onto the key’s project; if the page shows “Enable” rather than “Manage,” the earlier toggle landed elsewhere. - After it shows “Manage,” wait the few minutes Google warns about, then hand-run probe 1 for the BEFORE reading.
- Gate two stays queued: the spreadsheet must be shared as Viewer with the client_email your terminal prints — untested until gate one clears.
MikeLev.in: There’s only enough time and energy in the day for 2 or 3 really good thorough Worm-rides, and as you can see even just a nested sub-project, a rabbit hole you chose to go down because it was in the critical path of the work your facing, reduces a recurring friction point helping all similar high frequency type work that you encounter moving forward and follows a pattern you already established. That’s a Rabbit Hole Project worthy of Upper Case. We like the right Rabbit Hole projects. Familiar Wonderlands ROCK!
This is where we have to start thinking as a human. Keeping these
authentications is going to be a real left-hand / right-hand keeping them
knowing what they’re doing thing. I see a really descriptive wallet management
file in my future. But for right now what we need to know is that we’re not
using a service account! That was residue from that old gsc thing. I should
have recognized that. We’re using OAuth. Nobody using Pipulate will have rights
or the discipline or should even be expected to have one of those nasty things.
Let’s stick with OAuth. The pattern is in there too. Also, the project ID we’re
looking for is work-integrations-500916 and it’s in
~/.config/pipulate/credentials.json.
Authentication Strategy: OAuth Over Service Accounts
Ugh! This is going to be messy. Look how I organize this kind of stuff with blogs. I’ll put that in the context, and we don’t have to go there right now but we’re going to want a single JSON file where a person can manage and provide excellent descriptions of what credentials is what. Especially don’t worry about the Nix thing now, but thought you ought to see that. I am very serious about my immutable machines and wrapping very well organized configuration from other domains than just hardware and app management into that. I imagine a whole bunch of stuff that could get confused with each other being in there and we want it to be illuminating and joyful to have to interact with — the cure to opacity issues that usually surround these authentication issues, but of course while keeping all secrets secure.
I don’t even know how to change that things to wire it up to the correct file so
I’m going to stop the normal protocol right here and just post this, but of
course with an Ad Hoc CHOP!.
🎤
Fable 5 High: Context verified.
Receipt verification (Manifest list as sole authority)
This compile’s LIVE COMMAND RECEIPTS: the rolling pin, ls Notebooks/.agents/skills/ (now showing sheets_readonly — SKILL.md car BANKED against the prior can't read baseline), the sed printing its frontmatter (same conviction), and grep -c "ADVERSARIAL TQM LANE" at 1 (0→1 delta, todo car BANKED). The live sheets LIST ran again, failed again on SERVICE_DISABLED for project 279635155797, and again vanished from the Manifest — only the Processing Log kept its last words. Also worth noting: the previous turn’s executor patch and canary were never applied (this compile’s prompt_foo.py still carries the old raise-on-empty-stdout line, and no PENDING line exists in foo_files.py). That’s fine — you called the halt deliberately.
The falsification, squeezed properly this time
You just out-diagnosed the two-gate model. The toggle didn’t fail to propagate and the console detective work wasn’t wrong — the credential was wrong. sheets.py inherited gsc’s service-account plumbing by pattern-proximity, so you were enabling the API in the right project for a credential you never intended to use. Google’s 403 named 279635155797 because that’s the service-account key’s project; your actual identity for this lane is the Desktop-app OAuth client in work-integrations-500916 — the exact same credentials.json that gmail.py already drives. This is a genuinely better outcome than the two-gate model predicted:
- OAuth kills gate two entirely. No sharing dance, no client_email, no Viewer grants. Whatever Sheets your Google account can open, the connector can read. That’s the right model for Pipulate users, none of whom should ever be handed a service-account key.
- Gate one moves to work-integrations-500916 (Sheets API toggle there, once, forever).
- The pattern is already on the shelf — gmail.py’s mint-once-refresh-forever token flow copies over nearly verbatim, with one law: a separate
sheets_token.json, because token files are scope-scoped and gmail’s token must never be poisoned.
And your masthead’s “auth is never a problem” holds: this cost one wrong-project console visit and produces two banked rules plus the wallet-descriptor lane you’re now foreseeing. Familiar Wonderland, upper case earned.
The executor patch rides again this turn — it’s now load-bearing: an OAuth connector run headless before its token is minted fails with stderr-only guidance, which is exactly the receipt shape the executor currently eats.
1. PROBES
Hand-run these as BEFORE readings. The jq confirms your project claim from the credentials file itself. The ls on the token will fail — that failure is the baseline (token not yet minted). The grep predicts 0 → 2 (import + use). The AST probe gates syntax across the patch. The canary must surface in the next compile’s Manifest to flip the executor amendment PENDING→banked.
jq -r '.installed.project_id // .web.project_id' ~/.config/pipulate/credentials.json
ls -l ~/.config/pipulate/sheets_token.json
grep -c "InstalledAppFlow" scripts/connectors/sheets.py
.venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
bash -c "echo canary-stderr >&2; exit 3"
After the patches land and the Sheets API is enabled in work-integrations-500916, run the mint interactively (.venv/bin/python scripts/connectors/sheets.py in a real terminal — identity mode mints the token via the one-time browser handshake), then hand-run the live LIST once for its BEFORE reading.
2. NEXT CONTEXT
! jq -r '.installed.project_id // .web.project_id' ~/.config/pipulate/credentials.json
! ls -l ~/.config/pipulate/sheets_token.json
! grep -c "InstalledAppFlow" scripts/connectors/sheets.py
! .venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
! bash -c "echo canary-stderr >&2; exit 3"
! .venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
scripts/connectors/sheets.py
scripts/connectors/gmail.py
scripts/connectors/README.md
prompt_foo.py
foo_files.py
3. PATCHES
Car 1 — sheets.py docstring: the auth story changes:
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
Auth (service_account_file — headless by construction, no browser dance ever):
PIPULATE_SHEETS_KEY env var
-> ~/.config/pipulate/connectors.json sheets.paths.service_account
-> ~/.config/pipulate/connectors.json gsc.paths.service_account
(the SAME key JSON serves both Google APIs; only the scope differs)
-> ~/.config/pipulate/service-account-key.json
Share each target spreadsheet with the service account's client_email as
Viewer; run with no argument to print that email.
Wallet-walk hardening (convicted 2026-07-20): connectors.json carries
non-object top-level entries (a schema tag string), so every wallet descent
type-checks with isinstance(entry, dict) before touching keys.
[[[DIVIDER]]]
Auth (oauth_token_file — the gmail.py pattern; the human's OWN Google account):
App identity: ~/.config/pipulate/credentials.json
(override: PIPULATE_SHEETS_CREDENTIALS; the same Desktop-app
OAuth client gmail.py uses — Cloud project
work-integrations-500916)
User session: ~/.config/pipulate/sheets_token.json
(override: PIPULATE_SHEETS_TOKEN — a SEPARATE token from
gmail_token.json, because token files are scope-scoped and
one connector must never poison another's token)
The first run must happen INTERACTIVELY in a real terminal so the one-time
browser OAuth handshake can mint the durable token (running with no argument
does this). After that, `!` invocations in the compile lane refresh the token
silently and never block. There is NO sharing gate in this lane: whatever
Sheets your Google account can open, this connector can read.
AUTH-KIND RESIDUE conviction (2026-07-20): v1 of this file inherited gsc's
service_account plumbing by pattern-proximity, sending the API-enablement
toggle to the wrong project for the wrong credential. Auth kind is a USER
story decision: Pipulate humans own Google accounts -> OAuth.
[[[REPLACE]]]
Car 2 — imports:
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
[[[DIVIDER]]]
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
[[[REPLACE]]]
Car 3 — constants:
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
WALLET_FILE = Path.home() / '.config' / 'pipulate' / 'connectors.json'
BIG_TAB_CELLS = 5000 # LIST-mode warning threshold: likely context overflow
[[[DIVIDER]]]
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
CREDS_PATH = os.environ.get('PIPULATE_SHEETS_CREDENTIALS') or str(
Path.home() / '.config' / 'pipulate' / 'credentials.json')
TOKEN_PATH = os.environ.get('PIPULATE_SHEETS_TOKEN') or str(
Path.home() / '.config' / 'pipulate' / 'sheets_token.json')
BIG_TAB_CELLS = 5000 # LIST-mode warning threshold: likely context overflow
[[[REPLACE]]]
Car 4 — the auth machinery itself (wallet walk out, gmail token flow in):
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
def _wallet():
if WALLET_FILE.exists():
try:
return json.loads(WALLET_FILE.read_text(encoding='utf-8'))
except (json.JSONDecodeError, OSError):
pass
return {}
def _wallet_key_path(wallet, connector):
entry = wallet.get(connector)
if isinstance(entry, dict): # schema-tag strings ride the wallet too
p = (entry.get('paths') or {}).get('service_account')
if p:
return Path(p).expanduser()
return None
def resolve_key_path():
"""PIPULATE_SHEETS_KEY env -> wallet sheets -> wallet gsc -> canonical default."""
env = os.environ.get('PIPULATE_SHEETS_KEY')
if env:
return Path(env).expanduser()
wallet = _wallet()
for connector in ('sheets', 'gsc'):
p = _wallet_key_path(wallet, connector)
if p:
return p
return Path.home() / '.config' / 'pipulate' / 'service-account-key.json'
def get_service():
key_path = resolve_key_path()
if not key_path.exists():
die(
f"Sheets service-account key not found at: {key_path}\n"
"Set PIPULATE_SHEETS_KEY, or add sheets.paths.service_account to\n"
"~/.config/pipulate/connectors.json. The same JSON key the gsc\n"
"connector uses works here; only the scope differs. Then share each\n"
"target spreadsheet with the key's client_email as Viewer."
)
creds = service_account.Credentials.from_service_account_file(
str(key_path), scopes=SCOPES)
return build('sheets', 'v4', credentials=creds)
[[[DIVIDER]]]
def _save_token(creds):
"""Persist the user session token to the durable, gitignored path."""
token_path = Path(TOKEN_PATH)
token_path.parent.mkdir(parents=True, exist_ok=True)
with open(token_path, 'w', encoding='utf-8') as f:
f.write(creds.to_json())
def get_service():
"""Authenticated Sheets service; mints/refreshes the OAuth token as needed."""
creds = None
if os.path.exists(TOKEN_PATH):
try:
creds = Credentials.from_authorized_user_file(TOKEN_PATH, SCOPES)
except (json.JSONDecodeError, ValueError):
# Empty or poisoned token (the truncated-write trap). Re-auth.
creds = None
if creds and creds.valid:
return build('sheets', 'v4', credentials=creds)
# Headless refresh is safe even without a TTY.
if creds and creds.expired and creds.refresh_token:
try:
creds.refresh(Request())
_save_token(creds)
return build('sheets', 'v4', credentials=creds)
except Exception:
creds = None
# From here we need the interactive browser loopback flow.
if not sys.stdout.isatty():
die(
"Sheets auth needs a one-time interactive login.\n"
"Run this directly in your terminal first to mint the token:\n"
" python scripts/connectors/sheets.py\n"
"After that, `!` invocations inside the compile lane run silently."
)
if not os.path.exists(CREDS_PATH):
die(
f"Missing credentials.json at: {CREDS_PATH}\n"
"Download the Desktop-app OAuth client JSON from Google Cloud\n"
"Console (project work-integrations-500916) — the same file\n"
"gmail.py uses."
)
print("Opening local browser window for Google OAuth negotiation...",
file=sys.stderr)
flow = InstalledAppFlow.from_client_secrets_file(CREDS_PATH, SCOPES)
creds = flow.run_local_server(port=0)
_save_token(creds)
return build('sheets', 'v4', credentials=creds)
[[[REPLACE]]]
Car 5 — identity mode: wiring status, project_id printout, and the mint itself:
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
def identity():
"""No-argument mode: usage plus the service-account email to share Sheets with."""
key_path = resolve_key_path()
email = '(key file missing — any fetch attempt prints setup guidance)'
if key_path.exists():
try:
email = json.loads(key_path.read_text(encoding='utf-8')).get(
'client_email', '(client_email absent from key file)')
except (json.JSONDecodeError, OSError) as e:
email = f'(unreadable key file: {e})'
print("# sheets.py — read-only Google Sheets gateway (service account)\n")
print(f"key file : {key_path}")
print(f"identity : {email}")
print("\nShare each target spreadsheet with that email as Viewer, then:")
print('# Next: python scripts/connectors/sheets.py '
'"https://docs.google.com/spreadsheets/d/<ID>/edit" (LIST tabs + sizes)')
[[[DIVIDER]]]
def identity():
"""No-argument mode: OAuth wiring status; mints the token when interactive."""
creds_ok = os.path.exists(CREDS_PATH)
token_ok = os.path.exists(TOKEN_PATH)
project = '(credentials.json missing)'
if creds_ok:
try:
blob = json.loads(Path(CREDS_PATH).read_text(encoding='utf-8'))
project = (blob.get('installed') or blob.get('web') or {}).get(
'project_id', '(project_id absent)')
except (json.JSONDecodeError, OSError) as e:
project = f'(unreadable credentials.json: {e})'
print("# sheets.py — read-only Google Sheets gateway (OAuth: your own account)\n")
print(f"credentials : {CREDS_PATH} [{'present' if creds_ok else 'MISSING'}]")
print(f"project_id : {project}")
print(f"token : {TOKEN_PATH} [{'minted' if token_ok else 'not yet minted'}]")
if not token_ok and sys.stdout.isatty():
print("\nMinting the token now (one-time browser handshake)...")
get_service()
print("Token minted. Headless `!` runs will work from here on.")
elif not token_ok:
print("\nRun this same command once in a real terminal to mint the token.")
print("\n# Next: python scripts/connectors/sheets.py "
'"https://docs.google.com/spreadsheets/d/<ID>/edit" (LIST tabs + sizes)')
[[[REPLACE]]]
Car 6 — error guidance loses the sharing gate:
Target: scripts/connectors/sheets.py
[[[SEARCH]]]
except HttpError as e:
die(
f"Sheets API error: {e}\n"
"If this is 403/404: confirm the Google Sheets API is enabled for the\n"
"key's Cloud project AND the spreadsheet is shared with the service\n"
"account's client_email (run with no argument to print it)."
)
[[[DIVIDER]]]
except HttpError as e:
die(
f"Sheets API error: {e}\n"
"If this is 403 SERVICE_DISABLED: enable the Google Sheets API in the\n"
"OAuth client's Cloud project (run with no argument to print its\n"
"project_id). If 403/404 otherwise: confirm YOUR Google account can\n"
"open this spreadsheet in a browser — OAuth reads as you."
)
[[[REPLACE]]]
Car 7 — 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 (service_account_file; reuses gsc's key with the Sheets readonly scope; wallet descent type-checks non-object entries)
[[[DIVIDER]]]
- 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)
[[[REPLACE]]]
Car 8 — SKILL.md rewrite (auth story changes throughout — whole-file):
Target: Notebooks/.agents/skills/sheets_readonly/SKILL.md
[[[WRITE_FILE]]]
---
name: sheets_readonly
description: Read-only Google Sheets access for bounded context pulls. Trigger when a Google Sheets URL or spreadsheet ID needs to become compiled context. Executable truth lives at scripts/connectors/sheets.py; this skill is a signpost, never a second implementation.
---
# sheets_readonly — signpost to scripts/connectors/sheets.py
The executable is the specification. Run it; do not reimplement it.
## The three moves
1. Identity and mint (one-time, interactive):
`python scripts/connectors/sheets.py`
Prints the OAuth wiring status (credentials.json path, its project_id,
token path) and, in a real terminal, mints ~/.config/pipulate/sheets_token.json
via the browser handshake. After that, every run is headless.
2. LIST with size gauge:
`python scripts/connectors/sheets.py "<URL-or-ID>"`
Every tab's rows x cols x ~cells prints BEFORE any fetch; overflow-shaped
tabs are flagged. A #gid= fragment in a pasted URL selects that tab and
performs a bounded fetch of it directly.
3. Bounded FETCH:
`--sheet "<Tab>"` (row-bounded server-side) or `--range "'Tab'!A1:F50"`,
capped by -n/--max (default 25), `--format tsv|json|markdown` (tsv default).
## Rules inherited from the connector contract
- THE PROBE ECONOMY RULE: read the LIST size gauge before any fetch.
- Client URLs and spreadsheet IDs live in adhoc.txt, never in tracked source.
- Auth is OAuth as YOUR account (gmail.py pattern): no sharing gate, no
service accounts. A separate sheets_token.json exists because token files
are scope-scoped — never reuse gmail's token.
- 403 SERVICE_DISABLED means the Sheets API toggle in the OAuth client's
Cloud project (identity mode prints its project_id); any other 403/404
means your own account cannot open that sheet.
- Errors and auth guidance ride stderr; stdout stays parseable.
See scripts/connectors/README.md for the full connector contract.
[[[END_WRITE_FILE]]]
Car 9 — the executor learns that failure is a receipt (re-offered; now load-bearing for headless OAuth failures):
Target: prompt_foo.py
[[[SEARCH]]]
if proc.returncode != 0 and not cmd_stdout.strip():
raise subprocess.CalledProcessError(proc.returncode, command_str, output=cmd_stdout, stderr=cmd_stderr)
content = cmd_stdout.strip() or "(Executed successfully, no output)"
[[[DIVIDER]]]
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
# 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
# Processing Log preserved them. Empty stdout with nonempty
# stderr now falls through so the stderr merge below lands it.
if proc.returncode != 0 and not cmd_stdout.strip():
content = "(no stdout — stderr is the receipt)"
else:
content = cmd_stdout.strip() or "(Executed successfully, no output)"
[[[REPLACE]]]
Car 10 — todo alignment: two convictions banked, wallet-descriptor lane opened, PENDING line staged:
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 (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.
[[[REPLACE]]]
Actuation: patch, app, d, m per car (cars 1–6 all target sheets.py and can ride one patch together if you prefer fewer stops); blast as the caboose. No flake.nix change this turn — no exit/ndq needed.
4. PROMPT
Verify against THIS compile's receipts only, in this order:
1. CANARY: the bash canary must appear in the Manifest's LIVE COMMAND
RECEIPTS with "# NON-ZERO EXIT 3" and a stderr fence carrying
"canary-stderr". If present, flip FAILED-PROBE RECEIPT from PENDING to
banked (emit that patch) and instruct deleting the canary line. If
absent, diagnose the executor patch first.
2. IDENTITY: the jq receipt must read work-integrations-500916 and the
grep receipt must read 2 (OAuth flow imported and used). The token ls
receipt tells the mint story: present means the interactive handshake
happened; absent means the live LIST below will have failed with the
mint-first guidance on stderr — which, post-canary, is itself a
first-class receipt proving the headless guard works.
3. LIVE LIST: if it succeeds, read the size gauge, confirm the defensive
posture (tab dimensions visible before any fetch, big tabs flagged),
and enter dismount posture — VERIFY the connector claim, BANK
(including the OAuth rewrite and the two new earmarks), name DANGLING
(measure.py sketch, evidence.py unstarted, wallet descriptor lane
open), and SEED the measure.py ride: adhoc.txt lines that fetch real
cells from this sheet and the first metric-contract file as the
opening artifact of the adversarial TQM lane. If it fails, name which
gate the stderr convicts and stop there.
5. EXTERNAL DELIVERABLES
Manual, outside this repo, in order:
- Enable the Google Sheets API in the OAuth client’s project — visit
https://console.developers.google.com/apis/api/sheets.googleapis.com/overview?project=work-integrations-500916(the?project=parameter forces the console onto the right project; the earlier enablement in 279635155797 belonged to the service account and is now harmless residue). - Mint the token interactively: in a real terminal, run
.venv/bin/python scripts/connectors/sheets.py— identity mode prints the wiring and walks the one-time browser handshake, landing~/.config/pipulate/sheets_token.json. - Hand-run the live LIST once for its BEFORE reading — as you, no sharing step, because the sheet is already yours.
MikeLev.in:
Probe:
(nix) pipulate $ blast
📝 Committing: chore(router): set AI-edit blast boundary (foo_files.py)
[main b3ce23e3] chore(router): set AI-edit blast boundary (foo_files.py)
1 file changed, 1 insertion(+)
🚀 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), 389 bytes | 389.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
b2daaff1..b3ce23e3 main -> main
$ git status
On branch main
Your branch is up to date with 'origin/main'.
nothing to commit, working tree clean
(nix) pipulate $ jq -r '.installed.project_id // .web.project_id' ~/.config/pipulate/credentials.json
ls -l ~/.config/pipulate/sheets_token.json
grep -c "InstalledAppFlow" scripts/connectors/sheets.py
.venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
bash -c "echo canary-stderr >&2; exit 3"
work-integrations-500916
ls: cannot access '/home/mike/.config/pipulate/sheets_token.json': No such file or directory
0
sheets.py AST: OK
canary-stderr
(nix) pipulate $
Context:
# adhoc.txt _ _ _ ____ _ _ ___ ____ _
# / \ __| | | | | | ___ ___ / ___| | | |/ _ \| _ \| | Everything is easy until you have to authenticate.
# / _ \ / _` | | |_| |/ _ \ / __| | | | |_| | | | | |_) | | And we're making sure it's even going to be easy then.
# / ___ \ (_| | | _ | (_) | (__ | |___| _ | |_| | __/|_| But mostly we're going to try doing this with OAuth.
# /_/ \_\__,_| |_| |_|\___/ \___| \____|_| |_|\___/|_| (_) Service accounts are dangerous. Don't use 'em.
# 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. ---
! jq -r '.installed.project_id // .web.project_id' ~/.config/pipulate/credentials.json
! ls -l ~/.config/pipulate/sheets_token.json
! grep -c "InstalledAppFlow" scripts/connectors/sheets.py
! .venv/bin/python -c "import ast; ast.parse(open('scripts/connectors/sheets.py').read()); print('sheets.py AST: OK')"
! bash -c "echo canary-stderr >&2; exit 3"
! .venv/bin/python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho/edit"
scripts/connectors/sheets.py
scripts/connectors/gmail.py
scripts/connectors/README.md
prompt_foo.py
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'.
(nix) pipulate $ d
diff --git a/scripts/connectors/sheets.py b/scripts/connectors/sheets.py
index db03e412..d9979c5d 100644
--- a/scripts/connectors/sheets.py
+++ b/scripts/connectors/sheets.py
@@ -27,18 +27,26 @@ 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.
-Auth (service_account_file — headless by construction, no browser dance ever):
- PIPULATE_SHEETS_KEY env var
- -> ~/.config/pipulate/connectors.json sheets.paths.service_account
- -> ~/.config/pipulate/connectors.json gsc.paths.service_account
- (the SAME key JSON serves both Google APIs; only the scope differs)
- -> ~/.config/pipulate/service-account-key.json
-Share each target spreadsheet with the service account's client_email as
-Viewer; run with no argument to print that email.
-
-Wallet-walk hardening (convicted 2026-07-20): connectors.json carries
-non-object top-level entries (a schema tag string), so every wallet descent
-type-checks with isinstance(entry, dict) before touching keys.
+Auth (oauth_token_file — the gmail.py pattern; the human's OWN Google account):
+ App identity: ~/.config/pipulate/credentials.json
+ (override: PIPULATE_SHEETS_CREDENTIALS; the same Desktop-app
+ OAuth client gmail.py uses — Cloud project
+ work-integrations-500916)
+ User session: ~/.config/pipulate/sheets_token.json
+ (override: PIPULATE_SHEETS_TOKEN — a SEPARATE token from
+ gmail_token.json, because token files are scope-scoped and
+ one connector must never poison another's token)
+
+The first run must happen INTERACTIVELY in a real terminal so the one-time
+browser OAuth handshake can mint the durable token (running with no argument
+does this). After that, `!` invocations in the compile lane refresh the token
+silently and never block. There is NO sharing gate in this lane: whatever
+Sheets your Google account can open, this connector can read.
+
+AUTH-KIND RESIDUE conviction (2026-07-20): v1 of this file inherited gsc's
+service_account plumbing by pattern-proximity, sending the API-enablement
+toggle to the wrong project for the wrong credential. Auth kind is a USER
+story decision: Pipulate humans own Google accounts -> OAuth.
COMPILE-LANE CAUTION: LIST/FETCH output can contain spreadsheet titles, tab
names, and cell data. This lane assumes non-training AI accounts; still, keep
(nix) pipulate $ m
📝 Committing: chore: Update auth configuration for OAuth token file
[main d7a10938] chore: Update auth configuration for OAuth token file
1 file changed, 20 insertions(+), 12 deletions(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ 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 d9979c5d..37692882 100644
--- a/scripts/connectors/sheets.py
+++ b/scripts/connectors/sheets.py
@@ -61,7 +61,9 @@ import json
import argparse
from pathlib import Path
-from google.oauth2 import service_account
+from google.auth.transport.requests import Request
+from google.oauth2.credentials import Credentials
+from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
(nix) pipulate $ m
📝 Committing: chore: Update Google API client dependencies for authentication
[main 902493a0] chore: Update Google API client dependencies for authentication
1 file changed, 3 insertions(+), 1 deletion(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ 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 37692882..f9dac887 100644
--- a/scripts/connectors/sheets.py
+++ b/scripts/connectors/sheets.py
@@ -68,7 +68,10 @@ from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
-WALLET_FILE = Path.home() / '.config' / 'pipulate' / 'connectors.json'
+CREDS_PATH = os.environ.get('PIPULATE_SHEETS_CREDENTIALS') or str(
+ Path.home() / '.config' / 'pipulate' / 'credentials.json')
+TOKEN_PATH = os.environ.get('PIPULATE_SHEETS_TOKEN') or str(
+ Path.home() / '.config' / 'pipulate' / 'sheets_token.json')
BIG_TAB_CELLS = 5000 # LIST-mode warning threshold: likely context overflow
_URL_ID_RE = re.compile(r'/spreadsheets/d/([a-zA-Z0-9_-]+)')
(nix) pipulate $ m
📝 Committing: chore: Update credentials paths based on environment variables
[main df3e5b38] chore: Update credentials paths based on environment variables
1 file changed, 4 insertions(+), 1 deletion(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ 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 f9dac887..faaa9245 100644
--- a/scripts/connectors/sheets.py
+++ b/scripts/connectors/sheets.py
@@ -103,49 +103,58 @@ def parse_spreadsheet_ref(ref):
)
-def _wallet():
- if WALLET_FILE.exists():
- try:
- return json.loads(WALLET_FILE.read_text(encoding='utf-8'))
- except (json.JSONDecodeError, OSError):
- pass
- return {}
-
-
-def _wallet_key_path(wallet, connector):
- entry = wallet.get(connector)
- if isinstance(entry, dict): # schema-tag strings ride the wallet too
- p = (entry.get('paths') or {}).get('service_account')
- if p:
- return Path(p).expanduser()
- return None
+def _save_token(creds):
+ """Persist the user session token to the durable, gitignored path."""
+ token_path = Path(TOKEN_PATH)
+ token_path.parent.mkdir(parents=True, exist_ok=True)
+ with open(token_path, 'w', encoding='utf-8') as f:
+ f.write(creds.to_json())
-def resolve_key_path():
- """PIPULATE_SHEETS_KEY env -> wallet sheets -> wallet gsc -> canonical default."""
- env = os.environ.get('PIPULATE_SHEETS_KEY')
- if env:
- return Path(env).expanduser()
- wallet = _wallet()
- for connector in ('sheets', 'gsc'):
- p = _wallet_key_path(wallet, connector)
- if p:
- return p
- return Path.home() / '.config' / 'pipulate' / 'service-account-key.json'
+def get_service():
+ """Authenticated Sheets service; mints/refreshes the OAuth token as needed."""
+ creds = None
+ if os.path.exists(TOKEN_PATH):
+ try:
+ creds = Credentials.from_authorized_user_file(TOKEN_PATH, SCOPES)
+ except (json.JSONDecodeError, ValueError):
+ # Empty or poisoned token (the truncated-write trap). Re-auth.
+ creds = None
+ if creds and creds.valid:
+ return build('sheets', 'v4', credentials=creds)
-def get_service():
- key_path = resolve_key_path()
- if not key_path.exists():
+ # Headless refresh is safe even without a TTY.
+ if creds and creds.expired and creds.refresh_token:
+ try:
+ creds.refresh(Request())
+ _save_token(creds)
+ return build('sheets', 'v4', credentials=creds)
+ except Exception:
+ creds = None
+
+ # From here we need the interactive browser loopback flow.
+ if not sys.stdout.isatty():
die(
- f"Sheets service-account key not found at: {key_path}\n"
- "Set PIPULATE_SHEETS_KEY, or add sheets.paths.service_account to\n"
- "~/.config/pipulate/connectors.json. The same JSON key the gsc\n"
- "connector uses works here; only the scope differs. Then share each\n"
- "target spreadsheet with the key's client_email as Viewer."
+ "Sheets auth needs a one-time interactive login.\n"
+ "Run this directly in your terminal first to mint the token:\n"
+ " python scripts/connectors/sheets.py\n"
+ "After that, `!` invocations inside the compile lane run silently."
)
- creds = service_account.Credentials.from_service_account_file(
- str(key_path), scopes=SCOPES)
+
+ if not os.path.exists(CREDS_PATH):
+ die(
+ f"Missing credentials.json at: {CREDS_PATH}\n"
+ "Download the Desktop-app OAuth client JSON from Google Cloud\n"
+ "Console (project work-integrations-500916) — the same file\n"
+ "gmail.py uses."
+ )
+
+ print("Opening local browser window for Google OAuth negotiation...",
+ file=sys.stderr)
+ flow = InstalledAppFlow.from_client_secrets_file(CREDS_PATH, SCOPES)
+ creds = flow.run_local_server(port=0)
+ _save_token(creds)
return build('sheets', 'v4', credentials=creds)
(nix) pipulate $ m
📝 Committing: chore: Persist token to file and handle errors during loading/saving
[main c3d1829b] chore: Persist token to file and handle errors during loading/saving
1 file changed, 46 insertions(+), 37 deletions(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ 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 faaa9245..e962a628 100644
--- a/scripts/connectors/sheets.py
+++ b/scripts/connectors/sheets.py
@@ -162,20 +162,28 @@ def get_service():
# Modes
# ----------------------------------------------------------------------------
def identity():
- """No-argument mode: usage plus the service-account email to share Sheets with."""
- key_path = resolve_key_path()
- email = '(key file missing — any fetch attempt prints setup guidance)'
- if key_path.exists():
+ """No-argument mode: OAuth wiring status; mints the token when interactive."""
+ creds_ok = os.path.exists(CREDS_PATH)
+ token_ok = os.path.exists(TOKEN_PATH)
+ project = '(credentials.json missing)'
+ if creds_ok:
try:
- email = json.loads(key_path.read_text(encoding='utf-8')).get(
- 'client_email', '(client_email absent from key file)')
+ blob = json.loads(Path(CREDS_PATH).read_text(encoding='utf-8'))
+ project = (blob.get('installed') or blob.get('web') or {}).get(
+ 'project_id', '(project_id absent)')
except (json.JSONDecodeError, OSError) as e:
- email = f'(unreadable key file: {e})'
- print("# sheets.py — read-only Google Sheets gateway (service account)\n")
- print(f"key file : {key_path}")
- print(f"identity : {email}")
- print("\nShare each target spreadsheet with that email as Viewer, then:")
- print('# Next: python scripts/connectors/sheets.py '
+ project = f'(unreadable credentials.json: {e})'
+ print("# sheets.py — read-only Google Sheets gateway (OAuth: your own account)\n")
+ print(f"credentials : {CREDS_PATH} [{'present' if creds_ok else 'MISSING'}]")
+ print(f"project_id : {project}")
+ print(f"token : {TOKEN_PATH} [{'minted' if token_ok else 'not yet minted'}]")
+ if not token_ok and sys.stdout.isatty():
+ print("\nMinting the token now (one-time browser handshake)...")
+ get_service()
+ print("Token minted. Headless `!` runs will work from here on.")
+ elif not token_ok:
+ print("\nRun this same command once in a real terminal to mint the token.")
+ print("\n# Next: python scripts/connectors/sheets.py "
'"https://docs.google.com/spreadsheets/d/<ID>/edit" (LIST tabs + sizes)')
(nix) pipulate $ m
📝 Committing: chore: Update sheets.py documentation for OAuth mode
[main 1e9cb97a] chore: Update sheets.py documentation for OAuth mode
1 file changed, 20 insertions(+), 12 deletions(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ 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 e962a628..df03c8aa 100644
--- a/scripts/connectors/sheets.py
+++ b/scripts/connectors/sheets.py
@@ -318,9 +318,10 @@ def main():
except HttpError as e:
die(
f"Sheets API error: {e}\n"
- "If this is 403/404: confirm the Google Sheets API is enabled for the\n"
- "key's Cloud project AND the spreadsheet is shared with the service\n"
- "account's client_email (run with no argument to print it)."
+ "If this is 403 SERVICE_DISABLED: enable the Google Sheets API in the\n"
+ "OAuth client's Cloud project (run with no argument to print its\n"
+ "project_id). If 403/404 otherwise: confirm YOUR Google account can\n"
+ "open this spreadsheet in a browser — OAuth reads as you."
)
(nix) pipulate $ m
📝 Committing: chore: Improve Sheets API error message clarity
[main 26cefa24] chore: Improve Sheets API error message clarity
1 file changed, 4 insertions(+), 3 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 fce026cb..dd6980e8 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 (service_account_file; reuses gsc's key with the Sheets readonly scope; wallet descent type-checks non-object entries)
+- 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)
## Minting a new connector
(nix) pipulate $ m
📝 Committing: chore: Update sheets.py with OAuth token handling
[main 4a8dd09e] chore: Update sheets.py with OAuth token handling
1 file changed, 1 insertion(+), 1 deletion(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ WHOLE-FILE WRITE: OVERWROTE 'Notebooks/.agents/skills/sheets_readonly/SKILL.md'.
(nix) pipulate $ d
diff --git a/Notebooks/.agents/skills/sheets_readonly/SKILL.md b/Notebooks/.agents/skills/sheets_readonly/SKILL.md
index dae3c7ec..bd97bdf2 100644
--- a/Notebooks/.agents/skills/sheets_readonly/SKILL.md
+++ b/Notebooks/.agents/skills/sheets_readonly/SKILL.md
@@ -9,10 +9,11 @@ The executable is the specification. Run it; do not reimplement it.
## The three moves
-1. Identity (get the share target):
+1. Identity and mint (one-time, interactive):
`python scripts/connectors/sheets.py`
- Prints the service-account client_email. Share each target spreadsheet
- with that email as Viewer.
+ Prints the OAuth wiring status (credentials.json path, its project_id,
+ token path) and, in a real terminal, mints ~/.config/pipulate/sheets_token.json
+ via the browser handshake. After that, every run is headless.
2. LIST with size gauge:
`python scripts/connectors/sheets.py "<URL-or-ID>"`
@@ -28,9 +29,12 @@ The executable is the specification. Run it; do not reimplement it.
- THE PROBE ECONOMY RULE: read the LIST size gauge before any fetch.
- Client URLs and spreadsheet IDs live in adhoc.txt, never in tracked source.
-- TWO-GATE 403 DIAGNOSIS, in order: SERVICE_DISABLED (enable the Sheets API
- in the key's Cloud project, once per project) before PERMISSION_DENIED
- (share the document with the client_email, once per document).
+- Auth is OAuth as YOUR account (gmail.py pattern): no sharing gate, no
+ service accounts. A separate sheets_token.json exists because token files
+ are scope-scoped — never reuse gmail's token.
+- 403 SERVICE_DISABLED means the Sheets API toggle in the OAuth client's
+ Cloud project (identity mode prints its project_id); any other 403/404
+ means your own account cannot open that sheet.
- Errors and auth guidance ride stderr; stdout stays parseable.
See scripts/connectors/README.md for the full connector contract.
(nix) pipulate $ m
📝 Committing: chore: Update SKILL.md documentation regarding authentication flow
[main 91a6e201] chore: Update SKILL.md documentation regarding authentication flow
1 file changed, 10 insertions(+), 6 deletions(-)
(nix) pipulate $ patch
(nix) pipulate $ app
✅ DETERMINISTIC PATCH APPLIED: Successfully mutated 'prompt_foo.py'.
(nix) pipulate $ d
diff --git a/prompt_foo.py b/prompt_foo.py
index 12064e0c..721bb87c 100644
--- a/prompt_foo.py
+++ b/prompt_foo.py
@@ -1902,9 +1902,18 @@ def main():
proc.communicate()
logger.print(f"\n [Error] Timed out after 180s; process group killed.")
continue
- if proc.returncode != 0 and not cmd_stdout.strip():
+ 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)
- content = cmd_stdout.strip() or "(Executed successfully, no output)"
+ # FAILED-PROBE RECEIPT (PENDING until canary witness): 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
+ # Processing Log preserved them. Empty stdout with nonempty
+ # stderr now falls through so the stderr merge below lands it.
+ if proc.returncode != 0 and not cmd_stdout.strip():
+ content = "(no stdout — stderr is the receipt)"
+ else:
+ content = cmd_stdout.strip() or "(Executed successfully, no output)"
if proc.returncode != 0:
# Exit-code-as-data tools (grep -c, diff) signal via return
# code while stdout carries the receipt. Preserve the receipt,
(nix) pipulate $ m
📝 Committing: chore: Refine subprocess error handling in prompt_foo.py
[main f76b48f4] chore: Refine subprocess error handling in prompt_foo.py
1 file changed, 11 insertions(+), 2 deletions(-)
(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 68eb4896..5338f43b 100644
--- a/foo_files.py
+++ b/foo_files.py
@@ -1230,6 +1230,9 @@ scripts/xp.py # [1,981 tokens | 8,377 bytes]
# - 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.
+# - 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.
# ============================================================================
# VIII. THE PAINTBOX (Unused Colors)
(nix) pipulate $ m
📝 Committing: fix: clarify auth kind and wallet descriptor notes
[main ac915ae9] fix: clarify auth kind and wallet descriptor notes
1 file changed, 3 insertions(+)
(nix) pipulate $ git push
Enumerating objects: 60, done.
Counting objects: 100% (60/60), done.
Delta compression using up to 48 threads
Compressing objects: 100% (46/46), done.
Writing objects: 100% (48/48), 8.90 KiB | 2.22 MiB/s, done.
Total 48 (delta 36), reused 0 (delta 0), pack-reused 0 (from 0)
remote: Resolving deltas: 100% (36/36), completed with 10 local objects.
To github.com:pipulate/pipulate.git
b3ce23e3..ac915ae9 main -> main
(nix) pipulate $
Sure, I could have ran a lot of these together, but why when it’s already given in this convenient copy-paste format? Why do half the work when you can do double it for free (kidding, not kidding)?
Okay, now we listen to Fable 5’s response all read out loud again and look for any out-of-bound instructions that could very well abound on a project like this. Yup, I thought I heard this the first time around:
(nix) pipulate $ .venv/bin/python scripts/connectors/sheets.py
# sheets.py — read-only Google Sheets gateway (OAuth: your own account)
credentials : /home/mike/.config/pipulate/credentials.json [present]
project_id : work-integrations-500916
token : /home/mike/.config/pipulate/sheets_token.json [not yet minted]
Minting the token now (one-time browser handshake)...
Opening local browser window for Google OAuth negotiation...
Please visit this URL to authorize this application: https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=312063524029-2bpp3pohraoos5g74d5ma9u5gd7lfjaq.apps.googleusercontent.com&redirect_uri=http%3A%2F%2Flocalhost%3A56127%2F&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets.readonly&state=vlXJkpXZVIiF1XD2ZxPRVQZGtP1qFX&code_challenge=tF6tz_52Rz3VdKnR7oLVvLjPxwlfzeKLsciuVRB5bfo&code_challenge_method=S256&access_type=offline
Opening in existing browser session.
Token minted. Headless `!` runs will work from here on.
# Next: python scripts/connectors/sheets.py "https://docs.google.com/spreadsheets/d/<ID>/edit" (LIST tabs + sizes)
(nix) pipulate $
Is that awesome or what? I don’t think people understand falsification yet. Who would we actually want failure on a probe? The main problem with proving Bigfoot doesn’t exist is falsifiability. We make Richard Feynman-style guesses around that can be turned into a hypothesis that can be upgraded into a theory and ultimately into a proof, in which case it’s upgraded to science. A good hypothesis statement can be proven false with one experiment so for example: “Pigs don’t fly.” Given evolution’s history around convergent evolution of flying in what? Just about everything? All it takes is producing one flying pig and it’s been falsified. That’s the best you can do in science.
You cannot prove the existence of an absence and trying to do so is called faith; uhhh… more faith than science because believing that chipping your way towards truth using the iterative falsification process is a kind of faith too; only not not quite so faithy and that makes it a marginally better belief-system so long as it stays open-minded and not bogged down by its own dogmas the way Khun’s The Structure of Scientific Revolutions shows us it will. So choose science but look at everything through the eyes of a magician who knows all the tricks that’ll fool you.
Why do so many people not get this? Make it clearer, will you Fable 5? And how it applies here in addition to the prompt below. Please and thank you.
Prompt:
Verify against THIS compile’s receipts only, in this order:
-
CANARY: the bash canary must appear in the Manifest’s LIVE COMMAND RECEIPTS with “# NON-ZERO EXIT 3” and a stderr fence carrying “canary-stderr”. If present, flip FAILED-PROBE RECEIPT from PENDING to banked (emit that patch) and instruct deleting the canary line. If absent, diagnose the executor patch first.
-
IDENTITY: the jq receipt must read work-integrations-500916 and the grep receipt must read 2 (OAuth flow imported and used). The token ls receipt tells the mint story: present means the interactive handshake happened; absent means the live LIST below will have failed with the mint-first guidance on stderr — which, post-canary, is itself a first-class receipt proving the headless guard works.
-
LIVE LIST: if it succeeds, read the size gauge, confirm the defensive posture (tab dimensions visible before any fetch, big tabs flagged), and enter dismount posture — VERIFY the connector claim, BANK (including the OAuth rewrite and the two new earmarks), name DANGLING (measure.py sketch, evidence.py unstarted, wallet descriptor lane open), and SEED the measure.py ride: adhoc.txt lines that fetch real cells from this sheet and the first metric-contract file as the opening artifact of the adversarial TQM lane. If it fails, name which gate the stderr convicts and stop there.
🎤
Fable 5 High: Context verified.
Receipt verification (Manifest list as sole authority, in your order)
1. CANARY — witnessed. The Manifest’s LIVE COMMAND RECEIPTS carries ! bash -c "echo canary-stderr >&2; exit 3" exactly as the contract demanded: # NON-ZERO EXIT 3 (stdout preserved as receipt), the (no stdout — stderr is the receipt) placeholder, and a --- stderr --- fence carrying canary-stderr. The FAILED-PROBE RECEIPT amendment flips PENDING→banked (patch below), and the canary line comes out of adhoc.txt — it did its job, which was to fail on cue.
2. IDENTITY — green across the board. The jq receipt reads work-integrations-500916, digit-for-digit the project you named. The grep reads 2 (OAuth flow imported and used, the predicted 0→2 delta). The token ls tells the mint story’s happy ending: sheets_token.json, 742 bytes, stamped Jul 20 13:28 — the interactive handshake happened, exactly as your terminal transcript shows.
3. LIVE LIST — green, and the defensive posture held. The receipt shows variant_url_performance_analysis, 5 tabs, every one gauged at 1000 × 26 = 26,000 cells, every one flagged WARNING: big — fetch with an explicit --range, not a bare --sheet, and the # Next: breadcrumb teaching the drill-down. Total cost to context: 292 tokens to learn the shape of a 130,000-cell workbook before touching a single value. That is the whole design claim, confirmed by receipt: sizes visible before any fetch, big tabs flagged, no overflow.
One adversarial note to squeeze before celebrating: 1000×26 is Google’s default grid allocation. Five identical readings almost certainly means the gauge is measuring grid capacity, not data extent — the tabs may hold 40 rows of real data or 900. The gauge did its job (bounded the risk), but the claim “these tabs are big” is unproven; only a bounded fetch discriminates. That’s the first metric contract’s opening question, handed to us free.
On falsification, since you asked
Why don’t people get it? Because confirmation feels like progress and falsification feels like loss, and human cognition runs a positive-test default — in Wason’s card-selection experiments, most people check the cards that could confirm the rule and ignore the one card that could break it. Institutions amplify the bias: you get paid for deliverables, not for surviving attacks. So “we checked and it works” passes for diligence when it’s actually just the Bigfoot problem inverted — a pile of sightings, zero attempts to produce the flying pig.
The reframe that makes it click: a claim’s value is the strength of the attacks it has survived, not the number of agreements it has collected. “Pigs don’t fly” is a good claim because one pig can kill it. “This connector works” was a bad claim until this compile, because nothing had tried to kill it. Now it’s survived: wrong-credential 403 (killed v1’s auth model — good!), the jq type-error (wrote the wallet guard for us), the headless-mint guard, and a live workbook.
The canary is the purest example on the board, and worth naming because it’s subtle: we didn’t just test the executor — we manufactured a known failure and checked the instrument could see it. “The executor reports all receipts” was falsified two compiles ago by the vanishing SERVICE_DISABLED reading; the fix wasn’t trusted until a deliberate failure came back visible. That’s the magician’s posture: assume you’re being fooled, then rig the trick yourself and see if you catch it. Every green receipt in this system is only meaningful because the pipeline has demonstrably shown red when red was true. A QA process that has never displayed a failure isn’t rigorous — it’s untested.
That’s what the adversarial TQM lane institutionalizes: the deliverable ships with the list of ways it tried to be wrong.
Dismount (\k posture — the ride’s stated scope is reached)
VERIFY: The opening goal — a Google Sheet URL becomes bounded, size-defended Prompt Fu context via one gmail-shaped connector — is met per THIS compile’s live LIST receipt, not memory.
BANK: OAuth rewrite (grep=2, AST OK, token minted, live LIST green); FAILED-PROBE RECEIPT PENDING→banked (patch below); AUTH-KIND RESIDUE and WALLET DESCRIPTOR LANE earmarks already landed last compile and are visible in this one’s foo_files.py. Delete the canary ! line from adhoc.txt.
DANGLING (one line each): measure.py — still a sketch, no file. evidence.py — unstarted. Wallet sheets descriptor entry — optional, absent, fine. Grid-vs-data gauge ambiguity — new, feeds the first contract. Tool-roster metadata-split earmark — unrelated, still open.
SEED: below in NEXT CONTEXT — the measure.py ride opens by fetching real cells bounded, then writing the first metric contract against what the cells actually say.
1. PROBES
The first two are the ride-opening BEFORE readings for measure.py: bounded fetches that reveal actual data extent (testing the grid-vs-data hypothesis — identical 1000-row gauges predict default grids, so real rows should be far fewer). The greps are the flip’s delta pair: PENDING: FAILED-PROBE predicts 1→0, (banked 2026-07-20, canary-witnessed predicts 0→1.
.venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --range "'Summary'!A1:H30"
.venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --range "'Top Variant URLs'!A1:H30"
grep -c "PENDING: FAILED-PROBE RECEIPT" foo_files.py
grep -c "canary-witnessed" foo_files.py
2. NEXT CONTEXT
The canary line is deliberately absent — its deletion is part of the banking.
! .venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --range "'Summary'!A1:H30"
! .venv/bin/python scripts/connectors/sheets.py 1scf0G3-kc90_3rNzfyqkW8eDiHn_IXu6PpRphpLohho --range "'Top Variant URLs'!A1:H30"
! 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 — flip the constitutional line PENDING→banked:
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 very next compile's LIVE COMMAND RECEIPTS exactly as specified, then was deleted from adhoc.txt. Method note worth keeping: the fix was proven by MANUFACTURING a known failure and confirming the instrument displayed it — a QA pipeline is only trusted once it has demonstrably shown red when red was true.
[[[REPLACE]]]
Car 2 — the executor comment stops claiming PENDING:
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. The grep pair must read 0 and 1
(PENDING gone, banked present) — confirm the flip landed. Then read the two
bounded --range receipts and test the grid-vs-data hypothesis: five identical
1000x26 gauges predicted default grid allocation, so actual populated rows
should be far fewer than 1000. Report the real data extent of each fetched tab.
Then open the ADVERSARIAL TQM LANE for real: using the actual columns and
values now visible in the Summary receipt, draft the first metric-contract
file (contracts/variant_url_performance.yaml or similar — propose the path)
with at least one primary assertion and two adversarial countermeasurements
chosen as the easiest surviving false explanations for THIS data. Then emit
the first minimal measure.py — bounded stdin/file TSV in, per-check PASS/FAIL
lines out, exit code as verdict, no connector knowledge, no framework — as a
whole-file write, with AST and a self-test probe echoed for the next compile.
Acquisition and judgment stay separate commands. Emit the full patch train.
5. EXTERNAL DELIVERABLES
One manual edit, outside git’s reach by design: open adhoc.txt (ahe) and delete the canary line ! bash -c "echo canary-stderr >&2; exit 3" — it’s witnessed, banked, and retired. Everything else this turn lives in the repo lane.
MikeLev.in: Wow, I really love this. Now I can create these structures with a Google Sheet on one side and the real Botify (or Google Search Console API directly) on the other side, and I can do checks whether what’s said is backed by real data, and make it so that I can update it frequently. This is the strategy. Do stupid everything tricks in the web-world because you have this CDP (and other) telemetry, can figure out what every API is doing by sniffing it’s GraphQL (for example) and create some sort of truth-mapping. Left/Right assertions with the power of a Python dict somewhere turning it into an assertion test against a list of URLs or something. That’s just generally fleshing it out. We’re about to do it, now with genuine direct Google Sheets access.
The end.
Book Analysis
Ai Editorial Take
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.
🐦 X.com Promo Tweet
Automating QA doesn't mean trusting AI blindly. It means building tools that falsify our own claims. See how we integrated Google Sheets into a headless, deterministic pipeline for real-time verification. https://mikelev.in/futureproof/automating-truth-adversarial-qa-sheets/ #DataEngineering #AI #QA
Title Brainstorm
- Title Option: Automating Truth: Engineering Adversarial QA Pipelines with Google Sheets
- Filename:
automating-truth-adversarial-qa-sheets.md - Rationale: Directly addresses the TQM methodology and the practical implementation of the connector.
- Filename:
- Title Option: Engineering the Adversarial Lane: A Google Sheets Case Study
- Filename:
engineering-adversarial-lane-sheets.md - Rationale: Focuses on the structural evolution of the pipeline and the shift toward falsifiable data.
- Filename:
- Title Option: From Spreadsheet to Proof: Building Falsifiable Data Pipelines
- Filename:
spreadsheet-to-proof-data-pipelines.md - Rationale: Highlights the transition from raw data collection to actionable, testable evidence.
- Filename:
Content Potential And Polish
- Core Strengths:
- Clear articulation of adversarial testing as a tool for improvement rather than conflict.
- Excellent documentation of the transition from service-account to OAuth patterns.
- Strong conceptual foundation linking manual TQM concepts to automated Unix-style pipelines.
- Suggestions For Polish:
- Refine the transition from the philosophical ‘adversarial’ definition to the technical implementation of the Sheets connector.
- Consider consolidating the repetitive mentions of the three-standards (AGENTS.md, Skills, OKF) to let the connector implementation shine more.
Next Step Prompts
- Draft the
measure.pytool implementation with a sample contract.yaml for validating a spreadsheet’s metrics. - Design the
evidence.pystructure for zipping claims, inputs, and results into a persistent delivery cartridge.