Reply to Google reviews and log each one in a sheet

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

LOCATION = "accounts/1234567890/locations/9876543210"
SHEET_ID = "your-spreadsheet-id"
SCHEMA = {"type": "object", "properties": {"reply": {"type": "string"}, "topic": {"type": "string"}}}

@on_trigger
def run(event):
    auth = {"Authorization": f"Bearer {credentials.connection('google', 'work')}"}
    reviews = http.get(f"https://mybusiness.googleapis.com/v4/{LOCATION}/reviews", headers=auth).json().get("reviews", [])
    new = [r for r in reviews if r["createTime"] > (state.get("last") or "") and "reviewReply" not in r]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    rows = []
    for r in new:
        stars, text = ["ONE", "TWO", "THREE", "FOUR", "FIVE"].index(r["starRating"]) + 1, r.get("comment", "")
        out = ai.generate(f"Write a short, warm reply from Northwind Bakery to this {stars}-star review, and give a one-word "
                          f"topic.\n{text[:1500]}", json_schema=SCHEMA, max_output_tokens=200).json
        if stars >= 4:
            http.put(f"https://mybusiness.googleapis.com/v4/{r['name']}/reply", headers=auth, json={"comment": out["reply"]})
        else:
            notify.slack(f"{stars}★ from {r['reviewer']['displayName']}: {text}\nDraft reply: {out['reply']}", channel="#reviews")
        rows.append([r["createTime"][:10], r["reviewer"]["displayName"], stars, text, out["topic"], out["reply"]])
    build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False) \
        .spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Reviews!A:F", valueInputOption="RAW", body={"values": rows}).execute()
    state.set("last", max(r["createTime"] for r in new))
    return {"new": len(new)}