Turn emailed PDF timesheets into rows in Excel

import base64
import io
from pypdf import PdfReader
from clickruntime import on_trigger, report_fetched, http, credentials, ai, state, notify

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)

pdf_text = lambda b64: " ".join(p.extract_text() or "" for p in PdfReader(io.BytesIO(base64.b64decode(b64))).pages)[:12000]

BOOK = "/me/drive/items/your-workbook-id/workbook"
SCHEMA = {"type": "object", "properties": {"ok": {"type": "boolean"}, "name": {"type": "string"}, "week": {"type": "string"},
          "lines": {"type": "array", "items": {"type": "object", "properties": {"project": {"type": "string"}, "hours": {"type": "number"}}}}}}

@on_trigger
def run(event):
    since = state.get("since") or "2026-01-01T00:00:00Z"
    msgs = graph("GET", "/me/messages", params={"$filter": f"hasAttachments eq true and receivedDateTime gt {since}", "$orderby": "receivedDateTime", "$top": 25})["value"]
    msgs = [m for m in msgs if "timesheet" in m["subject"].lower()]
    report_fetched(len(msgs))
    if not msgs:
        return {"summary": "No new timesheets"}
    for m in msgs:
        for a in graph("GET", f"/me/messages/{m['id']}/attachments")["value"]:
            if a.get("contentType") == "application/pdf":
                ts = ai.generate(pdf_text(a["contentBytes"]), system="Read this timesheet: person's name, week starting (YYYY-MM-DD), hours per project. "
                                 "Set ok=false if unreadable.", json_schema=SCHEMA, max_output_tokens=300).json
                if ts["ok"] and ts["lines"]:
                    graph("POST", f"{BOOK}/tables/Timesheets/rows/add", json={"values": [[ts["name"], ts["week"], l["project"], l["hours"]] for l in ts["lines"]]})
                else:
                    notify.email_me("Timesheet needs checking", f"{m['from']['emailAddress']['address']}: {m['subject']}")
    state.set("since", max(m["receivedDateTime"] for m in msgs))
    return {"timesheets": len(msgs)}