Google Sheets Connector¶

Wire Hal (opencode) to the user's personal Budget Google Sheet via a service-account key, scoped to only the nominated spreadsheet (nothing else on Drive).

Status¶

WORKING — connector verified: reads spreadsheet metadata + tab list with Editor access.

Architecture¶

opencode (Hal) ──Python venv (~/.venv-sheets)──> Google Sheets API v4
                        ▲
              service-account key
              ~/.config/gdrive/service-account.json  (mode 600)
              scoped to ONLY the Budget spreadsheet

Auth = service account (machine identity), NOT OAuth/user login.
For a private sheet owned by the user, a service account is the correct method — an API key cannot access private sheets.
The service account can only ever see sheets explicitly shared with it → this is the "nominate only this sheet" boundary.

Credentials & Paths¶

Item	Value / Path
Service account email	`sheet-connector@universal-talon-454006-c6.iam.gserviceaccount.com`
Google Cloud project	`universal-talon-454006-c6` (Sheets API enabled)
JSON key	`~/.config/gdrive/service-account.json` (mode `600`)
Python venv	`~/.venv-sheets` (google-api-python-client, google-auth)
Budget spreadsheet ID	`1C6BTvRvouxy7MdpeK9KkeKHJ_TH-KJm2c5wS_5mC6SQ`
Key script (probe)	`/tmp/sheets_probe.py`, `/tmp/sheets_read.py`

Reconstituting from memory (fresh machine / fresh session)¶

If the connector ever needs rebuilding:

Install python venv + libs:

python3 -m venv ~/.venv-sheets
~/.venv-sheets/bin/pip install google-api-python-client google-auth

Ensure key at ~/.config/gdrive/service-account.json (mode 600).

Probe:

from google.oauth2 import service_account
from googleapiclient.discovery import build
creds = service_account.Credentials.from_service_account_file(
    "/home/dini/.config/gdrive/service-account.json",
    scopes=["https://www.googleapis.com/auth/spreadsheets"])
svc = build("sheets", "v4", credentials=creds, cache_discovery=False)

The Budget Spreadsheet¶

Title: Budget — 13 tabs (one per year 2016–2027) + Copy of main, copy of 2020, Copy of 2018, worst case.
Layout: columns = weekly buckets (dates ~7 days apart); column A = line-item labels; same rows every year.
Row set (R2–R24): Dean (income ~5500, monthly salary) · Carryover (top+bottom running balance) · super · total · Storage · Karas Super · CAR LOAN · Food · Rent · save · car insur · mobile · Power · Net · petrol/opal · credit card · Total D.
Income model: paid MONTHLY; all deductions (rent, car loan, super, storage, power, net, mobile) stem from that income.
Current year: 2026 (live). Plan: 2027. Downside: worst case.

Key Intended Use → Cashflow / What-if Forecast¶

Goal: "if I spend £200 now, how does my balance ripple through the coming weeks/months?"

The Carryover row must be FORMULA-DRIVEN so editing any spending cell auto-recalculates the balance across all following weeks.
Before building 2028, verify whether existing sheets' carryover uses formulas (not hard-typed values). If hard-typed, build the formula chain.

2028 Sheet Plan (future task — see Memory `budget_2028_sheet_plan`)¶

Carryover opening balance comes from the END of the 2027 sheet (2027 → 2028), NOT 2026.
Clone the standard structure forward (same rows, weekly columns, extended into 2028).
Dean's monthly salary lands in the right weekly cells; deductions roll through.
Formula-driven so edits auto-ripple (live cashflow).
Trigger phrase: "create a 2028 sheet" / "build the 2028 sheet".

Scope & Security¶

Connector is scoped to only the Budget spreadsheet (via share grant).
Key is private (mode 600) and lives only on this machine.
Writing strategy: analysis always goes into new tabs/columns, never overwriting the user's raw entries, and only after explicit confirmation.
Work-side access is a future, deferred topic (always-on server / reaching in from work) — not yet built.

[[Hal Google MCP Integration]] — Gmail + Calendar via Google's remote MCP servers (separate OAuth path)
[[Retirement Calculator]]
[[Superannuation Strategy]]
[[HomeServer-Box]]