Get a weekly email summary of new form responses

from clickruntime import on_trigger, credentials, http, state, ai, notify

SHEET = "your-spreadsheet-id"

@on_trigger
def run(event):
    h = {"Authorization": f"Bearer {credentials.connection('google', 'work')}"}
    rows = http.get(f"https://sheets.googleapis.com/v4/spreadsheets/{SHEET}/values/Responses!A1:Z", headers=h).json().get("values", [])
    if not rows:
        return {"new": 0}
    header, data = rows[0], rows[1:]
    done = state.get("rows_done") or 0
    new = data[done:]
    if not new:
        return {"new": 0}
    sections = []
    for col, question in enumerate(header[1:], start=1):
        answers = "\n".join(f"- {r[col]}" for r in new if len(r) > col and r[col].strip())[:6000]
        if answers:
            s = ai.generate(f"Question: {question}\nAnswers:\n{answers}\nSummarise the main themes in 3 bullets.", max_output_tokens=250).text
            sections.append(f"{question}\n{s}")
    notify.email_me(f"Feedback summary: {len(new)} new responses", "\n\n".join(sections))
    state.set("rows_done", len(data))
    return {"new": len(new)}