Weekly personalized newsletter from a Google Sheet

Every Monday at 8am, send a short personalized newsletter to the subscribers in my Google Sheet through Amazon SES. This runs as an agent because each email is drafted per recipient using their segment and interest columns, and the send decision depends on both the sheet and the SES suppression list.

Start by reading the subscriber list from Google Sheets with Get Values. The sheet has columns for email, first_name, an unsubscribed flag (TRUE/FALSE or blank), and one or more segment/interest columns (for example segment, interests, company_size). Ignore any row where unsubscribed is TRUE or where email is empty. Ask me for the spreadsheet ID, the tab name, and this week's topic at run start if they are not already provided.

Before sending anything, pull the current Amazon SES suppression list once with List Suppressed Destinations and hold it in memory. Do not call it once per recipient. For each remaining subscriber, skip them if their email is on the suppression list (past bounce or complaint).

For every recipient who survives both filters, draft a short newsletter of roughly 120 to 200 words that riffs on this week's topic and references the recipient's segment/interest columns where it fits naturally. Use the first name once, keep the tone warm and specific, and end with a single soft call to action. Do not invent personal details that are not in the row.

Send each drafted email with Amazon SES Send Email. Put the recipient's first name in the subject line (for example, "Alex, this week on <topic>"). Pace the sends to respect the SES sandbox rate limit of one message per second by default: wait about a second between calls. If Amazon SES returns an error for a specific recipient (rejected, throttled, or anything else), record the address and the error and keep going rather than failing the whole run.

When every recipient has been handled, append one summary row to a Run Log tab in the same spreadsheet using Append Values. The row should contain: run timestamp in UTC, this week's topic, count sent, count skipped for unsubscribed, count skipped for SES suppression, count of send errors, and a short comma-separated list of any failed email addresses. If the Run Log tab does not exist yet, tell me so I can create it.

Guardrails: never persist recipient email content anywhere outside Amazon SES. The drafts live only inside the Send Email call and must not be written back to the Sheet, logged, or included in the summary row. Never send to a row where unsubscribed is TRUE, and never send to an address on the SES suppression list. If the sheet is empty or unreadable, append a summary row noting the failure and exit without sending anything.