`write_by_name.py`

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

NAME-MATCHED writer — immune to row shifts. Each result item carries a "company"; the
writer finds the CURRENT row(s) whose company-column value matches (normalized) and writes
there. Header-driven columns. Writes to EVERY matching row (a company can span multiple
contact rows). Only fills empty cells; LinkedIn fields become =HYPERLINK. Reports unmatched.
"""
import sys, os, json, re
sys.path.insert(0, os.path.dirname(__file__))
import lib, enrich

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"}
def norm(s): return re.sub(r'[^a-z0-9]','',(s or '').lower())

gid=int(sys.argv[1]); results=json.load(open(sys.argv[2]))
cfg=enrich.TABS[gid]
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)
# header row: locate by finding the LinkedIn (Company) label in first 4 rows
hr=next((i for i in range(min(4,len(rows))) for j,v in enumerate(rows[i]) if v.strip()=="LinkedIn (Company)"), cfg["header_row"])
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)
col={k:hidx(v) for k,v in LABELS.items()}
rightmost=max((i for i,v in enumerate(hdr) if v.strip()), default=0)
newh=[]; nxt=rightmost+1
for k,v in LABELS.items():
    if col[k] is None: col[k]=nxt; newh.append({"row0":hr,"col0":nxt,"value":v}); nxt+=1
if newh: lib.batch_fill(tok,gid,newh)
cc=cfg["company"]
def cell(r,i): return (rows[r][i] if r<len(rows) and i<len(rows[r]) else "").strip()

# index company-name -> list of row0 (only data rows below header)
byname={}
for r in range(hr+1,len(rows)):
    c=norm(cell(r,cc))
    if c: byname.setdefault(c,[]).append(r)

cells=[]; unmatched=[]
for it in results:
    key=norm(it.get("company",""))
    targets=byname.get(key)
    if not targets:  # loose contains-match fallback
        targets=[r for nm,rs in byname.items() if key and (key in nm or nm in key) for r in rs]
    if not targets:
        unmatched.append(it.get("company","")); continue
    for r0 in targets:
        for k in LABELS:
            val=(it.get(k) or "").strip()
            if not val or cell(r0,col[k]): continue
            if k in LINK_KEYS:
                cells.append({"row0":r0,"col0":col[k],"value":'=HYPERLINK("%s")'%val.replace('"',""),"formula":True})
            else:
                cells.append({"row0":r0,"col0":col[k],"value":val})
res=lib.batch_fill(tok,gid,cells)
print(json.dumps({"gid":gid,"matched":len(results)-len(unmatched),"unmatched":unmatched,"cells_written":res.get("totalUpdatedCells",0)}))