Sort your Outlook inbox with AI and log it to a sheet

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

api = lambda name, version: build(name, version, credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)

def graph(method, path, **kw):
    r = http.request(method, "https://graph.microsoft.com/v1.0" + path, headers={"Authorization": f"Bearer {credentials.connection('microsoft', 'work')}"}, **kw)
    return r.raise_for_status() or (r.json() if r.content else None)

SHEET_ID = "your-spreadsheet-id"
SCHEMA = {"type": "object", "properties": {"category": {"enum": ["client", "invoice", "sales", "newsletter", "other"]},
          "summary": {"type": "string"}, "simple": {"type": "boolean"}, "reply": {"type": "string"}}}

@on_trigger
def run(event):
    since = state.get("since") or "2026-01-01T00:00:00Z"
    msgs = graph("GET", "/me/mailFolders/inbox/messages", params={"$filter": f"receivedDateTime gt {since}", "$orderby": "receivedDateTime",
                 "$select": "id,subject,from,bodyPreview,receivedDateTime", "$top": 50})["value"]
    report_fetched(len(msgs))
    if not msgs:
        return {"summary": "No new emails"}
    rows = []
    for m in msgs:
        sender = m["from"]["emailAddress"]["address"]
        res = ai.generate(f"From: {sender}\nSubject: {m['subject']}\n{m['bodyPreview']}", system="Categorise this email, summarise it in one line, "
                          "and if it is a simple client question write a short reply.", json_schema=SCHEMA, max_output_tokens=300).json
        graph("PATCH", f"/me/messages/{m['id']}", json={"categories": [res["category"].title()]})
        if res.get("simple") and res.get("reply"):
            graph("POST", f"/me/messages/{m['id']}/createReply", json={"comment": res["reply"]})
        rows.append([m["receivedDateTime"], sender, m["subject"], res["category"], res["summary"]])
    api("sheets", "v4").spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Inbox Log!A:E", valueInputOption="RAW", body={"values": rows}).execute()
    state.set("since", msgs[-1]["receivedDateTime"])
    return {"sorted": len(rows)}