Schedule backlog refinement meetings from your sheet

from datetime import datetime, timedelta, timezone
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, ai

SHEET_ID = "your-spreadsheet-id"
TEAM = ["dev1@example.com", "dev2@example.com", "designer@example.com"]

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    rows = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values().get(
        spreadsheetId=SHEET_ID, range="Backlog!A2:D").execute().get("values", [])
    todo = sorted([r for r in rows if len(r) >= 3 and (len(r) < 4 or r[3].lower() != "refined")], key=lambda r: int(r[2]))[:9]
    cal = build("calendar", "v3", credentials=creds, cache_discovery=False)
    start = (datetime.now(timezone.utc) + timedelta(days=1)).replace(hour=14, minute=0, second=0, microsecond=0)
    for n in range(0, len(todo), 3):
        batch = todo[n:n + 3]
        agenda = ai.generate("Write a 3-line agenda for a refinement session on these stories:\n" + "\n".join(f"{r[0]}: {r[1]}" for r in batch), max_output_tokens=200).text
        s = start + timedelta(days=n // 3)
        cal.events().insert(calendarId="primary", sendUpdates="all", body={"summary": "Backlog refinement", "description": agenda,
            "start": {"dateTime": s.isoformat()}, "end": {"dateTime": (s + timedelta(minutes=45)).isoformat()},
            "attendees": [{"email": e} for e in TEAM]}).execute()
    return {"items": len(todo), "meetings": (len(todo) + 2) // 3}