Log late Shopify orders to a sheet

from datetime import datetime, timedelta, timezone
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify, state, report_fetched

STORE = "your-store.myshopify.com"

def gql(query, variables=None):
    resp = http.post(f"https://{STORE}/admin/api/2025-07/graphql.json", json={"query": query, "variables": variables or {}},
                     headers={"X-Shopify-Access-Token": credentials.connection("shopify", "store")})
    return resp.raise_for_status() or resp.json()["data"]

SHEET_ID, SLA_HOURS = "your-spreadsheet-id", 48

@on_trigger
def run(event):
    now = datetime.now(timezone.utc)
    cutoff = (now - timedelta(hours=SLA_HOURS)).strftime("%Y-%m-%dT%H:%M:%SZ")
    orders = gql(f'{{ orders(first: 100, query: "financial_status:paid fulfillment_status:unfulfilled created_at:<{cutoff}") '
                 '{ nodes { name createdAt customer { displayName } } } }')["orders"]["nodes"]
    logged = set(state.get("logged") or [])
    new = [o for o in orders if o["name"] not in logged]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    rows = [[o["name"], (o["customer"] or {}).get("displayName", ""), o["createdAt"][:16].replace("T", " "),
             round((now - datetime.fromisoformat(o["createdAt"].replace("Z", "+00:00"))).total_seconds() / 3600)] for o in new]
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    sheets.spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Late orders!A:D", valueInputOption="USER_ENTERED", body={"values": rows}).execute()
    notify.slack(f"{len(new)} orders past {SLA_HOURS}h without shipping: " + ", ".join(r[0] for r in rows), channel="#fulfilment")
    state.set("logged", sorted(logged | {o["name"] for o in new})[-1000:])
    return {"new": len(new)}