Screen applicants in a sheet and email each one

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"
JOB = "Customer success manager, B2B SaaS, 3+ years, remote in EU."

@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="Applicants!A2:E").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 {"summary": "No new applicants"}
    key = credentials.secret("SENDGRID_API_KEY")
    for row_num, (name, email, answers, *_) in new:
        result = ai.generate(f"Job: {JOB}\nApplicant answers: {answers[:2000]}", max_output_tokens=80,
                             json_schema={"type": "object", "properties": {"score": {"type": "integer"}, "reason": {"type": "string"}}}).json
        values.update(spreadsheetId=SHEET_ID, range=f"Applicants!D{row_num}:E{row_num}", valueInputOption="RAW",
                      body={"values": [[result["score"], result["reason"]]]}).execute()
        template = "d-nextsteps" if result["score"] >= 7 else "d-thankyou"
        http.post("https://api.sendgrid.com/v3/mail/send", headers={"Authorization": f"Bearer {key}"},
                  json={"from": {"email": "jobs@northwind.example"}, "template_id": template,
                        "personalizations": [{"to": [{"email": email}], "dynamic_template_data": {"name": name}}]})
    return {"scored": len(new)}