`pending-approval/fill_sheet.py`

75 lines
#!/usr/bin/env python3
"""
fill_sheet.py — write Image 1 / Image 2 / Alt / Description into the Fentucci
grasscloth sourcing sheet (cols G/H/I/J), keyed by sheet row.

CREDENTIAL: a service-account JSON from a project where the Google Sheets API is
ENABLED (you pointed at project `sheets-updater-485823`). The sheet must be SHARED
(Editor) with that service account's client_email.

Setup (one time):
  1. In console.cloud.google.com (project sheets-updater-485823):
     IAM & Admin -> Service Accounts -> (pick/create one) -> Keys -> Add key ->
     JSON. Download it.
  2. Put the JSON somewhere, e.g.  ~/Projects/secrets-manager/sheets-updater-sa.json
  3. Open the JSON, copy "client_email", and SHARE the sheet (Editor) with it.

Run:
  SA=~/Projects/secrets-manager/sheets-updater-sa.json python3 fill_sheet.py --dry   # preview
  SA=~/Projects/secrets-manager/sheets-updater-sa.json python3 fill_sheet.py         # write

Data source: /tmp/fentucci_filled.json  (list of {sheet_row,img1,img2,alt,description}).
"""
import os, sys, json, time
import gspread

SHEET_KEY = "1trKNm-ymqlbs96XJfndDUz19mzH8A11ktaw2lfQ2VQo"
DATA      = os.environ.get("DATA", "/tmp/fentucci_filled.json")
# columns: G=Image 1, H=Image 2, I=Alt Tags, J=Description
COLS = {"img1": "G", "img2": "H", "alt": "I", "description": "J"}
DRY  = "--dry" in sys.argv

def find_sa():
    p = os.environ.get("SA")
    if p and os.path.exists(os.path.expanduser(p)):
        return os.path.expanduser(p)
    for cand in [
        "~/Projects/secrets-manager/sheets-updater-sa.json",
        "~/.config/ga-analytics-agent/service-account.json",
    ]:
        cand = os.path.expanduser(cand)
        if os.path.exists(cand):
            return cand
    sys.exit("No service-account JSON. Set SA=/path/to/key.json (from a project with Sheets API enabled).")

def main():
    rows = json.load(open(DATA))
    print(f"{len(rows)} rows from {DATA} | mode: {'DRY-RUN' if DRY else 'WRITE'}")
    sa = find_sa()
    email = json.load(open(sa)).get("client_email")
    print(f"service account: {email}")
    if DRY:
        for r in rows[:5]:
            print(f"  row {r['sheet_row']}: G={r.get('img1','')[:50]!r} I={r.get('alt','')!r}")
        print(f"  ... ({len(rows)} rows). Re-run without --dry to write.")
        return
    gc = gspread.service_account(filename=sa)
    ws = gc.open_by_key(SHEET_KEY).sheet1
    # build one batch update: each row -> 4 cells G:J
    batch = []
    for r in rows:
        sr = r["sheet_row"]
        batch.append({"range": f"G{sr}:J{sr}",
                      "values": [[r.get("img1") or "", r.get("img2") or "",
                                  r.get("alt") or "", r.get("description") or ""]]})
    # chunk to stay under request limits
    CHUNK = 50
    for i in range(0, len(batch), CHUNK):
        ws.batch_update(batch[i:i+CHUNK], value_input_option="RAW")
        print(f"  wrote rows {i+1}-{min(i+CHUNK,len(batch))} of {len(batch)}")
        time.sleep(1)
    print("DONE ✅  filled G/H/I/J for", len(batch), "rows")

if __name__ == "__main__":
    main()