← back to Rentv Sheet Enrich Refine
propagate master enrichment (websites/phones/LinkedIn/Marketing-VP split/Why) to ALL company-bearing tabs
3a1c93d6004a0f02559442d35187a5723e250fbe · 2026-08-13 11:09:28 -0700 · Steve Abrams
Files touched
M __pycache__/lib.cpython-314.pycM lib.pyA propagate.py
Diff
commit 3a1c93d6004a0f02559442d35187a5723e250fbe
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Aug 13 11:09:28 2026 -0700
propagate master enrichment (websites/phones/LinkedIn/Marketing-VP split/Why) to ALL company-bearing tabs
---
__pycache__/lib.cpython-314.pyc | Bin 7324 -> 7441 bytes
lib.py | 3 +-
propagate.py | 91 ++++++++++++++++++++++++++++++++++++++++
3 files changed, 93 insertions(+), 1 deletion(-)
diff --git a/__pycache__/lib.cpython-314.pyc b/__pycache__/lib.cpython-314.pyc
index 86c249f..6e72972 100644
Binary files a/__pycache__/lib.cpython-314.pyc and b/__pycache__/lib.cpython-314.pyc differ
diff --git a/lib.py b/lib.py
index b29782a..2dc7287 100644
--- a/lib.py
+++ b/lib.py
@@ -54,7 +54,8 @@ def get_meta(tok):
def read_tab(tok, title):
"""Return the tab's values as a list of rows (list of str), padded ragged."""
- rng = urllib.parse.quote(f"{title}")
+ # A1 notation needs the sheet title wrapped in single quotes when it has spaces/slashes
+ rng = urllib.parse.quote("'" + title.replace("'", "''") + "'", safe="")
res = _req("GET", f"{API}/{SID}/values/{rng}?majorDimension=ROWS", tok)
return res.get("values", [])
diff --git a/propagate.py b/propagate.py
new file mode 100644
index 0000000..c42c4f2
--- /dev/null
+++ b/propagate.py
@@ -0,0 +1,91 @@
+#!/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","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()
← 7de7dbb master: 770 company phones from $0 website scrape written (1
·
back to Rentv Sheet Enrich Refine
·
propagate to Sheet2 venues (7317) + Locations; all company/v e30cac5 →