Send your newsletter to everyone in a subscriber sheet

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

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()
    draft = state.get("draft")
    if not draft:
        notes = values.get(spreadsheetId=SHEET_ID, range="Notes!A2:A").execute().get("values", [])
        text = ai.generate("Write a short, warm weekly newsletter from these notes:\n" + "\n".join(r[0] for r in notes if r),
                           max_output_tokens=700).text
        state.set("draft", {"text": text, "approved": False, "sent": []})
        notify.email_me("Newsletter draft for approval", text + "\n\nReply via your approval link to send.")
        return {"status": "awaiting approval"}
    if not draft["approved"]:
        return {"status": "still waiting"}
    emails = [r[0] for r in values.get(spreadsheetId=SHEET_ID, range="Subscribers!A2:A").execute().get("values", []) if r]
    todo = [e for e in emails if e not in draft["sent"]][:500]
    key = credentials.secret("SENDGRID_API_KEY")
    for email in todo:
        http.post("https://api.sendgrid.com/v3/mail/send", headers={"Authorization": f"Bearer {key}"},
                  json={"from": {"email": "news@northwind-bakery.example"}, "subject": "This week at Northwind Bakery",
                        "personalizations": [{"to": [{"email": email}]}], "content": [{"type": "text/plain", "value": draft["text"]}]})
        draft["sent"].append(email)
    state.set("draft", draft if len(draft["sent"]) < len(emails) else None)
    return {"sent": len(todo)}