Check purchase requests against your budget sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, ai, notify, report_fetched
BASE_ID = "appXXXXXXXXXXXXXX"
SHEET_ID = "1AbCdEfGhIjKlMnOpQrStUvWxYz"
@on_trigger
def run(event):
    api = f"https://api.airtable.com/v0/{BASE_ID}/Purchase%20requests"
    headers = {"Authorization": f"Bearer {credentials.connection('airtable', 'work')}"}
    records = http.get(api, headers=headers, params={"filterByFormula": "{Status}=''"}).json()["records"]
    report_fetched(len(records))
    if not records:
        return {"checked": 0}
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    rows = sheets.spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Budget!A2:B").execute().get("values", [])
    left = {r[0]: float(r[1]) for r in rows if len(r) == 2}
    for rec in records:
        f = rec["fields"]
        amount, remaining = float(f.get("Amount") or 0), left.get(f.get("Department"), 0.0)
        if amount <= remaining * 0.2:
            status, note = "Approved", "Within budget"
        elif amount > remaining:
            status, note = "Rejected", f"Over budget: {remaining:.0f} left"
        else:
            status = "Needs approval"
            note = ai.generate(f"Request: {f.get('Item')} for {amount:.0f}. Budget left: {remaining:.0f}.\nReason: {f.get('Justification', '')[:1000]}",
                               system="Suggest approve or decline in one sentence.", max_output_tokens=80).text
            notify.slack(f"Needs approval: {f.get('Item')} ({f.get('Department')}) {amount:.0f} of {remaining:.0f} left. {note}", channel="#finance")
        http.patch(api, headers=headers, json={"records": [{"id": rec["id"], "fields": {"Status": status, "Decision note": note}}]}).raise_for_status()
    return {"checked": len(records)}