Fill in company research for every row in a sheet

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

SHEET_ID = "your-spreadsheet-id"
FIELDS = ["what_they_do", "size", "recent_news", "linkedin_url"]

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    rows = sheets.values().get(spreadsheetId=SHEET_ID, range="Prospects!A2:E").execute().get("values", [])
    todo = [(i + 2, r[0]) for i, r in enumerate(rows) if r and r[0] and len(r) < 2][:10]
    report_fetched(len(todo))
    if not todo:
        return {"researched": 0}
    schema = {"type": "object", "properties": {f: {"type": "string"} for f in FIELDS}, "required": FIELDS}
    for row, company in todo:
        res = http.get("https://serpapi.com/search", params={"engine": "google", "q": company, "api_key": credentials.secret("SERPAPI_API_KEY")}).json()
        snippets = "\n".join(f"- {r['title']}: {r.get('snippet', '')} ({r['link']})" for r in res.get("organic_results", [])[:8])
        facts = ai.generate(f"Company: {company}\nResults:\n{snippets}\nFill each field briefly; use '' if unknown.", json_schema=schema, max_output_tokens=250).json
        sheets.values().update(spreadsheetId=SHEET_ID, range=f"Prospects!B{row}:E{row}", valueInputOption="RAW",
                               body={"values": [[facts[f] for f in FIELDS]]}).execute()
    return {"researched": len(todo)}