# Newsletter Generator: From a Google Sheets Row to a Draft Campaign

> A Make scenario that turns one row of a Google Sheets newsletter planner into a finished draft campaign in Campaign Monitor, in one of three layouts chosen by the editor.

Section: Make Automations

Page: https://mburhan.com/case/make-newsletter-generator-sheet-to-campaign-monitor

## Project Overview

This Make scenario automates the creation of newsletter draft campaigns using content stored in Google Sheets. Editors prepare the newsletter information in a spreadsheet, select one of three templates, and mark the row for creation. Make then builds the newsletter and creates a draft campaign in Campaign Monitor.

## 01. The Challenge

Creating a newsletter manually requires editors to copy titles, images, excerpts, links, and category labels into an email template. This process must be repeated for news articles, sponsored posts, a destination report, and a podcast episode.

## 02. The Solution

The Make scenario connects Google Sheets with Campaign Monitor to generate a newsletter draft from a single spreadsheet row. It uses the selected template to build the campaign content and updates the row when the process is complete.

## 03. How It Works

### Step 1: Prepare the Newsletter

The editor fills in a Google Sheets row with the content for three news posts, three sponsored posts, a destination report, and a podcast episode.

### Step 2: Select a Template

The editor chooses template 1, 2, or 3 and sets the **Create Newsletter** cell to `Create`.

### Step 3: Find the Requested Row

Make searches the sheet on its schedule for a row marked `Create` and processes one row at a time.

### Step 4: Route to the Selected Layout

A router directs the row to one of three routes based on the selected template number.

### Step 5: Build the Newsletter

A code step assembles the newsletter content, including banners, images, category labels, titles, excerpts, links, and buttons. The layout varies according to the selected template.

### Step 6: Create the Draft Campaign

An HTTP request sends the generated content to the Campaign Monitor API, which creates a draft campaign using the selected template.

### Step 7: Update the Spreadsheet

After the campaign is created, Google Sheets is updated with the status `Done`.

Image: The Make scenario: Google Sheets searches for a row marked Create, a router picks one of three templates, a code step builds the newsletter content, an HTTP call creates the campaign from the template, and Google Sheets updates the row. A Skip step on each route handles errors. (https://mburhan.com/case-content/make-newsletter-generator/image1.png)

## 04. Newsletter Content Structure

The generated newsletter includes:

- A top banner
- Three news posts
- A banner separating content sections
- Three sponsored posts
- A destination report
- A podcast episode
- An additional banner

News and sponsored posts include images, category labels, titles, excerpts, and links. The destination report includes a destination name and description, while the podcast section includes its date, title, description, and listening link.

## 05. Template Options

The scenario supports three newsletter layouts:

- **Template 1:** A three-row layout.
- **Templates 2 and 3:** Mixed layouts with two posts side by side and one centred below, using their respective templates.

## 06. Content Handling

The code includes defaults for missing content. Missing news or sponsored-post images use a default image, missing links point to the main website, and each campaign receives a unique name with a timestamp. The subject line and header date default to the current month and year. Sender details and the subscriber list are also set in the code step.

## 07. Workflow Architecture

Image (https://mburhan.com/uploads/9ea2bb3a7484f334d4e03c96.png)

Each template route builds the campaign content and sends it to Campaign Monitor. A Skip step on each route handles errors.

## 08. Tools and Integrations

- **Make:** Coordinates the workflow, routing, and newsletter generation.
- **Google Sheets:** Stores newsletter content, template selection, and processing status.
- **Campaign Monitor:** Creates the newsletter as a draft campaign.

## 09. Review and Error Handling

The campaign is created as a draft in Campaign Monitor so the team can review it before sending. If Campaign Monitor returns an error, the scenario skips that run instead of stopping with an error.

## 10. Outcome

The scenario allows editors to prepare newsletter content in Google Sheets and generate a corresponding Campaign Monitor draft without manually copying each content block into the email editor. The team can review the generated draft in Campaign Monitor before sending it.
