sheets

Google Sheets surface for snappy-os. Service-account JWT auth - no OAuth dance, no per-user consent screens. Used to give Ray (and other clients) a read-only window into snappy-os data without standing up a dashboard.

The actor (this lib) writes; the auditor is an independent reader. For the business-tracking sheet specifically, the auditor is readRows() from a fresh process that confirms the headers landed and Ray's permission resolved.

See state/lib/sheets.ts for the full API surface.

Steps

• getSheetsClient() - JWT-authenticated { sheets, drive } clients. Cached per process.
• createSpreadsheet(title, tabs, opts?) - create workbook, write header row per tab, bold header. Returns { spreadsheetId, spreadsheetUrl }. opts.parentFolderId overrides GOOGLE_DRIVE_PARENT_FOLDER_ID; must be set to avoid storageQuotaExceeded (see Pre-flight above).
• shareSpreadsheet(spreadsheetId, email, role?) - Drive permission grant. role defaults to reader. No notification email by default.
• writeRows(spreadsheetId, tabName, rows) - append rows under the headers. USER_ENTERED so Sheets parses dates/numbers natively. Phase B cron uses this.
• readRows(spreadsheetId, tabName, range?) - read raw 2D string array. Used as the independent-fetch auditor.

Eval

Actor: createSpreadsheet, shareSpreadsheet, writeRows in state/lib/sheets.ts. Auditor: readRows() from a fresh process - confirms header row matches the requested columns; for share, an HTTP HEAD against the spreadsheetUrl from a non-authenticated session confirms the URL resolves.

Score convention:

Pre-flight (run before any createSpreadsheet call)

1. Drive storage - the service account xano-automation@snappy-424813.iam.gserviceaccount.com has no Drive storage quota of its own. Creating a sheet in the service account's root always throws 403 storageQuotaExceeded. Fix: set GOOGLE_DRIVE_PARENT_FOLDER_ID in .env.cache to a Google Drive folder that Robert owns and has shared with the service account (Writer). Then pass opts.parentFolderId to createSpreadsheet - the lib plucks the env var automatically if opts.parentFolderId is not supplied.
2. Permissions - if createSpreadsheet returns "The caller does not have permission," the service account has not been granted access to the parent folder (or Drive API is not enabled in the Cloud project). Share the target folder with the SA email and verify the Drive API is enabled at console.cloud.google.com.

Gotchas

• GOOGLE_SERVICE_ACCOUNT_KEY in .env.cache arrives with literal \n - the lib unescapes to real newlines before handing to the JWT signer. If you bypass the lib and roll your own, OpenSSL throws DECODER routines :: unsupported with no useful detail.
• Service account is the implicit owner of any sheet it creates. Robert and Ray cannot become owners without a domain-wide delegation step the lib does not perform.
• Drive permissions.create defaults to sending a notification email - the lib explicitly disables this. Robert delivers the URL in the ray-update body, not via Google's mailer.
• Tab names containing spaces work, but ranges must quote them: 'Retainer Balance'!A1 - the lib's helpers handle this by passing the unquoted tab name to googleapis, which quotes internally.

Graduation

This skill is prose with a shape eval. Graduate by adding a script that wraps the common create-then-share-then-verify flow in one script - the business-tracking sheet is the first candidate.

Rubric

criteria:
  - name: spreadsheet_created
    kind: deterministic
    check: "The `createSpreadsheet` function successfully returns a `spreadsheetId` and `spreadsheetUrl` without throwing an error."
  - name: headers_match_columns
    kind: judge
    check: "An independent `readRows()` call confirms that the header rows in all created tabs exactly match the requested `columns` in the `createSpreadsheet_input`."
  - name: ray_has_access
    kind: deterministic
    check: "If `shareSpreadsheet_input` was provided for 'Ray', an HTTP HEAD request to the `spreadsheetUrl` from a non-authenticated session, mimicking Ray's access, resolves successfully."
  - name: no_storage_quota_exceeded
    kind: deterministic
    check: "The `createSpreadsheet` call did not result in a `403 storageQuotaExceeded` error, indicating `GOOGLE_DRIVE_PARENT_FOLDER_ID` was correctly configured and accessible."

sheets - loader

Per-turn rules for the sheets skill. Full reference: state/skills/sheets/SKILL.md. Do not skip these.

Critical Rules

• ALWAYS unescape GOOGLE_SERVICE_ACCOUNT_KEY literal \n to real newlines before signing - state/lib/sheets.ts does this for you, do not bypass
• NEVER set sendNotificationEmail: true on shareSpreadsheet without explicit instruction - Robert relays the URL in the ray-update body, not via Google's mailer
• The service account is the implicit OWNER of every sheet it creates. Robert and Ray cannot become owners without domain-wide delegation. Plan accordingly.
• Use the lib's exported functions - do NOT roll your own JWT or call googleapis directly elsewhere in the codebase

Commands

| ui dashboard | state/skills/sheets/resources/ui.openui | |invoke: import from state/lib/sheets.ts - createSpreadsheet, shareSpreadsheet, writeRows, readRows, getSheetsClient |verify: npx tsx state/lib/sheets.ts (import-check) and npx tsx state/lib/sheets.ts read <id> <tab> (independent fetch as auditor) |eval log: state/log/evals.ndjson (skill: "sheets")

OpenUI Resource

• Skill-owned OpenUI Lang resource: state/skills/sheets/resources/ui.openui. Read it before rendering or editing this skill's generated component surface.
• Treat this resource as a first-class artifact of the skill, not a generic chat response. Improve it when the skill's user-facing output needs to become richer.
• System resources compose OpenUI primitives and inherit SnappyChat tokens. Use ui_contract: branded in SKILL.md only for deliberate platform or client visuals.

Known Pitfalls

• GOOGLE_SERVICE_ACCOUNT_KEY newline format - bypassing the lib means OpenSSL throws DECODER routines :: unsupported with no further detail
• Tab names with spaces need range-quoting ('Retainer Balance'!A1) when constructing ranges by hand; the lib's writeRows/readRows handle this for you
• writeRows appends - to overwrite, you need a separate values.update call or to clear the range first; lib does not (yet) expose an overwrite helper

Self-Test

An agent reading this should correctly:

1. [ ] Use the lib's createSpreadsheet rather than calling googleapis directly
2. [ ] Default shareSpreadsheet to sendNotificationEmail: false
3. [ ] Use readRows from a fresh process as the audit step

Self-report

If this loader fell short, append a line:

echo "[$(date -u +%FT%TZ)] sheets: <what was missing>" >> state/log/loader-feedback.log

<!-- kernel-ok: historical attribution for the PID-loader pattern. -->

Self-correcting loader (PID feedback)

This is the rule that makes loaders learn. Ported from snappy-kernel, where it demonstrably worked: the snappy-dom-cartographer skill mapped Skool admin UI once, every later ship-agent inherited the full map for free.

Before you finish, do two things:

1. Fix gaps (P - proportional). MANDATORY. If this loader didn't cover your case - if you had to read another file, run an undocumented command, work around a wrong selector, or discover a quirk - you MUST attempt an Edit to this AGENTS.md before you log.

• Default action: edit this file. Surgical. Add the missing line, table

row, or code example where it belongs. No restructuring.

• Only LOGGED is allowed when: the fix needs >10 lines, spans multiple

files, or requires a structural rewrite. In that case the state/regen/drain.sh queue picks it up asynchronously.

• "I didn't have time" / "it's minor" / "the next agent will figure it out"

are NOT valid reasons. Minor gaps compound - that's the whole point of this loop.

• Concrete example of a valid P-fix: discover agent-browser screenshot

takes path positionally not as --output <path>. One line in a table. Edit it in. Do not just log it.

• The goal: the next agent never has to leave the loader.

2. Log the result.

echo "[$(date -u +%FT%TZ)] sheets: <what was missing or fixed> [FIXED|LOGGED]" >> state/log/loader-feedback.log

• FIXED = you patched this loader inline (P-fix).
• LOGGED = too large for inline; the PostToolUse enqueue + Stop-hook drain

will rewrite the loader from scratch on next session-end.

Do not skip this. Every agent run must leave the system better than it found it. The loader is the setpoint; you are the sensor; the gap is the error signal; closing the gap is the correction.