Research each new lead in your sheet

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

SHEET_ID = "your-spreadsheet-id"

def sonar(prompt):
    r = http.post("https://api.perplexity.ai/chat/completions", headers={"Authorization": f"Bearer {credentials.secret('PERPLEXITY_API_KEY')}"},
                  json={"model": "sonar", "messages": [{"role": "user", "content": prompt}]}, timeout=90).json()
    return r["choices"][0]["message"]["content"], [s["url"] for s in r.get("search_results", [])]

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")),
                   cache_discovery=False).spreadsheets().values()
    rows = sheets.get(spreadsheetId=SHEET_ID, range="Leads!A2:F").execute().get("values", [])
    new = [(i + 2, r) for i, r in enumerate(rows) if len(r) == 3]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    updates = []
    for n, (name, company, title) in new:
        research, links = sonar(f"Research {name}, {title} at {company}: what the company does, recent news, and their role.")
        out = ai.generate(f"From this research write a three-line summary, then on a new line starting 'Opener:' one personal "
                          f"opening sentence for an email to {name}.\n{research[:3000]}", max_output_tokens=250).text
        summary, _, opener = out.partition("Opener:")
        updates.append({"range": f"Leads!D{n}:F{n}", "values": [[summary.strip(), opener.strip(), "\n".join(links[:5])]]})
    sheets.batchUpdate(spreadsheetId=SHEET_ID, body={"valueInputOption": "RAW", "data": updates}).execute()
    notify.email_me(f"{len(new)} leads researched", "\n".join(f"{r[0]} ({r[1]})" for _, r in new))
    return {"researched": len(new)}