`split_mvp.py`

66 lines
#!/usr/bin/env python3
"""
split_mvp.py — parse the packed "Marketing/VP Contact" cell (col 18), format
    "Name — Title — https://linkedin.com/in/<slug>"  (em-dash separated)
into the three split columns so coverage/sort read clean fields:
    22 Marketing/VP Name · 23 Marketing/VP Position · 24 Marketing/VP LinkedIn (HYPERLINK)
Only fills split cells that are EMPTY (never overwrites — provenance = ADD-only).
LinkedIn written as a green =HYPERLINK() live link. Run after every VP wave.
"""
import lib, re
GID = 3823360
DASH = re.compile(r"\s+—\s+|\s+-\s+")  # em-dash primary, hyphen fallback
LI = re.compile(r"https?://[^\s]*linkedin\.com/in/[^\s]+", re.I)

def parse(packed):
    """Return (name, title, li_url) from a packed Marketing/VP Contact value."""
    packed = packed.strip()
    if not packed:
        return None
    li = ""
    m = LI.search(packed)
    if m:
        li = m.group(0).rstrip("/.,)")
        packed = packed[:m.start()].strip().rstrip("—-").strip()
    parts = [p.strip() for p in DASH.split(packed) if p.strip()]
    name = parts[0] if parts else ""
    title = " ".join(parts[1:]) if len(parts) > 1 else ""
    if not name:
        return None
    return name, title, li

def main():
    tok = lib.access_token()
    title = {s["properties"]["sheetId"]: s["properties"]["title"]
             for s in lib.get_meta(tok)["sheets"]}[GID]
    rows = lib.read_tab(tok, title)
    hdr = rows[0]
    c = {v.strip(): j for j, v in enumerate(hdr) if v.strip()}
    PACK = c["Marketing/VP Contact"]; NAME = c["Marketing/VP Name"]
    POS = c["Marketing/VP Position"]; LIC = c["Marketing/VP LinkedIn"]
    def g(r, i): return (r[i] if i < len(r) else "").strip()
    cells = []; n = 0
    for r in range(1, len(rows)):
        packed = g(rows[r], PACK)
        if not packed:
            continue
        pr = parse(packed)
        if not pr:
            continue
        name, tit, li = pr
        if name and not g(rows[r], NAME):
            cells.append({"row0": r, "col0": NAME, "value": name})
        if tit and not g(rows[r], POS):
            cells.append({"row0": r, "col0": POS, "value": tit})
        if li and not g(rows[r], LIC):
            cells.append({"row0": r, "col0": LIC,
                          "value": f'=HYPERLINK("{li}","LinkedIn")', "formula": True})
        n += 1
    print(f"{n} packed Marketing/VP rows scanned -> {len(cells)} split cells to write")
    if cells:
        lib.batch_fill(tok, GID, cells)
    print("done")

if __name__ == "__main__":
    main()