Log new emails in a sheet and turn action items into tasks

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

SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    gmail = build("gmail", "v1", credentials=creds, cache_discovery=False).users().messages()
    done = set(state.get("done") or [])
    new = [m for m in gmail.list(userId="me", q="in:inbox newer_than:1d", maxResults=50).execute().get("messages", [])
           if m["id"] not in done]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    rows = []
    for m in new:
        msg = gmail.get(userId="me", id=m["id"], format="metadata", metadataHeaders=["From", "Subject", "Date"]).execute()
        h = {x["name"]: x["value"] for x in msg["payload"]["headers"]}
        cat = ai.generate("Pick one of Action needed, Waiting, FYI, Newsletter for this email. Reply with the category only."
                          f"\nFrom: {h.get('From')}\nSubject: {h.get('Subject')}\n{msg['snippet']}", max_output_tokens=10).text.strip()
        rows.append([h.get("Date", ""), h.get("From", ""), h.get("Subject", ""), cat])
        if cat == "Action needed":
            build("tasks", "v1", credentials=creds, cache_discovery=False).tasks().insert(tasklist="@default", body={
                "title": h.get("Subject") or "Email to action", "notes": f"https://mail.google.com/mail/u/0/#all/{m['id']}"}).execute()
    build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values().append(
        spreadsheetId=SHEET_ID, range="Email log!A:D", valueInputOption="USER_ENTERED", body={"values": rows}).execute()
    state.set("done", sorted(done | {m["id"] for m in new})[-1000:])
    return {"logged": len(rows)}