Log Shopify refunds to Google Sheets

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
import base64, hashlib, hmac
from clickruntime import on_event, credentials, http, state

STORE = "your-store.myshopify.com"
SHEET_ID = "your-spreadsheet-id"

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")})
    resp.raise_for_status()
    return resp.json()["data"]

@on_event
def handle(event):
    digest = base64.b64encode(hmac.new(credentials.secret("SHOPIFY_WEBHOOK_SECRET").encode(), event.body, hashlib.sha256).digest()).decode()
    if not hmac.compare_digest(digest, event.header("X-Shopify-Hmac-Sha256") or ""):
        return {"ignored": "bad signature"}
    r = event.json
    if state.get(f"refund:{r['id']}"):
        return {"duplicate": True}
    order = gql("query($id: ID!) { order(id: $id) { name } }", {"id": f"gid://shopify/Order/{r['order_id']}"})["order"]
    items = ", ".join(f"{li['line_item']['title']} × {li['quantity']}" for li in r.get("refund_line_items", []))
    paid = [t for t in r.get("transactions", []) if t.get("kind") == "refund" and t.get("status") == "success"]
    amount = sum(float(t["amount"]) for t in paid)
    currency = paid[0]["currency"] if paid else ""
    restocked = "restocked" if any(li.get("restock_type") in ("return", "cancel") for li in r.get("refund_line_items", [])) else "not restocked"
    row = [r["created_at"][:10], order["name"], items or "(amount only)", amount, currency, restocked, r.get("note") or ""]
    build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False) \
        .spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Refunds!A:G", valueInputOption="USER_ENTERED",
                                        body={"values": [row]}).execute()
    state.set(f"refund:{r['id']}", True)
    return {"logged": order["name"]}