`split_names.py`

105 lines
#!/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()