Google Sheets mail merge with auto status tracking
Every 15 minutes, pull pending rows from your recipient sheet, send each contact a personalized email, and mark the row as Sent.
Google Sheets
Send EmailMarketingSalesEmail AutomationData SyncI want a code workflow that runs every 15 minutes on a cron trigger and works through a Google Sheets mail merge for me.
My recipient sheet has columns like Email, FirstName, and Status, plus any custom merge fields I want to use (Company, City, OfferCode, etc.). The Status column tracks per-row state: blank or "Pending" means not yet sent, "Sent" means delivered, "Error" means something went wrong.
On each run:
1. Use Google Sheets Get Values to read the entire recipient sheet in one call, including the header row.
2. For every data row where the Status column is blank or equals "Pending":
a. Validate the Email column with a basic email-format regex. If it is invalid, use Google Sheets Update Values to set the Status cell for that row to "Error: invalid email" and skip the row.
b. Substitute the row values into a configured HTML body template and subject line template. Use {{ColumnName}} placeholders that resolve to the sheet's header columns (case-insensitive match on the header).
c. Call the Send Email service's Send an Email operation to deliver the rendered HTML message to the row's Email address.
d. On success, use Google Sheets Update Values to write "Sent <ISO timestamp>" into the Status column for that row.
e. On failure, use Google Sheets Update Values to write "Error: <short message>" into the Status column for that row.
3. Cap the run at 50 sends total. Remaining Pending rows are picked up on the next 15-minute tick.
Inputs I should be able to configure at the top of the workflow:
- Spreadsheet ID and sheet/tab name of the recipient sheet
- Subject line template (supports {{Column}} placeholders)
- HTML body template (supports {{Column}} placeholders)
- Per-run send cap (default 50)
- Which Status values count as ready to send (default: blank and "Pending")
Other requirements:
- Read the sheet once at the start of the run, do not re-fetch after each send.
- Update the Status cell one row at a time, immediately after each send, so a crash mid-run does not lose progress or double-send.
- Skip rows whose Email column is empty without marking them, so unfinished rows stay editable.
- Log a short summary at the end of each run: how many rows were sent, skipped, errored, and how many Pending rows remain.
Additional information
What does this prompt do?
- Reads your Google Sheet on a 15-minute schedule and finds rows marked Pending or blank.
- Personalizes a subject line and HTML body using merge fields like FirstName, Company, or any column you add.
- Sends one email per recipient, then writes Sent and a timestamp back to that row.
- Skips invalid email addresses and caps each run at 50 sends so you never blast the whole list at once.
What do I need to use this?
- A Google account with edit access to the recipient spreadsheet.
- A recipient sheet with at least an Email column and a Status column.
- A subject line and HTML body template you want to send.
How can I customize it?
- Change the schedule from every 15 minutes to hourly, daily, or whatever cadence fits your campaign.
- Edit the subject line and HTML body, including any merge fields like FirstName, Company, or OfferCode.
- Adjust the per-run send cap, or change which Status values count as ready to send.
Frequently asked questions
Do I need Gmail, Outlook, or my own SMTP account?
Can recipients reply to the email?
What happens if a row has a bad email address?
How does it avoid sending the same email twice?
Can I add my own merge fields beyond FirstName?
Turn your contact list into a personalized campaign.
Connect your Google Sheet once and Geni will run the mail merge on a schedule, send each email, and write Sent back to every row.