Send a welcome email to each new row in a sheet

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

SHEET_ID = "your-spreadsheet-id"

@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="Signups!A2:C").execute().get("values", [])
    new = [(i + 2, r) for i, r in enumerate(rows) if len(r) >= 2 and r[1] and len(r) < 3]
    report_fetched(len(new))
    if not new:
        return {"summary": "No new rows"}
    key = credentials.secret("SENDGRID_API_KEY")
    for row_num, r in new:
        http.post("https://api.sendgrid.com/v3/mail/send", headers={"Authorization": f"Bearer {key}"},
                  json={"from": {"email": "hello@northwind.example"}, "template_id": "d-welcome",
                        "personalizations": [{"to": [{"email": r[1]}], "dynamic_template_data": {"first_name": r[0]}}]})
        values.update(spreadsheetId=SHEET_ID, range=f"Signups!C{row_num}", valueInputOption="RAW",
                      body={"values": [[date.today().isoformat()]]}).execute()
    return {"sent": len(new)}