`write_by_contact.py`

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

PER-CONTACT writer — row-shift immune, matched on (company + contact-name) so each
person's data lands on THEIR row (not smeared across every contact at the company like
the company-only write_by_name.py). Each result item = {company, name, contact_li?, email?}.
Only fills empty cells; contact_li becomes =HYPERLINK; email -> "Updated Email (found)".
Reports matched / unmatched / cells.
"""
import sys, os, json, re
sys.path.insert(0, os.path.dirname(__file__))
import lib
GID = int(sys.argv[1]); results = json.load(open(sys.argv[2]))
def norm(s): return re.sub(r'[^a-z0-9]', '', (s or '').lower())

tok = lib.access_token()
title = {s["properties"]["sheetId"]: s["properties"]["title"]
         for s in lib.get_meta(tok)["sheets"]}[GID]
rows = lib.read_tab(tok, title)
hr = next((i for i in range(min(4, len(rows)))
           for j, v in enumerate(rows[i]) if v.strip() == "Contact Name"), 0)
hdr = rows[hr]
def hidx(label): return next((i for i in range(len(hdr)) if (hdr[i] if i < len(hdr) else "").strip() == label), None)
NAME = hidx("Contact Name"); CO = hidx("Company/Venue")
LIC = hidx("LinkedIn (Contact)"); EM = hidx("Updated Email (found)")
# auto-create any missing target columns
rightmost = max((i for i, v in enumerate(hdr) if v.strip()), default=0); nxt = rightmost + 1
newh = []
if LIC is None: LIC = nxt; newh.append({"row0": hr, "col0": nxt, "value": "LinkedIn (Contact)"}); nxt += 1
if EM  is None: EM  = nxt; newh.append({"row0": hr, "col0": nxt, "value": "Updated Email (found)"}); nxt += 1
if newh: lib.batch_fill(tok, GID, newh)
def cell(r, i): return (rows[r][i] if i is not None and r < len(rows) and i < len(rows[r]) else "").strip()

# index (normalized company, normalized name) -> [row0]
idx = {}
for r in range(hr + 1, len(rows)):
    k = (norm(cell(r, CO)), norm(cell(r, NAME)))
    if k[0] or k[1]:
        idx.setdefault(k, []).append(r)

cells = []; matched = 0; unmatched = []
for it in results:
    key = (norm(it.get("company", "")), norm(it.get("name", "")))
    targets = idx.get(key)
    if not targets:  # fallback: same name, company contains
        targets = [r for (co, nm), rs in idx.items()
                   if nm and nm == key[1] and key[0] and (key[0] in co or co in key[0]) for r in rs]
    if not targets:
        unmatched.append(it.get("name", "") + " @ " + it.get("company", "")); continue
    matched += 1
    for r0 in targets:
        li = (it.get("contact_li") or "").strip()
        em = (it.get("email") or "").strip()
        if li and not cell(r0, LIC):
            cells.append({"row0": r0, "col0": LIC, "value": '=HYPERLINK("%s")' % li.replace('"', ""), "formula": True})
        if em and not cell(r0, EM):
            cells.append({"row0": r0, "col0": EM, "value": em})
res = lib.batch_fill(tok, GID, cells)
print(json.dumps({"gid": GID, "matched": matched, "unmatched": len(unmatched),
                  "cells_written": res.get("totalUpdatedCells", 0)}))