`write_results.py`

82 lines
#!/usr/bin/env python3
"""
write_results.py <gid> <results.json>

Header-DRIVEN central writer. Finds each enrichment column by its HEADER LABEL in the
tab's header row (never by fragile offset math), so data always lands in the right column
regardless of each tab's hidden Status column. Auto-creates any missing enrichment header
(e.g. "Why (Marketing/VP)") in the next free column. Only fills EMPTY cells; every filled
cell is light-green. LinkedIn fields become clickable =HYPERLINK formulas.

results.json item shape (all optional except row):
  {"row": <1-based>, "website": "...", "company_li": "https://linkedin.com/company/..",
   "contact_li": "https://linkedin.com/in/..", "mvp": "Name — Title — <url>",
   "updated_email": "person@corp.com", "why": "why this marketing/VP was chosen"}
"""
import sys, os, json
sys.path.insert(0, os.path.dirname(__file__))
import lib, enrich

# result key -> exact header label in the sheet
LABELS = {
    "website":       "Website",
    "company_li":    "LinkedIn (Company)",
    "contact_li":    "LinkedIn (Contact)",
    "mvp":           "Marketing/VP Contact",
    "updated_email": "Updated Email (found)",
    "why":           "Why (Marketing/VP)",
}
LINK_KEYS = {"company_li", "contact_li"}

gid = int(sys.argv[1]); results = json.load(open(sys.argv[2]))
cfg = enrich.TABS[gid]; hr = cfg["header_row"]

tok = lib.access_token()
meta = lib.get_meta(tok)
title = {s["properties"]["sheetId"]: s["properties"]["title"] for s in meta["sheets"]}[gid]
rows = lib.read_tab(tok, title)
def cell(r0, c): return (rows[r0][c] if r0 < len(rows) and c < len(rows[r0]) else "").strip()

hdr = rows[hr] if hr < len(rows) else []
def hcell(i): return (hdr[i] if i < len(hdr) else "").strip()

# locate each label's column; track rightmost used column for placing new headers
col = {}
rightmost = 0
for i, v in enumerate(hdr):
    if v.strip():
        rightmost = i
for key, label in LABELS.items():
    found = next((i for i in range(len(hdr)) if hcell(i) == label), None)
    col[key] = found

# create any missing header (e.g. Why) after the rightmost used column
new_header_cells = []
nxt = rightmost + 1
for key, label in LABELS.items():
    if col[key] is None:
        col[key] = nxt
        new_header_cells.append({"row0": hr, "col0": nxt, "value": label})
        nxt += 1
if new_header_cells:
    lib.batch_fill(tok, gid, new_header_cells)

cells = []
for it in results:
    r0 = int(it["row"]) - 1
    for key in LABELS:
        val = (it.get(key) or "").strip()
        if not val:
            continue
        c = col[key]
        if cell(r0, c):          # never overwrite existing content
            continue
        if key in LINK_KEYS:
            cells.append({"row0": r0, "col0": c, "value": '=HYPERLINK("%s")' % val.replace('"', ""), "formula": True})
        else:
            cells.append({"row0": r0, "col0": c, "value": val})
res = lib.batch_fill(tok, gid, cells)
print(json.dumps({"gid": gid, "rows_in": len(results),
                  "headers_added": [c["value"] for c in new_header_cells],
                  "cells_written": res.get("totalUpdatedCells", 0)}))