Find prospects' emails and draft a first message in your sheet

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

SHEET_ID = "your-spreadsheet-id"
OFFER = "We help small online shops cut delivery costs with smarter packaging."

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    rows = sheets.spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Prospects!A2:F").execute().get("values", [])
    new = [(i + 2, r) for i, r in enumerate(rows) if len(r) >= 3 and len(r) < 4]
    report_fetched(len(new))
    if not new:
        return {"new": 0}
    updates = []
    for n, (first, last, domain, *_) in new:
        found = http.get("https://api.hunter.io/v2/email-finder", params={"domain": domain, "first_name": first,
                         "last_name": last, "api_key": credentials.secret("HUNTER_API_KEY")}).json().get("data") or {}
        email, score, draft = found.get("email") or "Not found", found.get("score", ""), ""
        if found.get("email"):
            draft = ai.generate(f"Write a 70-word first email to {first} {last} ({found.get('position') or 'unknown role'}) at "
                                f"{domain}. Offer: {OFFER} Friendly, specific, one question at the end.", max_output_tokens=200).text
        updates.append({"range": f"Prospects!D{n}:F{n}", "values": [[email, score, draft.strip()]]})
    sheets.spreadsheets().values().batchUpdate(spreadsheetId=SHEET_ID,
                                               body={"valueInputOption": "RAW", "data": updates}).execute()
    return {"processed": len(new)}