List low-stock items from Supabase in a purchasing sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify

SHEET_ID = "your-spreadsheet-id"
SUPABASE = "https://your-project.supabase.co/rest/v1"

def sb(method, path, **kw):
    key = credentials.secret("SUPABASE_KEY")
    r = http.request(method, f"{SUPABASE}/{path}", **kw, headers={"apikey": key, "Authorization": f"Bearer {key}", "Prefer": "return=representation,resolution=merge-duplicates"})
    return r.raise_for_status() or (r.json() if r.content else None)

@on_trigger
def run(event):
    items = sb("GET", "inventory?active=is.true&select=sku,name,quantity,reorder_level,target_level")
    low = [i for i in items if i["quantity"] < i["reorder_level"]]
    rows = [["SKU", "Item", "In stock", "Reorder level", "Suggested order", "Approved?"]]
    rows += [[i["sku"], i["name"], i["quantity"], i["reorder_level"], (i.get("target_level") or 2 * i["reorder_level"]) - i["quantity"], ""] for i in low]
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    sheets.values().clear(spreadsheetId=SHEET_ID, range="Purchasing!A:F").execute()
    sheets.values().update(spreadsheetId=SHEET_ID, range="Purchasing!A1", valueInputOption="RAW", body={"values": rows}).execute()
    if low:
        notify.email_me(f"{len(low)} items to reorder – please review", "\n".join(f"{r[1]}: {r[2]} in stock, order {r[4]}" for r in rows[1:])
                        + f"\n\nApprove in the sheet: https://docs.google.com/spreadsheets/d/{SHEET_ID}")
    return {"low": len(low)}