Create a task with an explanation when a sheet row is marked failed

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

SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    sheets = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets()
    values = sheets.values().get(spreadsheetId=SHEET_ID, range="Orders!A1:H").execute().get("values", [])
    header, rows = values[0], values[1:]
    handled = set(state.get("handled") or [])
    failed = [dict(zip(header, r)) for r in rows if dict(zip(header, r)).get("Status") == "Failed"]
    new = [r for r in failed if r.get("Order ID") not in handled]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    tasks = build("tasks", "v1", credentials=creds, cache_discovery=False).tasks()
    lines = []
    for r in new:
        why = ai.generate("This order failed. In two short sentences, give the most likely cause and a first step to fix it.\n"
                          + "\n".join(f"{k}: {v}" for k, v in r.items()), max_output_tokens=120).text.strip()
        tasks.insert(tasklist="@default", body={"title": f"Fix failed order {r.get('Order ID')}", "notes": why}).execute()
        lines.append(f"{r.get('Order ID')}: {why}")
        handled.add(r.get("Order ID"))
    notify.email_me(f"{len(new)} failed orders need attention", "\n\n".join(lines))
    state.set("handled", sorted(handled)[-2000:])
    return {"tasks": len(new)}