Check emailed invoices against your purchase order sheet

import base64
import io
from pypdf import PdfReader
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, report_fetched, http, credentials, ai, state, notify

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)

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

SHEET_ID = "your-spreadsheet-id"
SCHEMA = {"type": "object", "properties": {"invoice": {"type": "boolean"}, "po": {"type": "string"}, "supplier": {"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"}
    sheet = api("sheets", "v4").spreadsheets().values()
    pos = {r[0]: (n, r) for n, r in enumerate(sheet.get(spreadsheetId=SHEET_ID, range="Purchase Orders!A2:D").execute().get("values", []), start=2) if r}
    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 PO number, supplier and total.", json_schema=SCHEMA, max_output_tokens=100).json
            n, row = pos.get(inv.get("po", ""), (None, None))
            if inv["invoice"] and row and abs(float(row[2]) - inv["total"]) < 0.01:
                sheet.update(spreadsheetId=SHEET_ID, range=f"Purchase Orders!D{n}", valueInputOption="RAW", body={"values": [["invoiced"]]}).execute()
            elif inv["invoice"]:
                notify.email_me(f"Invoice mismatch: {inv.get('supplier')}", f"PO {inv.get('po') or 'missing'}: invoice {inv['total']}, PO {row[2] if row else 'not found'}")
    state.set("since", msgs[-1]["receivedDateTime"])
    return {"checked": len(msgs)}