Write product descriptions in a sheet and push approved ones to Shopify

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, ai, 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 = "your-spreadsheet-id"
SCHEMA = {"type": "object", "properties": {"en": {"type": "string"}, "fr": {"type": "string"}, "de": {"type": "string"}}}

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()
    rows = sheets.get(spreadsheetId=SHEET_ID, range="Descriptions!A2:F").execute().get("values", [])
    todo = [(i, r + [""] * (6 - len(r))) for i, r in enumerate(rows, start=2) if len(r) < 6 or r[5] in ("", "Approved")]
    todo = [(i, r) for i, r in todo if r[1] and (not r[2] or r[5] == "Approved")]
    report_fetched(len(todo))
    for i, (handle, notes, en, fr, de, status) in todo:
        if not en:
            d = ai.generate(f"Product notes: {notes[:1500]}", json_schema=SCHEMA, max_output_tokens=1200,
                            system="Write an 80-word product description in English, then French and German translations.").json
            sheets.update(spreadsheetId=SHEET_ID, range=f"Descriptions!C{i}:E{i}", valueInputOption="RAW", body={"values": [[d["en"], d["fr"], d["de"]]]}).execute()
        elif status == "Approved":
            pid = gql(f'{{ productByHandle(handle: "{handle}") {{ id }} }}')["productByHandle"]["id"]
            gql("mutation($p: ProductUpdateInput!) { productUpdate(product: $p) { userErrors { message } } }", {"p": {"id": pid, "descriptionHtml": f"<p>{en}</p>"}})
            sheets.update(spreadsheetId=SHEET_ID, range=f"Descriptions!F{i}", valueInputOption="RAW", body={"values": [["Published"]]}).execute()
    return {"rows": len(todo)}