`scripts/refresh.py`

55 lines
#!/usr/bin/env python3
"""Pull the SDCC media-contacts spreadsheet from Google Drive and emit data/journalists.json.

The Drive file (gdrive:SDCC/sdcc-journalists-media-contacts.xlsx) is the source of
truth — team edits in Google Sheets/Drive land here on every refresh.
"""
import json, subprocess, sys, os, datetime

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
DATA = os.path.join(ROOT, "data")
XLSX = os.path.join(DATA, "sdcc-journalists-media-contacts.xlsx")
OUT = os.path.join(DATA, "journalists.json")

# 1. pull latest from Drive (rclone only copies if changed)
r = subprocess.run(["rclone", "copy", "gdrive:SDCC/sdcc-journalists-media-contacts.xlsx", DATA],
                   capture_output=True, text=True, timeout=120)
drive_ok = r.returncode == 0
if not drive_ok:
    sys.stderr.write(f"rclone pull failed: {r.stderr[:300]}\n")
    if not os.path.exists(XLSX):
        sys.exit(2)  # nothing cached either — hard fail

# 2. parse xlsx (headers row 1; hyperlinks preserved per cell)
from openpyxl import load_workbook
wb = load_workbook(XLSX)
ws = wb.active
headers = [str(c.value or "").strip() for c in ws[1]]

def key(h):
    return h.lower().replace(" (", "_").replace(")", "").replace(" ", "_").replace("(", "").replace("__", "_").strip("_")

rows = []
for row in ws.iter_rows(min_row=2):
    if not any(c.value for c in row):
        continue
    rec, links = {}, {}
    for h, c in zip(headers, row):
        k = key(h)
        rec[k] = "" if c.value is None else str(c.value)
        if c.hyperlink and c.hyperlink.target:
            links[k] = c.hyperlink.target
    rec["_links"] = links
    rows.append(rec)

payload = {
    "generated_at": datetime.datetime.now().astimezone().isoformat(),
    "drive_pull_ok": drive_ok,
    "source": "gdrive:SDCC/sdcc-journalists-media-contacts.xlsx",
    "count": len(rows),
    "rows": rows,
}
with open(OUT, "w") as f:
    json.dump(payload, f)
print(f"refreshed: {len(rows)} rows, drive_pull_ok={drive_ok}")