`split_phones.py`

81 lines
#!/usr/bin/env python3
"""
split_phones.py — on every tab that has a combined "Phone(s)" column, parse the multiple
numbers out of each cell and distribute them into Phone 1 / Phone 2 / Phone 3 (/4) columns
(created at the right if missing). Each becomes a single number the console renders as a
tap-to-call tel: link. Quota-safe: one values.batchUpdate per tab. ADD-only: won't clobber
an existing Phone N value. Leaves the source "Phone(s)" column intact (hide it in the UI).
"""
import lib, json, re, urllib.request, urllib.parse, urllib.error, time
PHONE = re.compile(r'(?:\+?1[-.\s]?)?\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}(?:\s*(?:x|ext\.?)\s*\d+)?', re.I)
MAXK = 4

def req(method, url, tok, body=None):
    r = urllib.request.Request(url, data=(None if body is None else json.dumps(body).encode()),
        method=method, headers={"Authorization": f"Bearer {tok}", "Content-Type": "application/json"})
    try:
        return json.load(urllib.request.urlopen(r))
    except urllib.error.HTTPError as e:
        raise RuntimeError(f"{e.code}: {e.read().decode()[:200]}")

def header_row(rows):
    best, bi = -1, 0
    for i in range(min(4, len(rows))):
        n = sum(1 for c in rows[i] if str(c).strip())
        if n > best: best, bi = n, i
    return bi

def nums(cell):
    return [m.group(0).strip() for m in PHONE.finditer(str(cell or ""))]

def main():
    tok = lib.access_token()
    for s in lib.get_meta(tok)["sheets"]:
        gid = s["properties"]["sheetId"]; title = s["properties"]["title"]
        rows = lib.read_tab(tok, title)
        if not rows: continue
        hr = header_row(rows)
        hdr = [str(c).strip() for c in rows[hr]]
        pi = next((i for i, h in enumerate(hdr) if h.lower() in ("phone(s)", "phones")), None)
        if pi is None: continue
        def g(r, i): return str(r[i]).strip() if i < len(r) else ""
        parsed = [nums(g(r, pi)) for r in rows]
        k = min(MAXK, max((len(p) for p in parsed[hr+1:]), default=0))
        if k == 0:
            print(f"  {title[:34]:34s} -> no parseable numbers"); continue
        # locate / append Phone 1..k columns
        width = max((len(r) for r in rows), default=len(hdr))
        right = width
        colidx = {}
        for j in range(1, k+1):
            label = f"Phone {j}"
            found = next((i for i, h in enumerate(hdr) if h.lower() == label.lower()), None)
            if found is None:
                colidx[j] = right; right += 1
            else:
                colidx[j] = found
        # widen grid if needed
        need = max(colidx.values()) + 1
        if need > width:
            req("POST", f"{lib.API}/{lib.SID}:batchUpdate", tok, {"requests": [{"appendDimension": {
                "sheetId": gid, "dimension": "COLUMNS", "length": need - width}}]})
        # build the data ranges (header + per-row) — ADD-only per existing value
        data = []
        for j in range(1, k+1):
            ci = colidx[j]; col = [[""] for _ in range(len(rows))]
            col[hr] = [f"Phone {j}"]
            for ri in range(hr+1, len(rows)):
                existing = g(rows[ri], ci)
                val = parsed[ri][j-1] if len(parsed[ri]) >= j else ""
                col[ri] = [existing if existing else val]
            colL = lib.col_letter(ci)
            data.append({"range": f"'{title}'!{colL}1:{colL}{len(col)}", "values": col})
        req("POST", f"{lib.API}/{lib.SID}/values:batchUpdate", tok,
            {"valueInputOption": "RAW", "data": data})
        filled = sum(1 for p in parsed[hr+1:] if p)
        print(f"  {title[:34]:34s} -> split into Phone 1..{k} ({filled} rows had numbers)")
        time.sleep(1.0)

if __name__ == "__main__":
    main()