Plan reorders from Shopify sales in a sheet

from datetime import date, timedelta
from collections import Counter
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify

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, COVER_DAYS = "your-spreadsheet-id", 45

@on_trigger
def run(event):
    orders = gql("""query($q: String!) { orders(first: 250, query: $q) { nodes { lineItems(first: 50) { nodes { quantity variant { id } } } } } }""",
                 {"q": f"created_at:>={date.today() - timedelta(days=30)}"})["orders"]["nodes"]
    sold = Counter()
    for o in orders:
        sold.update({li["variant"]["id"]: li["quantity"] for li in o["lineItems"]["nodes"] if li["variant"]})
    rows, urgent = [], []
    for v in gql("{ productVariants(first: 250) { nodes { id sku displayName inventoryQuantity } } }")["productVariants"]["nodes"]:
        rate = sold[v["id"]] / 30
        days = round(v["inventoryQuantity"] / rate) if rate else ""
        reorder = max(0, round(rate * COVER_DAYS - v["inventoryQuantity"]))
        rows.append([v["sku"], v["displayName"], v["inventoryQuantity"], round(rate, 2), days, reorder])
        if days != "" and days < 14:
            urgent.append(f"{v['displayName']}: {days} days left, reorder about {reorder}")
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    sheets.spreadsheets().values().update(spreadsheetId=SHEET_ID, range="Reorders!A2", valueInputOption="USER_ENTERED", body={"values": rows}).execute()
    if urgent:
        notify.email_me(f"{len(urgent)} products to reorder", "\n".join(urgent))
    return {"variants": len(rows), "urgent": len(urgent)}