Send personalised emails from a Google Doc template

import base64
from datetime import date
from email.message import EmailMessage
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials

DOC_ID, SHEET_ID = "your-doc-id", "your-spreadsheet-id"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    doc = build("docs", "v1", credentials=creds, cache_discovery=False).documents().get(documentId=DOC_ID).execute()
    text = "".join(r["textRun"]["content"] for c in doc["body"]["content"] for r in c.get("paragraph", {}).get("elements", []) if "textRun" in r)
    values = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets().values()
    head, *rows = values.get(spreadsheetId=SHEET_ID, range="Contacts!A1:Z").execute()["values"]
    gmail, sent, status_col = build("gmail", "v1", credentials=creds, cache_discovery=False), 0, chr(65 + head.index("Status"))
    for i, row in enumerate(rows, start=2):
        rec = dict(zip(head, row + [""] * len(head)))
        if rec["Status"] or not rec.get("Email"):
            continue
        filled = text
        for k, v in rec.items():
            filled = filled.replace("{{" + k + "}}", v)
        subject, _, body = filled.partition("\n")
        msg = EmailMessage()
        msg["To"], msg["Subject"] = rec["Email"], subject.strip()
        msg.set_content(body.strip())
        gmail.users().messages().send(userId="me", body={"raw": base64.urlsafe_b64encode(msg.as_bytes()).decode()}).execute()
        values.update(spreadsheetId=SHEET_ID, range=f"Contacts!{status_col}{i}", valueInputOption="RAW",
                      body={"values": [[f"Sent {date.today().isoformat()}"]]}).execute()
        sent += 1
    return {"sent": sent}