Create researched newsletter drafts from Google Sheets with Tavily, OpenAI, and MailerLite

Quick overview

This workflow runs weekly, reads a Google Sheets newsletter calendar to pick one due “Ready” topic, researches it with Tavily, drafts a three-section HTML newsletter using OpenAI and optional Gemini image analysis, then creates a draft campaign in MailerLite and writes the campaign ID back to the sheet.

How it works

1. Runs weekly on a schedule.
2. Reads rows from a Google Sheets calendar and selects the earliest topic that is marked Ready, due today or earlier (UTC), and has no Campaign ID.
3. Marks the selected row as In progress in Google Sheets and uses Tavily to research the main newsletter topic.
4. Uses OpenAI to generate a newsletter title and exactly three section topics based on the Tavily sources.
5. For each section topic, runs a focused Tavily search and uses OpenAI to write the section with one cited source URL, optionally calling Google Gemini to evaluate user-provided image URLs and pick one.
6. Validates the generated content, renders the three sections into plain HTML, and creates a draft campaign in MailerLite for the specified audience group.
7. Records the MailerLite campaign ID in Google Sheets and updates the row status to Draft.

Setup

1. Create a Google Sheets tab with the required columns (Content ID, Topic, Planned Date, Status, Audience Group ID, Image URLs, Campaign ID) and add at least one Ready row with a valid numeric MailerLite group ID.
2. Add Google Sheets OAuth credentials and set the spreadsheet ID and sheet name in the workflow configuration.
3. Add HTTP Header Auth credentials for Tavily and MailerLite (Bearer tokens) and ensure your MailerLite account supports custom HTML campaign creation.
4. Add OpenAI credentials for planning and writing, and Google Gemini credentials for the connected image-analysis tool. Set model IDs available to your accounts. If you don’t need image assessment, remove or disconnect the Gemini tool and update the writer’s instructions accordingly.
5. Set a MailerLite-verified sender name and email in the workflow configuration, and ensure any Image URLs in the sheet are direct, publicly accessible HTTPS image links.

Requirements

• A Google account and a Google Sheet with the required calendar columns.
• Tavily API access for research.
• OpenAI API access for the included planning and writing models.
• Image-analysis model: Google Gemini is included in the workflow. You can replace it with an OpenAI vision model or another compatible vision tool by changing the connected tool and adapting its inputs and prompts.
• A MailerLite account with a verified sender and an existing audience group.
• A MailerLite plan that supports custom HTML through the API; the documentation specifies the Advanced plan.
• Optional image URLs that you have permission to use and that email clients can access directly.

Customization

• Change the schedule, topic selection, audience, and writing tone.
• Replace Tavily with another research source and adapt the response mappings.
• Replace OpenAI with another compatible writing model; adjust the prompts and output mappings as needed.
• Replace Gemini with OpenAI vision or another compatible image-analysis tool. Connect it to the section-writing agent and adjust the tool inputs and instructions.
• Host approved images with Google Drive, Cloudinary, ImageKit, S3-compatible storage, or another provider. Supply direct, publicly accessible HTTPS image URLs.

Additional info

• Creates a MailerLite draft only; it does not automatically schedule or send the campaign.
• If no suitable image is available, a section can remain text-only.
• Gemini assesses image relevance; it does not verify reuse rights.
• Review the draft’s content, sources, images, audience, and email footer in MailerLite before sending.
• Avoid overlapping runs. If a run fails after creating a campaign, inspect MailerLite before retrying.
  API and hosting costs depend on usage.