`propagate.py`

92 lines
#!/usr/bin/env python3
"""
propagate.py — push the MASTER's company-level enrichment out to EVERY tab.

Builds an enrichment map keyed by normalized company from the master
"Unique Contacts (all tabs)", then for each other tab: auto-detects its company
column (header contains company/venue/firm), auto-expands the grid, adds the
enrichment columns if missing, and fills them by company-name match (empty cells
only, light-green, LinkedIn as =HYPERLINK). Per-tab try/except so one bad tab
never blocks the rest.
"""
import lib, json, re, urllib.request
def norm(s): return re.sub(r'[^a-z0-9]','',(s or '').lower())
API="https://sheets.googleapis.com/v4/spreadsheets"

# columns we copy from master -> each tab (label -> is-hyperlink)
FIELDS=[("Website",False),("Company Phone (found)",False),("LinkedIn (Company)",True),
        ("Marketing/VP Name",False),("Marketing/VP Position",False),
        ("Marketing/VP LinkedIn",True),("Why (Marketing/VP)",False)]
SKIP={"Color Legend (inferred)","3","Unique Contacts (all tabs)","All Contacts (exploded)","Contacts (exploded)"}
CKEYS=("company","company/venue","venue","firm","organization","organization/venue")

def expand(tok,gid,to):
    req={"requests":[{"appendDimension":{"sheetId":gid,"dimension":"COLUMNS","length":to}}]}
    urllib.request.urlopen(urllib.request.Request(f"{API}/{lib.SID}:batchUpdate",
        data=json.dumps(req).encode(),headers={"Authorization":f"Bearer {tok}","Content-Type":"application/json"},method="POST"))

def main():
    tok=lib.access_token()
    meta=json.load(urllib.request.urlopen(urllib.request.Request(
        f"{API}/{lib.SID}?fields=sheets(properties(sheetId,title))",headers={"Authorization":f"Bearer {tok}"})))
    # 1) build enrichment map from master
    mrows=lib.read_tab(tok,"Unique Contacts (all tabs)")
    mh={v.strip():j for j,v in enumerate(mrows[0]) if v.strip()}
    def mg(r,i): return (r[i] if i<len(r) else "").strip()
    emap={}
    for r in range(1,len(mrows)):
        c=norm(mg(mrows[r],mh["Company/Venue"]))
        if not c or c in emap: continue
        d={}
        for lab,_ in FIELDS:
            if lab in mh:
                v=mg(mrows[r],mh[lab])
                if v.startswith("=HYPERLINK"):
                    m=re.match(r'=HYPERLINK\("([^"]+)"\)',v); v=m.group(1) if m else v
                if v: d[lab]=v
        if d: emap[c]=d
    print(f"master enrichment map: {len(emap)} companies")

    for s in meta["sheets"]:
        title=s["properties"]["title"]; gid=s["properties"]["sheetId"]
        if title in SKIP: continue
        try:
            rows=lib.read_tab(tok,title)
            # find company column + header row
            ccol=chrow=None
            for hi in range(min(3,len(rows))):
                for j,v in enumerate(rows[hi]):
                    if v.strip().lower() in CKEYS: ccol,chrow=j,hi; break
                if ccol is not None: break
            if ccol is None:
                print(f"  SKIP {title!r}: no company column"); continue
            hdr=rows[chrow]
            hlabels={v.strip():j for j,v in enumerate(hdr) if v.strip()}
            right=max((j for j,v in enumerate(hdr) if v.strip()),default=0)
            width=max(len(r) for r in rows) if rows else right+1
            # assign/locate target columns
            col={}; newh=[]; nxt=right+1
            for lab,_ in FIELDS:
                if lab in hlabels: col[lab]=hlabels[lab]
                else: col[lab]=nxt; newh.append((nxt,lab)); nxt+=1
            need=nxt
            if need>width: expand(tok,gid,need-width+2)
            def cg(r,i): return (rows[r][i] if r<len(rows) and i<len(rows[r]) else "").strip()
            cells=[{"row0":chrow,"col0":c,"value":lab} for c,lab in newh]
            for r in range(chrow+1,len(rows)):
                comp=norm(cg(r,ccol))
                d=emap.get(comp)
                if not d: continue
                for lab,islink in FIELDS:
                    val=d.get(lab)
                    if not val or cg(r,col[lab]): continue
                    if islink: cells.append({"row0":r,"col0":col[lab],"value":'=HYPERLINK("%s")'%val.replace('"',""),"formula":True})
                    else: cells.append({"row0":r,"col0":col[lab],"value":val})
            res=lib.batch_fill(tok,gid,cells)
            print(f"  {title!r}: +{len(newh)} cols, {res.get('totalUpdatedCells',0)} cells")
        except BaseException as e:
            print(f"  ERROR {title!r}: {str(e)[:80]}")

if __name__=="__main__":
    main()