Send a personalised email sequence to leads in a sheet

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

SHEET_ID = "your-spreadsheet-id"
STEPS = [(0, "Quick idea for {company}"), (3, "Following up"), (7, "Last note from me")]

@on_trigger
def run(event):
    values = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()
    rows = values.get(spreadsheetId=SHEET_ID, range="Leads!A2:F").execute().get("values", [])
    key, sent = credentials.secret("SENDGRID_API_KEY"), 0
    for i, r in enumerate(rows):
        name, email, company, note, step, last = (r + [""] * 6)[:6]
        step = int(step or 0)
        if step >= len(STEPS) or note == "replied" or (last and date.fromisoformat(last) + timedelta(days=STEPS[step][0]) > date.today()) or sent >= 50:
            continue
        opener = ai.generate(f"One friendly opening line to {name} at {company}. Context: {note}", max_output_tokens=60).text.strip()
        http.post("https://api.sendgrid.com/v3/mail/send", headers={"Authorization": f"Bearer {key}"},
                  json={"from": {"email": "hello@acme.example"}, "subject": STEPS[step][1].format(company=company),
                        "personalizations": [{"to": [{"email": email}]}], "content": [{"type": "text/plain", "value": opener}]})
        values.update(spreadsheetId=SHEET_ID, range=f"Leads!E{i + 2}:F{i + 2}", valueInputOption="RAW",
                      body={"values": [[step + 1, date.today().isoformat()]]}).execute()
        sent += 1
    return {"sent": sent}