Turn invoices dropped in a Drive folder into expense rows

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

FOLDER_ID, SHEET_ID = "your-invoices-folder-id", "your-spreadsheet-id"
SCHEMA = {"type": "object", "properties": {k: {"type": "string"} for k in ("supplier", "date", "total", "vat")}}

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    drive = build("drive", "v3", credentials=creds, cache_discovery=False)
    since = state.get("since") or "2026-01-01T00:00:00"
    q = f"'{FOLDER_ID}' in parents and createdTime > '{since}' and not properties has {{key='logged' and value='yes'}}"
    files = drive.files().list(q=q, orderBy="createdTime", fields="files(id,name,createdTime,webViewLink)").execute().get("files", [])
    report_fetched(len(files))
    if not files:
        return {"logged": 0}
    rows = []
    for f in files:
        text = drive.files().export(fileId=drive.files().copy(fileId=f["id"], body={"mimeType": "application/vnd.google-apps.document"}).execute()["id"],
                                    mimeType="text/plain").execute().decode()[:6000]
        x = ai.generate(f"Extract supplier, date (YYYY-MM-DD), total and VAT from this invoice:\n{text}", json_schema=SCHEMA, max_output_tokens=120).json
        rows.append([x.get("supplier"), x.get("date"), x.get("total"), x.get("vat"), f["webViewLink"]])
        drive.files().update(fileId=f["id"], body={"properties": {"logged": "yes"}}).execute()
    build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values().append(
        spreadsheetId=SHEET_ID, range="Expenses!A:E", valueInputOption="USER_ENTERED", body={"values": rows}).execute()
    state.set("since", files[-1]["createdTime"][:19])
    return {"logged": len(rows)}