Turn feedback in a sheet into a Google Doc report

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

SHEET_ID = "your-spreadsheet-id"

@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="Feedback!A2:C").execute().get("values", [])
    done = state.get("rows_done") or 0
    new = [r[-1] for r in rows[done:] if r]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    try:
        text = ai.generate("Feedback comments:\n" + "\n".join(f"- {c[:400]}" for c in new[:300]),
                           system="Write a feedback report: summary, 4-6 themes with sentiment and 2 quotes each, then suggested actions. Plain text.",
                           max_output_tokens=1500).text
    except ai.AIError as e:
        notify.email_me("Feedback report not written", f"{len(new)} new rows; AI was unavailable ({e}).")
        return {"new": len(new), "report": False}
    docs = build("docs", "v1", credentials=creds, cache_discovery=False)
    doc = docs.documents().create(body={"title": f"Feedback report {date.today()}"}).execute()
    docs.documents().batchUpdate(documentId=doc["documentId"], body={"requests": [{"insertText": {"location": {"index": 1}, "text": text}}]}).execute()
    state.set("rows_done", len(rows))
    url = f"https://docs.google.com/document/d/{doc['documentId']}/edit"
    notify.email_me(f"Feedback report: {len(new)} new responses", url)
    return {"new": len(new), "doc": doc["documentId"]}