Score new applicants in a sheet and add them to Notion

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

SHEET_ID, HIRING_DB = "your-spreadsheet-id", "your-notion-database-id"
ROLE = "Customer support lead: 3+ years in B2B support, writes clearly, has managed a small team."
SCHEMA = {"type": "object", "properties": {"score": {"type": "integer"}, "reason": {"type": "string"}}, "required": ["score", "reason"]}

@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="Applicants!A2:E").execute().get("values", [])
    new = [(i + 2, r) for i, r in enumerate(rows) if len(r) >= 3 and (len(r) < 5 or not r[4])]
    report_fetched(len(new))
    if not new:
        return {"summary": "Nothing new"}
    notion = {"Authorization": f"Bearer {credentials.connection('notion', 'work')}", "Notion-Version": "2022-06-28"}
    for row, r in new:
        name, email, notes = r[0], r[1], r[2] + (" " + r[3] if len(r) > 3 else "")
        s = ai.generate(f"Role: {ROLE}\nCandidate notes: {notes[:3000]}", system="Score fit 1-10 with a one-line reason.", json_schema=SCHEMA, max_output_tokens=80).json
        page = http.post("https://api.notion.com/v1/pages", headers=notion, json={"parent": {"database_id": HIRING_DB}, "properties": {
            "Name": {"title": [{"text": {"content": name}}]}, "Email": {"email": email}, "Score": {"number": s["score"]},
            "Reason": {"rich_text": [{"text": {"content": s["reason"]}}]}}}).json()
        sheets.values().update(spreadsheetId=SHEET_ID, range=f"Applicants!E{row}", valueInputOption="RAW", body={"values": [[page["url"]]]}).execute()
    return {"added": len(new)}