`add_sheetname.py`

54 lines
#!/usr/bin/env python3
"""
add_sheetname.py — insert a "Sheet Name" column as the FIRST column (A) on every tab and
fill every data record with that tab's title, so each row carries its source sheet.
Idempotent: skips a tab that already has a "Sheet Name" column. One insert + one values
write per tab (quota-safe). Values written under the tab's real header row (title rows above
the header stay blank).
"""
import lib, json, urllib.request, urllib.parse, urllib.error, time

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 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:
            print(f"  {title[:34]:34s} -> empty, skipped"); continue
        if any(str(c).strip().lower() == "sheet name" for r in rows[:4] for c in r):
            print(f"  {title[:34]:34s} -> already has Sheet Name"); continue
        hr = header_row(rows)
        # 1) insert a new column at position 0
        req("POST", f"{lib.API}/{lib.SID}:batchUpdate", tok, {"requests": [{"insertDimension": {
            "range": {"sheetId": gid, "dimension": "COLUMNS", "startIndex": 0, "endIndex": 1},
            "inheritFromBefore": False}}]})
        # 2) fill column A: "Sheet Name" header at the header row, the title on every data row
        n = len(rows)
        col = [[""] for _ in range(n)]
        col[hr] = ["Sheet Name"]
        for i in range(hr + 1, n):
            col[i] = [title]
        rangeA1 = urllib.parse.quote(f"'{title}'!A1:A{n}", safe="")
        req("PUT", f"{lib.API}/{lib.SID}/values/{rangeA1}?valueInputOption=RAW", tok, {"values": col})
        print(f"  {title[:34]:34s} -> inserted Sheet Name (A), filled {n-hr-1} rows")
        time.sleep(1.2)  # stay under the 60 writes/min quota

if __name__ == "__main__":
    main()