Add products to WooCommerce from a sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, report_fetched, credentials, http, ai

SHEET_ID = "your-spreadsheet-id"

STORE = "https://yourstore.com/wp-json/wc/v3"

def wc(method, path, **kw):
    resp = http.request(method, STORE + path, auth=(credentials.secret("WC_CONSUMER_KEY"), credentials.secret("WC_CONSUMER_SECRET")), **kw)
    resp.raise_for_status()
    return resp.json()

def sheets():
    return build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()

@on_trigger
def run(event):
    rows = sheets().get(spreadsheetId=SHEET_ID, range="Products!A2:F").execute().get("values", [])
    rows = [(n, r + [""] * (6 - len(r))) for n, r in enumerate(rows, start=2) if r and r[0]]
    new = [(n, r) for n, r in rows if not r[5]]
    report_fetched(len(new))
    for n, (name, sku, price, stock, notes, _) in new:
        desc = ai.generate(f"Product: {name}\nNotes: {notes}", max_output_tokens=300,
                           system="Write a friendly 80-word product description in HTML paragraphs.").text
        p = wc("POST", "/products", json={"name": name, "sku": sku, "regular_price": price, "description": desc, "status": "draft",
                                          "manage_stock": True, "stock_quantity": int(stock or 0)})
        sheets().update(spreadsheetId=SHEET_ID, range=f"Products!F{n}", valueInputOption="RAW", body={"values": [[p["id"]]]}).execute()
    existing = [{"id": int(r[5]), "stock_quantity": int(r[3] or 0)} for _, r in rows if r[5]]
    if existing:
        wc("POST", "/products/batch", json={"update": existing[:100]})
    return {"created": len(new), "stock_synced": len(existing)}