Send personal emails to a lead sheet and follow up on no-replies

import base64
from datetime import date, timedelta
from email.message import EmailMessage
from clickruntime import on_trigger, credentials, http
SHEET, G = "your-spreadsheet-id", "https://gmail.googleapis.com/gmail/v1/users/me"
FIRST = "Hi {name},\n\nGreat to meet you at the Northwind expo. Would a 15-minute call next week be useful?"
FOLLOW = "Hi {name}, just bringing this back to the top of your inbox."
@on_trigger
def run(event):
    h = {"Authorization": f"Bearer {credentials.connection('google', 'work')}"}
    rows = http.get(f"https://sheets.googleapis.com/v4/spreadsheets/{SHEET}/values/Leads!A2:E", headers=h).json().get("values", [])
    updates, sent, today = [], 0, date.today()
    for i, r in enumerate(rows, start=2):
        name, email, status, sent_on, thread = (r + [""] * 5)[:5]
        if status == "sent" and sent_on <= str(today - timedelta(days=3)):
            replied = len(http.get(f"{G}/threads/{thread}", headers=h).json()["messages"]) > 1
            status = "replied" if replied else "follow-up"
            body = None if replied else FOLLOW
        elif not status and sent < 30:
            status, body = "sent", FIRST
        else: continue
        if body:
            m = EmailMessage(); m["To"] = email; m["Subject"] = "Quick follow-up from the expo"; m.set_content(body.format(name=name))
            res = http.post(f"{G}/messages/send", headers=h, json={"raw": base64.urlsafe_b64encode(m.as_bytes()).decode(),
                                                                    **({"threadId": thread} if thread else {})}).json()
            thread, sent = res["threadId"], sent + 1
        updates.append({"range": f"Leads!C{i}:E{i}", "values": [[status, str(today), thread]]})
    if updates: http.post(f"https://sheets.googleapis.com/v4/spreadsheets/{SHEET}/values:batchUpdate", headers=h,
                  json={"valueInputOption": "RAW", "data": updates})
    return {"sent": sent}