Log a daily snapshot of your Trello board to Google Sheets

from datetime import date
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, state, ai, notify

BOARD_ID = "your-board-id"
SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    auth = {"key": credentials.secret("TRELLO_API_KEY"), "token": credentials.secret("TRELLO_TOKEN")}
    lists = {l["id"]: l["name"] for l in http.get(f"https://api.trello.com/1/boards/{BOARD_ID}/lists", params=auth).json()}
    members = {m["id"]: m["fullName"] for m in http.get(f"https://api.trello.com/1/boards/{BOARD_ID}/members", params=auth).json()}
    cards = http.get(f"https://api.trello.com/1/boards/{BOARD_ID}/cards", params=auth).json()
    today = date.today().isoformat()
    rows = [[today, c["name"], lists.get(c["idList"], ""), c.get("due") or "", ", ".join(members.get(m, "") for m in c["idMembers"])] for c in cards]
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    sheets.spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Snapshots!A:E", valueInputOption="USER_ENTERED", body={"values": rows}).execute()
    now = {r[1]: r[2] for r in rows}
    before = state.get("last") or {}
    changes = [f"{n}: {before[n]} → {l}" for n, l in now.items() if n in before and before[n] != l]
    if changes:
        try:
            notify.email_me("Board changes today", ai.generate("\n".join(changes), system="Summarise these Trello moves in 3 sentences.", max_output_tokens=200).text)
        except ai.AIError:
            notify.email_me("Board changes today", "\n".join(changes))
    state.set("last", now)
    return {"rows": len(rows), "changes": len(changes)}