Score your product sheet for missing data every week

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, state, notify

SHEET_ID = "your-spreadsheet-id"
TEAM_ID = "your-linear-team-id"
REQUIRED = ["sku", "name", "price", "weight", "description", "image_url"]

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    rows = sheets.spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Products!A1:Z").execute().get("values", [])
    head, body = rows[0], rows[1:]
    ticketed = set(state.get("ticketed") or [])
    gaps = {}
    for r in body:
        rec = dict(zip(head, r))
        missing = [f for f in REQUIRED if not rec.get(f, "").strip()]
        if missing:
            gaps[rec.get("sku") or rec.get("name", "?")] = missing
    q = "mutation($i: IssueCreateInput!){ issueCreate(input: $i){ success } }"
    for sku, missing in gaps.items():
        if sku not in ticketed:
            http.post("https://api.linear.app/graphql", headers={"Authorization": credentials.secret("LINEAR_API_KEY")},
                      json={"query": q, "variables": {"i": {"teamId": TEAM_ID, "title": f"{sku}: missing {', '.join(missing)}"}}})
    state.set("ticketed", sorted(set(gaps)))
    score = round(100 * (len(body) - len(gaps)) / max(len(body), 1))
    lines = "\n".join(f"{s}: {', '.join(m)}" for s, m in gaps.items())
    notify.email_me(f"Catalog scorecard: {score}% complete", f"{len(gaps)} of {len(body)} products have gaps.\n\n{lines}")
    return {"score": score, "incomplete": len(gaps)}