Get daily content ideas from search trends in a sheet

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

SHEET_ID = "your-spreadsheet-id"
NICHE = "specialty coffee"

@on_trigger
def run(event):
    key = credentials.secret("SERPAPI_API_KEY")
    news = http.get("https://serpapi.com/search", params={"engine": "google_news", "q": NICHE, "api_key": key}).json().get("news_results", [])[:10]
    trends = http.get("https://serpapi.com/search", params={"engine": "google_trends", "q": NICHE, "data_type": "RELATED_QUERIES", "api_key": key}).json()
    rising = [q["query"] for q in trends.get("related_queries", {}).get("rising", [])[:10]]
    recent = state.get("recent") or []
    schema = {"type": "object", "properties": {"ideas": {"type": "array", "items": {"type": "object",
              "properties": {"idea": {"type": "string"}, "why": {"type": "string"}}, "required": ["idea", "why"]}}}, "required": ["ideas"]}
    prompt = f"Niche: {NICHE}\nHeadlines:\n" + "\n".join(n["title"] for n in news) + f"\nRising searches: {', '.join(rising)}\nAvoid: {'; '.join(recent)}\nSuggest 5 post ideas."
    ideas = ai.generate(prompt, json_schema=schema, max_output_tokens=500).json["ideas"][:5]
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    sheets.spreadsheets().values().append(spreadsheetId=SHEET_ID, range="Content Ideas!A:C", valueInputOption="RAW",
                                          body={"values": [[date.today().isoformat(), i["idea"], i["why"]] for i in ideas]}).execute()
    state.set("recent", (recent + [i["idea"] for i in ideas])[-70:])
    return {"ideas": len(ideas)}