Log review sentiment in a sheet and open cases for bad ones

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

SHEET_ID = "your-spreadsheet-id"
REVIEWS = "https://reviews.example.com/api/reviews"  # your review source
API = "https://yourcompany.my.salesforce.com/services/data/v61.0"
SCHEMA = {"type": "object", "properties": {"labels": {"type": "array", "items": {"type": "string",
          "enum": ["positive", "neutral", "negative"]}}}, "required": ["labels"]}

@on_trigger
def run(event):
    seen = set(state.get("seen") or [])
    reviews = [r for r in http.get(REVIEWS, params={"days": 2}).json()["reviews"] if r["id"] not in seen][:40]
    report_fetched(len(reviews))
    if not reviews:
        return {"summary": "No new reviews"}
    numbered = "\n".join(f"{i + 1}. {r['rating']}/5 {r['text'][:400]}" for i, r in enumerate(reviews))
    labels = ai.generate("Label the sentiment of each numbered review, in order.\n" + numbered, json_schema=SCHEMA, max_output_tokens=400).json["labels"]
    headers = {"Authorization": f"Bearer {credentials.connection('salesforce', 'work')}"}
    for r, label in zip(reviews, labels):
        if label == "negative":
            http.post(f"{API}/sobjects/Case", headers=headers, json={"Subject": f"Negative review from {r['author']}",
                      "Description": r["text"], "Origin": "Web", "Priority": "High"}).raise_for_status()
    rows = [[r["date"], r["author"], r["rating"], label, r["text"]] for r, label in zip(reviews, labels)]
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    sheets.values().append(spreadsheetId=SHEET_ID, range="Reviews!A1", valueInputOption="RAW", body={"values": rows}).execute()
    state.set("seen", sorted(seen | {r["id"] for r in reviews})[-2000:])
    return {"logged": len(rows), "cases": labels.count("negative")}