Draft blog posts from products, tracked in a sheet

from datetime import date
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, BLOG_ID = "your-spreadsheet-id", "gid://shopify/Blog/123456789"
SCHEMA = {"type": "object", "properties": {"title": {"type": "string"}, "html": {"type": "string"}}}

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    done = {r[1] for r in sheets.spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Content!A2:B").execute().get("values", []) if len(r) > 1}
    products = gql('{ products(first: 100, query: "status:active") { nodes { title description } } }')["products"]["nodes"]
    product = next((p for p in products if p["title"] not in done), None)
    if not product:
        return {"written": None}
    post = ai.generate(f"Product: {product['title']}\n{product['description'][:2000]}", json_schema=SCHEMA, max_output_tokens=1500,
                       system="Write a helpful 600-word blog post about this product. Return a title and an HTML body.").json
    article = gql("""mutation($a: ArticleCreateInput!) { articleCreate(article: $a) { article { id } } }""",
                  {"a": {"blogId": BLOG_ID, "title": post["title"], "body": post["html"], "isPublished": False, "author": {"name": "Store team"}}})
    article_id = article["articleCreate"]["article"]["id"].split("/")[-1]
    sheets.spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Content!A:D", valueInputOption="USER_ENTERED",
        body={"values": [[str(date.today()), product["title"], post["title"], f"https://admin.shopify.com/store/your-store/content/articles/{article_id}"]]}).execute()
    return {"written": product["title"]}