← back to Rentv Sheet Enrich Refine
scaffold: Sheets API connect + stdlib read/write lib with light-green fill marking
915689c9f898306d46e266290423d7c1c40aa20e · 2026-08-13 09:18:01 -0700 · Steve Abrams
Files touched
A .gitignoreA connect.pyA lib.py
Diff
commit 915689c9f898306d46e266290423d7c1c40aa20e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Aug 13 09:18:01 2026 -0700
scaffold: Sheets API connect + stdlib read/write lib with light-green fill marking
---
.gitignore | 9 +++++++
connect.py | 83 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
lib.py | 83 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3 files changed, 175 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..49021c1
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,9 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+data/*.csv
+data/*.json
diff --git a/connect.py b/connect.py
new file mode 100644
index 0000000..44e22f6
--- /dev/null
+++ b/connect.py
@@ -0,0 +1,83 @@
+#!/usr/bin/env python3
+"""
+connect.py — ONE-TIME Google Sheets auth for the RENTV CRE-sheet enrichment.
+
+Reuses the existing DESKTOP-type OAuth client (YT_UPLOAD_CLIENT_ID/SECRET) that
+already auto-trusts the http://localhost loopback — so there is NOTHING to register
+in the Google console. Runs the consent flow once, captures a long-lived refresh
+token, and stores it as SHEETS_REFRESH_TOKEN in the secure secrets store.
+
+IMPORTANT: log in with the Google account that can EDIT the target spreadsheet.
+
+Run it yourself in this session with the `!` prefix so the browser opens here:
+ ! python3 ~/Projects/rentv-sheet-enrich/connect.py
+Then click "Allow".
+"""
+import os, sys, json, time, urllib.parse, urllib.request, http.server, threading, webbrowser, re
+
+ENV_PATH = os.path.expanduser("~/Projects/secrets-manager/.env")
+
+def load_env(path):
+ d = {}
+ try:
+ for ln in open(path):
+ m = re.match(r'^([A-Z0-9_]+)=(.*)$', ln.rstrip("\n"))
+ if m:
+ d[m.group(1)] = m.group(2)
+ except FileNotFoundError:
+ pass
+ return d
+
+def upsert_env(path, kv):
+ try: lines = open(path).read().splitlines()
+ except FileNotFoundError: lines = []
+ keys = set(kv); out, seen = [], set()
+ for ln in lines:
+ m = re.match(r'^([A-Z0-9_]+)=', ln)
+ if m and m.group(1) in keys:
+ out.append(f'{m.group(1)}={kv[m.group(1)]}'); seen.add(m.group(1))
+ else:
+ out.append(ln)
+ for k, v in kv.items():
+ if k not in seen: out.append(f'{k}={v}')
+ open(path, "w").write("\n".join(out) + "\n")
+
+envf = load_env(ENV_PATH)
+CID = os.environ.get("YT_UPLOAD_CLIENT_ID") or envf.get("YT_UPLOAD_CLIENT_ID") or sys.exit("No desktop OAuth client id found (YT_UPLOAD_CLIENT_ID)")
+CSEC = os.environ.get("YT_UPLOAD_CLIENT_SECRET") or envf.get("YT_UPLOAD_CLIENT_SECRET") or sys.exit("No desktop OAuth client secret found")
+PORT = int(os.environ.get("SHEETS_PORT", "8733"))
+REDIRECT = f"http://localhost:{PORT}/"
+SCOPE = "https://www.googleapis.com/auth/spreadsheets"
+
+hold = {}
+class H(http.server.BaseHTTPRequestHandler):
+ def do_GET(self):
+ p = urllib.parse.parse_qs(urllib.parse.urlparse(self.path).query)
+ if "code" in p: hold["code"] = p["code"][0]
+ elif "error" in p: hold["error"] = p["error"][0]
+ self.send_response(200); self.send_header("Content-Type", "text/html"); self.end_headers()
+ self.wfile.write(b"<h2 style='font-family:sans-serif'>Sheets connected. You can close this tab.</h2>")
+ def log_message(self, *a): pass
+
+srv = http.server.HTTPServer(("127.0.0.1", PORT), H)
+threading.Thread(target=srv.serve_forever, daemon=True).start()
+auth = "https://accounts.google.com/o/oauth2/v2/auth?" + urllib.parse.urlencode({
+ "client_id": CID, "redirect_uri": REDIRECT, "response_type": "code",
+ "scope": SCOPE, "access_type": "offline", "prompt": "consent"})
+print("AUTH_URL:", auth, flush=True)
+try: webbrowser.open(auth)
+except Exception: pass
+print("Waiting for consent (Allow in the browser)...", flush=True)
+for _ in range(300):
+ if hold: break
+ time.sleep(1)
+srv.shutdown()
+if "code" not in hold: sys.exit(f"No consent captured: {hold or 'timeout'}")
+
+data = urllib.parse.urlencode({"code": hold["code"], "client_id": CID, "client_secret": CSEC,
+ "redirect_uri": REDIRECT, "grant_type": "authorization_code"}).encode()
+tok = json.load(urllib.request.urlopen(urllib.request.Request("https://oauth2.googleapis.com/token", data)))
+rt = tok.get("refresh_token")
+if not rt: sys.exit("No refresh_token returned (revoke prior grant + retry).")
+upsert_env(ENV_PATH, {"SHEETS_REFRESH_TOKEN": rt})
+print("CONNECTED — Sheets refresh token saved. Enrichment can now write to the sheet.", flush=True)
diff --git a/lib.py b/lib.py
new file mode 100644
index 0000000..e12be3f
--- /dev/null
+++ b/lib.py
@@ -0,0 +1,83 @@
+#!/usr/bin/env python3
+"""
+lib.py — pure-stdlib Google Sheets read/write helpers with light-green fill marking.
+
+No external deps (no gspread / google-api-python-client). Refreshes an access token
+from SHEETS_REFRESH_TOKEN + the desktop OAuth client, then talks to the Sheets REST API.
+
+Every cell this library writes is stamped with a LIGHT-GREEN background so Steve can
+see at a glance what was auto-filled vs. what was already in the sheet.
+"""
+import os, re, json, urllib.parse, urllib.request
+
+SID = os.environ.get("SHEET_ID", "1yDmtj9Wxe71SaoQm8xpFsn722B4Ws67hTtPIHeXoN9E")
+ENV_PATH = os.path.expanduser("~/Projects/secrets-manager/.env")
+API = "https://sheets.googleapis.com/v4/spreadsheets"
+
+# Light green fill for auto-populated cells (≈ #cbe6b4)
+GREEN = {"red": 0.796, "green": 0.902, "blue": 0.706}
+
+def _env():
+ d = {}
+ try:
+ for ln in open(ENV_PATH):
+ m = re.match(r'^([A-Z0-9_]+)=(.*)$', ln.rstrip("\n"))
+ if m: d[m.group(1)] = m.group(2)
+ except FileNotFoundError:
+ pass
+ return d
+
+def access_token():
+ e = _env()
+ cid = e.get("YT_UPLOAD_CLIENT_ID"); csec = e.get("YT_UPLOAD_CLIENT_SECRET")
+ rt = e.get("SHEETS_REFRESH_TOKEN")
+ if not rt:
+ raise SystemExit("Not connected yet — run connect.py first (SHEETS_REFRESH_TOKEN missing).")
+ data = urllib.parse.urlencode({
+ "client_id": cid, "client_secret": csec,
+ "refresh_token": rt, "grant_type": "refresh_token"}).encode()
+ tok = json.load(urllib.request.urlopen(
+ urllib.request.Request("https://oauth2.googleapis.com/token", data)))
+ return tok["access_token"]
+
+def _req(method, url, tok, body=None):
+ data = json.dumps(body).encode() if body is not None else None
+ r = urllib.request.Request(url, data=data, method=method,
+ headers={"Authorization": f"Bearer {tok}", "Content-Type": "application/json"})
+ try:
+ return json.load(urllib.request.urlopen(r))
+ except urllib.error.HTTPError as ex:
+ raise SystemExit(f"Sheets API {method} {url.split('?')[0]} -> {ex.code}: {ex.read().decode()[:500]}")
+
+def get_meta(tok):
+ return _req("GET", f"{API}/{SID}?fields=sheets(properties(sheetId,title,gridProperties))", tok)
+
+def read_tab(tok, title):
+ """Return the tab's values as a list of rows (list of str), padded ragged."""
+ rng = urllib.parse.quote(f"{title}")
+ res = _req("GET", f"{API}/{SID}/values/{rng}?majorDimension=ROWS", tok)
+ return res.get("values", [])
+
+def col_letter(idx0):
+ s = ""; n = idx0
+ while True:
+ s = chr(ord('A') + n % 26) + s; n = n // 26 - 1
+ if n < 0: break
+ return s
+
+def batch_fill(tok, sheet_id, cells):
+ """
+ cells: list of dicts {row0, col0, value} (0-based row/col indices)
+ Writes each value AND stamps a light-green background, in ONE batchUpdate.
+ """
+ reqs = []
+ for c in cells:
+ reqs.append({"updateCells": {
+ "start": {"sheetId": sheet_id, "rowIndex": c["row0"], "columnIndex": c["col0"]},
+ "rows": [{"values": [{
+ "userEnteredValue": {"stringValue": str(c["value"])},
+ "userEnteredFormat": {"backgroundColor": GREEN}}]}],
+ "fields": "userEnteredValue,userEnteredFormat.backgroundColor"}})
+ if not reqs:
+ return {"skipped": True}
+ return _req("POST", f"{API}/{SID}:batchUpdate", tok, {"requests": reqs})
(oldest)
·
back to Rentv Sheet Enrich Refine
·
enrich Layer 1: website-from-email-domain engine + dry-run ( f280a73 →