← back to Rentv Sheet Enrich
Split Contact Name -> First Name + Last Name across ALL 19 name-bearing tabs (parallel, quota-safe values+green writes, auto grid-expand); handles Contact Name/Contact Name(s)/Contact variants + title-row headers
48eb172f69518dfaa174888cd7b0d2fd038ad5ba · 2026-08-13 13:33:05 -0700 · Steve Abrams
Files touched
Diff
commit 48eb172f69518dfaa174888cd7b0d2fd038ad5ba
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Aug 13 13:33:05 2026 -0700
Split Contact Name -> First Name + Last Name across ALL 19 name-bearing tabs (parallel, quota-safe values+green writes, auto grid-expand); handles Contact Name/Contact Name(s)/Contact variants + title-row headers
---
split_names.py | 104 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 104 insertions(+)
diff --git a/split_names.py b/split_names.py
new file mode 100644
index 0000000..b359094
--- /dev/null
+++ b/split_names.py
@@ -0,0 +1,104 @@
+#!/usr/bin/env python3
+"""
+split_names.py — add "First Name" + "Last Name" columns to EVERY tab that has a
+"Contact Name" column, parsed from that name. Quota-safe: writes each tab's two columns
+as ONE values-range update (not per-cell) + ONE green-format call = 2 API calls/tab, so
+it never trips the 60-writes/min Sheets limit. Handles "First Last", "First M. Last",
+"Last, First"; skips non-person rows. Columns appended at the right (non-destructive).
+Runs tabs with light parallelism + 429 retry. ADD-only: won't clobber existing values.
+"""
+import lib, re, time, urllib.request, urllib.error
+from concurrent.futures import ThreadPoolExecutor
+
+SUFFIX = {"jr","sr","ii","iii","iv","cpa","esq","mba","phd","mai","ccim"}
+GREEN = lib.GREEN
+NAME_LABELS = ["Contact Name", "Contact Name(s)", "Contact Person", "Full Name", "Contact", "Name", "Client"]
+
+def split(name):
+ name = (name or "").strip()
+ if not name or not name[0].isalpha(): return "", ""
+ # these tabs are "First Last"; a comma/&/;/"and" separates MULTIPLE people -> take the first
+ name = re.split(r'\s*(?:[,;&/]|\band\b)\s*', name)[0].strip()
+ toks = [t for t in name.split() if t]
+ while len(toks) > 1 and re.sub(r'[^a-z]', '', toks[-1].lower()) in SUFFIX: toks.pop()
+ if not toks: return "", ""
+ if len(toks) == 1: return toks[0], ""
+ return toks[0], toks[-1]
+
+def find_header(rows):
+ """Return (header_row_index, name_col_index) or (None, None)."""
+ for hr in range(min(5, len(rows))):
+ row = [c.strip() for c in rows[hr]]
+ for lbl in NAME_LABELS:
+ if lbl in row:
+ return hr, row.index(lbl)
+ return None, None
+
+def req(method, url, tok, body, tries=5):
+ for a in range(tries):
+ try:
+ r = urllib.request.Request(url, data=(None if body is None else __import__("json").dumps(body).encode()),
+ method=method, headers={"Authorization": f"Bearer {tok}", "Content-Type": "application/json"})
+ return __import__("json").load(urllib.request.urlopen(r))
+ except urllib.error.HTTPError as e:
+ if e.code == 429 and a < tries-1:
+ time.sleep(8 * (a+1)); continue
+ raise RuntimeError(f"{e.code}: {e.read().decode()[:300]}")
+
+def process(tok, title, gid):
+ rows = lib.read_tab(tok, title)
+ if not rows: return (title, "empty")
+ hr, NAME = find_header(rows)
+ if hr is None: return (title, "skip (no name column)")
+ hdr = [h.strip() for h in rows[hr]]
+ def g(r, i): return (r[i] if i < len(r) else "").strip()
+ right = max((i for i, v in enumerate(hdr) if v.strip()), default=0)
+ existF = hdr.index("First Name") if "First Name" in hdr else None
+ existL = hdr.index("Last Name") if "Last Name" in hdr else None
+ FN = existF if existF is not None else right + 1
+ LN = existL if existL is not None else (FN + 1)
+ # ensure the grid is wide enough (expand columns if needed) before writing
+ width = max(len(r) for r in rows)
+ need = LN + 1
+ if need > width:
+ req("POST", f"{lib.API}/{lib.SID}:batchUpdate", tok, {"requests": [{"appendDimension": {
+ "sheetId": gid, "dimension": "COLUMNS", "length": need - width}}]})
+ # build full-length columns: blanks above header, header at hr, split values below
+ fcol = [[""] for _ in range(len(rows))]; lcol = [[""] for _ in range(len(rows))]
+ fcol[hr] = ["First Name"]; lcol[hr] = ["Last Name"]
+ filled = 0
+ for ri in range(hr + 1, len(rows)):
+ first, last = split(g(rows[ri], NAME))
+ if existF is not None and g(rows[ri], existF): first = g(rows[ri], existF) # ADD-only
+ if existL is not None and g(rows[ri], existL): last = g(rows[ri], existL)
+ fcol[ri] = [first]; lcol[ri] = [last]
+ filled += (1 if first else 0) + (1 if last else 0)
+ fL, lL = lib.col_letter(FN), lib.col_letter(LN)
+ req("POST", f"{lib.API}/{lib.SID}/values:batchUpdate", tok, {
+ "valueInputOption": "RAW",
+ "data": [
+ {"range": f"'{title}'!{fL}1:{fL}{len(fcol)}", "values": fcol},
+ {"range": f"'{title}'!{lL}1:{lL}{len(lcol)}", "values": lcol},
+ ]})
+ def greenreq(col):
+ return {"repeatCell": {
+ "range": {"sheetId": gid, "startRowIndex": hr, "endRowIndex": len(rows),
+ "startColumnIndex": col, "endColumnIndex": col+1},
+ "cell": {"userEnteredFormat": {"backgroundColor": GREEN}},
+ "fields": "userEnteredFormat.backgroundColor"}}
+ req("POST", f"{lib.API}/{lib.SID}:batchUpdate", tok, {"requests": [greenreq(FN), greenreq(LN)]})
+ return (title, f"header@row{hr} '{hdr[NAME]}' -> split {len(rows)-1-hr} rows ({filled} cells)")
+
+def main():
+ tok = lib.access_token()
+ sheets = [(s["properties"]["title"], s["properties"]["sheetId"]) for s in lib.get_meta(tok)["sheets"]]
+ def safe(ts):
+ try: return process(tok, ts[0], ts[1])
+ except Exception as e: return (ts[0], f"ERROR {e}")
+ with ThreadPoolExecutor(max_workers=3) as ex: # 3 tabs at a time * 2 calls = well under 60/min
+ results = list(ex.map(safe, sheets))
+ for title, msg in results:
+ print(f" {title[:36]:36s} -> {msg}")
+
+if __name__ == "__main__":
+ main()
← 0d5bb2d auto-data-snapshot: 2026-08-13T13:27:38 (3 data files) — dat
·
back to Rentv Sheet Enrich
·
Rearchitect console: serve from OUR OWN local snapshot (snap ef8ce09 →