Find Reddit leads and log them in a sheet

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

QUERY = '"recommend" scheduling software OR "calendly alternative"'
SHEET_ID, RANGE = "your-spreadsheet-id", "Leads!A:E"
SCHEMA = {"type": "object", "required": ["score", "reply"], "properties": {"score": {"type": "integer"}, "reply": {"type": "string"}}}

def reddit(path, **params):
    headers = {"Authorization": "Bearer " + credentials.connection("reddit", "main"), "User-Agent": "clickruntime/1.0"}
    return [c["data"] for c in http.get(f"https://oauth.reddit.com{path}", headers=headers, params=params).json()["data"]["children"]]

def append_rows(rows):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    sheets.spreadsheets().values().append(spreadsheetId=SHEET_ID, range=RANGE, valueInputOption="RAW", body={"values": rows}).execute()

@on_trigger
def run(event):
    seen = set(state.get("seen") or [])
    posts = [p for p in reddit("/search", q=QUERY, sort="new", t="day", limit=25) if p["id"] not in seen]
    report_fetched(len(posts))
    if not posts:
        return {"leads": 0}
    rows = []
    for p in posts:
        r = ai.generate(f"{p['title']}\n{p['selftext'][:1500]}", json_schema=SCHEMA, max_output_tokens=250,
                        system="Our product: simple appointment booking for small teams. Score fit 1-5 and draft a short, helpful, non-salesy reply.").json
        if r["score"] >= 4:
            rows.append([p["created_utc"], p["subreddit"], p["title"], r["score"], r["reply"], "https://reddit.com" + p["permalink"]])
    if rows:
        append_rows(rows)
    state.set("seen", (list(seen) + [p["id"] for p in posts])[-2000:])
    return {"checked": len(posts), "leads": len(rows)}