Log weekly Shopify sales to 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, ai

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

@on_trigger
def run(event):
    end = date.today() - timedelta(days=date.today().weekday())
    q = f"created_at:>={end - timedelta(days=7)} created_at:<{end} -status:cancelled"
    orders = gql("""query($q: String!) { orders(first: 250, query: $q) { nodes { totalPriceSet { shopMoney { amount } }
        lineItems(first: 50) { nodes { title quantity } } } } }""", {"q": q})["orders"]["nodes"]
    revenue = sum(float(o["totalPriceSet"]["shopMoney"]["amount"]) for o in orders)
    top = Counter({li["title"]: 0 for o in orders for li in o["lineItems"]["nodes"]})
    for o in orders:
        top.update({li["title"]: li["quantity"] for li in o["lineItems"]["nodes"]})
    best = top.most_common(1)[0][0] if top else ""
    note = ""
    try:
        note = ai.generate(f"{len(orders)} orders, {revenue:.0f} revenue, best seller {best}", system="One short sentence for a sales log.", max_output_tokens=50).text
    except (ai.AINotIncluded, ai.AILimitReached):
        pass
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    sheets.spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Weekly sales!A:F", valueInputOption="USER_ENTERED",
        body={"values": [[str(end - timedelta(days=7)), len(orders), round(revenue, 2), round(revenue / max(len(orders), 1), 2), best, note]]}).execute()
    return {"orders": len(orders)}