`add_lastmod.py`

53 lines
#!/usr/bin/env python3
"""
add_lastmod.py — ensure a "Last Modified" column exists on every tab that has a "Notes"
column (append at the right if missing). The console stamps it with the date+time whenever
a Notes cell is edited. Quota-safe: 1 header write per tab via the values API.
"""
import lib, json, urllib.request, urllib.error, urllib.parse

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:
            continue
        hr = header_row(rows)
        hdr = [str(c).strip() for c in rows[hr]]
        if not any(h.lower() == "notes" for h in hdr):
            continue  # no Notes column -> no Last Modified needed
        if any(h.lower() == "last modified" for h in hdr):
            print(f"  {title[:34]:34s} -> already has Last Modified"); continue
        # append at the rightmost used column + 1
        width = max((len(r) for r in rows), default=len(hdr))
        col = max(width, len(hdr))  # 0-based index of the new column
        colL = lib.col_letter(col)
        # ensure grid is wide enough
        if col + 1 > width:
            req("POST", f"{lib.API}/{lib.SID}:batchUpdate", tok, {"requests": [{"appendDimension": {
                "sheetId": gid, "dimension": "COLUMNS", "length": col + 1 - width}}]})
        rangeA1 = urllib.parse.quote(f"'{title}'!{colL}{hr+1}", safe="")
        req("PUT", f"{lib.API}/{lib.SID}/values/{rangeA1}?valueInputOption=RAW", tok,
            {"values": [["Last Modified"]]})
        print(f"  {title[:34]:34s} -> added Last Modified at col {colL} (header row {hr})")

if __name__ == "__main__":
    main()