Collect Reddit questions for SEO and content research

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from datetime import date
from clickruntime import on_trigger, credentials, http, ai

SUBS, SHEET_ID, RANGE = ["gardening", "vegetablegardening"], "your-spreadsheet-id", "Research!A:E"
SCHEMA = {"type": "object", "properties": {"items": {"type": "array", "items": {"type": "object",
          "properties": {"n": {"type": "integer"}, "title": {"type": "string"}, "outline": {"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):
    posts = [p for s in SUBS for p in reddit(f"/r/{s}/top", t="week", limit=40)]
    listing = "\n".join(f"{i}. {p['title']} | {p['selftext'][:200]}" for i, p in enumerate(posts))
    items = ai.generate(listing, json_schema=SCHEMA, max_output_tokens=1500,
                        system="Keep only posts asking a question. For each, suggest an article title and a 3-5 heading outline.").json["items"]
    rows = [[date.today().isoformat(), posts[i["n"]]["title"], i["title"], i["outline"], "https://reddit.com" + posts[i["n"]]["permalink"]]
            for i in items if 0 <= i["n"] < len(posts)]
    if rows:
        append_rows(rows)
    return {"questions": len(rows)}