#!/usr/bin/env python3 """ Fix Workflow 0 end-to-end pipeline: 1. Add missing columns (G-K) to EN Onboarding Queue sheet header 2. Patch live n8n workflow 21cXlP5tZ1WF3PIq: - Sheets — Add to Onboarding Queue: sheetName -> {value:"Sheet1", mode:"name"}, add columns.schema = [] - Check Queue for Duplicate: sheetName -> {value:"Sheet1", mode:"name"} 3. Insert Rachel Bergeron's queue row so worker picks her up (bypassing n8n for this one) Usage: python3 tools/fix_onboarding_pipeline.py --step 1 python3 tools/fix_onboarding_pipeline.py --step 2 python3 tools/fix_onboarding_pipeline.py --step 3 """ import os, sys, json, time, argparse, urllib.request, urllib.parse, pathlib ROOT = pathlib.Path(__file__).parent.parent for line in (ROOT / ".env").read_text().splitlines(): line = line.strip() if line and not line.startswith("#") and "=" in line: k, _, v = line.partition("=") os.environ.setdefault(k.strip(), v.strip()) QUEUE_SHEET_ID = "1FX0eDt9d322XYcOQRp8b9tXkRrrrHcSWJ9maXEwYk7k" N8N_WF_ID = "21cXlP5tZ1WF3PIq" SHEETS_BASE = "https://sheets.googleapis.com/v4/spreadsheets" GTOKEN_PATH = pathlib.Path.home() / ".config/google-workspace-mcp/profiles/emerson-north/tokens.json" GCREDS_PATH = pathlib.Path.home() / ".config/google-workspace-mcp/profiles/emerson-north/credentials.json" def google_token(): t = json.loads(GTOKEN_PATH.read_text()) if t.get("expiry_date", 0) / 1000 > time.time() + 60: return t["access_token"] c = json.loads(GCREDS_PATH.read_text()) w = c.get("web") or c.get("installed", {}) data = urllib.parse.urlencode({ "client_id": w["client_id"], "client_secret": w["client_secret"], "refresh_token": t["refresh_token"], "grant_type": "refresh_token", }).encode() r = json.loads(urllib.request.urlopen(urllib.request.Request( "https://oauth2.googleapis.com/token", data=data, headers={"Content-Type": "application/x-www-form-urlencoded"}), timeout=15).read()) t["access_token"] = r["access_token"] t["expiry_date"] = int((time.time() + r["expires_in"]) * 1000) GTOKEN_PATH.write_text(json.dumps(t, indent=2)) return t["access_token"] def sheets_write(token, range_, values): url = f"{SHEETS_BASE}/{QUEUE_SHEET_ID}/values/{urllib.parse.quote(range_)}?valueInputOption=USER_ENTERED" body = json.dumps({"range": range_, "values": values}).encode() req = urllib.request.Request(url, data=body, method="PUT", headers={ "Authorization": f"Bearer {token}", "Content-Type": "application/json"}) return json.loads(urllib.request.urlopen(req, timeout=20).read()) def sheets_append(token, range_, values): url = f"{SHEETS_BASE}/{QUEUE_SHEET_ID}/values/{urllib.parse.quote(range_)}:append?valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS" body = json.dumps({"range": range_, "values": values}).encode() req = urllib.request.Request(url, data=body, method="POST", headers={ "Authorization": f"Bearer {token}", "Content-Type": "application/json"}) return json.loads(urllib.request.urlopen(req, timeout=20).read()) def sheets_get(token, range_): url = f"{SHEETS_BASE}/{QUEUE_SHEET_ID}/values/{urllib.parse.quote(range_)}" req = urllib.request.Request(url, headers={"Authorization": f"Bearer {token}"}) return json.loads(urllib.request.urlopen(req, timeout=15).read()).get("values", []) def n8n(method, path, body=None): base = os.environ["N8N_BASE_URL"].rstrip("/") req = urllib.request.Request(f"{base}{path}", method=method, headers={ "X-N8N-API-KEY": os.environ["N8N_API_KEY"], "Content-Type": "application/json", "Accept": "application/json", }, data=json.dumps(body).encode() if body is not None else None) return json.loads(urllib.request.urlopen(req, timeout=30).read()) # ── Step 1: queue sheet header ──────────────────────────────────────────────── def step1(): token = google_token() current = sheets_get(token, "Sheet1!A1:K1") print(f"Current header: {current[0] if current else '(empty)'}") new_header = [[ "ID", "Business Name", "Status", "Submitted At", "Processed At", "Notes", "GHL Contact ID", "Brand Color Primary", "Brand Color Secondary", "Brand Files", "Services Enabled", ]] sheets_write(token, "Sheet1!A1:K1", new_header) after = sheets_get(token, "Sheet1!A1:K1") print(f"New header: {after[0]}") # ── Step 2: n8n workflow patch ──────────────────────────────────────────────── def step2(): wf = n8n("GET", f"/api/v1/workflows/{N8N_WF_ID}") print(f"Fetched: {wf['name']} ({len(wf['nodes'])} nodes)") fixed_append = False fixed_dedupe = False for n in wf["nodes"]: if n["name"] == "Sheets — Add to Onboarding Queue": n["parameters"]["sheetName"] = {"__rl": True, "value": "Sheet1", "mode": "name"} cols = n["parameters"].setdefault("columns", {}) if "schema" not in cols: cols["schema"] = [] fixed_append = True print(" ✓ Fixed Sheets — Add to Onboarding Queue") elif n["name"] == "Check Queue for Duplicate": n["parameters"]["sheetName"] = {"__rl": True, "value": "Sheet1", "mode": "name"} fixed_dedupe = True print(" ✓ Fixed Check Queue for Duplicate") elif n["name"] == "Sheets — Log OAuth Connections": n["parameters"]["sheetName"] = {"__rl": True, "value": "Sheet1", "mode": "name"} cols = n["parameters"].setdefault("columns", {}) if "schema" not in cols: cols["schema"] = [] print(" ✓ Fixed Sheets — Log OAuth Connections (preventative)") if not (fixed_append and fixed_dedupe): print(f"WARN: append_fixed={fixed_append} dedupe_fixed={fixed_dedupe}") update_body = { "name": wf["name"], "nodes": wf["nodes"], "connections": wf["connections"], "settings": wf.get("settings", {}), } if wf.get("staticData") is not None: update_body["staticData"] = wf["staticData"] result = n8n("PUT", f"/api/v1/workflows/{N8N_WF_ID}", update_body) print(f"✓ PUT ok. active={result.get('active')}") # Re-read to verify wf2 = n8n("GET", f"/api/v1/workflows/{N8N_WF_ID}") for n in wf2["nodes"]: if n["name"] in ("Sheets — Add to Onboarding Queue", "Check Queue for Duplicate"): print(f" verify {n['name']}: sheetName={json.dumps(n['parameters'].get('sheetName'))}") # ── Step 3: insert Rachel's queue row ───────────────────────────────────────── def step3(): token = google_token() row_id = f"rachel-{int(time.time())}" rachel = [[ row_id, # ID "NaturallyRachB", # Business Name "pending", # Status "2026-04-13T18:59:34.556Z", # Submitted At "", # Processed At "manual insert — skipped n8n (bug fix session)", # Notes "HUTOQ8tnJ60fpCqt4H7i", # GHL Contact ID "", # Brand Color Primary "", # Brand Color Secondary "[]", # Brand Files "dashboard,resources,content,campaigns,products,assistant,profile", # Services Enabled ]] sheets_append(token, "Sheet1!A1:K1", rachel) all_rows = sheets_get(token, "Sheet1!A:K") print(f"Total rows after insert: {len(all_rows)}") for r in all_rows: print(" ", r[:6]) if __name__ == "__main__": p = argparse.ArgumentParser() p.add_argument("--step", type=int, required=True, choices=[1, 2, 3]) args = p.parse_args() {1: step1, 2: step2, 3: step3}[args.step]()