Add emailed invoices to your Excel register

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

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": {"invoice": {"type": "boolean"}, "supplier": {"type": "string"}, "number": {"type": "string"},
          "date": {"type": "string"}, "due": {"type": "string"}, "total": {"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"]
    report_fetched(len(msgs))
    if not msgs:
        return {"summary": "No new attachments"}
    added = 0
    for m in msgs:
        for a in graph("GET", f"/me/messages/{m['id']}/attachments")["value"]:
            if a.get("contentType") != "application/pdf":
                continue
            inv = ai.generate(pdf_text(a["contentBytes"]), system="Is this an invoice? Extract supplier, number, date, due date (YYYY-MM-DD) and total.",
                              json_schema=SCHEMA, max_output_tokens=120).json
            if inv["invoice"]:
                graph("POST", f"{BOOK}/tables/Invoices/rows/add", json={"values": [[inv["supplier"], inv["number"], inv["date"], inv["due"], inv["total"]]]})
                name = f"{inv['supplier']}_{inv['number']}_{inv['date']}.pdf".replace(" ", "_").replace("/", "-")
                http.put(f"https://graph.microsoft.com/v1.0/me/drive/root:/Invoices/{name}:/content", data=base64.b64decode(a["contentBytes"]),
                         headers={"Authorization": f"Bearer {credentials.connection('microsoft', 'work')}"}).raise_for_status()
                added += 1
        graph("PATCH", f"/me/messages/{m['id']}", json={"isRead": True})
    state.set("since", msgs[-1]["receivedDateTime"])
    return {"invoices": added}