Automating Email Sending with CloudWatch Events and Google Sheets

Are you tired of manually sending emails on a regular basis? Do you want to automate this process to save time and effort? With CloudWatch Events and a Google Sheet, you can easily set up a system to automatically send emails on a schedule.

Note this article follows from Adding Subscribe and Unsubscribe, so please read that first!

1. The first step in automating your email sending is to create a Google Sheet that will store all the future emails that you want to send. This sheet could include columns for the subject, body or parameters that are substituted into the template of each email.
2. Next, you will need to set up a CloudWatch Event to trigger the sending of your emails. To do this, go to the CloudWatch dashboard and click the “Create rule” button. Select the “Schedule” option and choose the frequency at which you want your emails to be sent.
3. Then, add the Event as a trigger to the lambda that inserts all the emails into the Queue

Architecture Diagram

Let’s create a lambda that pulls the data from google sheets to form the email:

const { google } = require("googleapis");
const sheets = google.sheets("v4");

const SCOPES = ["https://www.googleapis.com/auth/spreadsheets"];
const spreadsheetId = "SpreadsheetId"; //Create a google sheets and copy the ID
                                       //after the /d/

const { LambdaClient, InvokeCommand } = require("@aws-sdk/client-lambda");

exports.handler = async (event) => {
  console.log(event);
  const auth = new google.auth.GoogleAuth({
    scopes: SCOPES,
  });
  const authToken = await auth.getClient();
  const res = await sheets.spreadsheets.values.get({
    spreadsheetId,
    auth: authToken,
    range: "Sheet1",
  });
  let vals = res.data.values;
  let counter = parseInt(vals[0][columnIndex])
  let index = ((counter - 2) % (vals.length - 1)) + 1; 
  //index resets when it goes past the number of rows

  let row = vals[index];

  const update = await sheets.spreadsheets.values.update({
    auth: authToken,
    spreadsheetId,
    range: "Sheet1!F1",
    valueInputOption: "USER_ENTERED",
    resource: {
      range: "Sheet1!F1",
      majorDimension: "ROWS",
      values: [[counter + 1]],
    },
  });
  return row;
};

The lambda looks at a counter in the first row and columnIndex column and that counter determines the row of information that needs to be sent. Then it increases the counter by 1.

For this to work, you need to install the googleapis dependency in the same directory and create a file named service_account_credentials.json that contains the keys needed to access google sheets. You can do all this locally and then zip the file for upload in the lambda console.

To create the service_account_credentials.json file to access Google Sheets, you can follow these steps:

1. Go to the Google Cloud Console: First, go to the Google Cloud Console at console.cloud.google.com.
2. Select a project: If you have multiple projects in your Google Cloud account, you will need to select the one that you want to use to access Google Sheets.
3. Enable the Google Sheets API: To use the Google Sheets API, you will need to enable it for your project. To do this, go to the “APIs & Services” section of the Google Cloud Console and click the “Enable APIs and Services” button. Search for “Google Sheets API” and click on the result to enable it.
4. Go to the “IAM & Admin” section: From the main menu, navigate to the “IAM & Admin” section.
5. Click on the “Service accounts” link: Under the “IAM & Admin” section, click on the “Service accounts” link.
6. Create a new service account: To create a new service account, click the “Create service account” button. Give your service account a name and select a role for it. You can also specify any additional permissions that you want to grant to the service account.
7. Create a key for the service account: After creating the service account, you will need to create a key for it. To do this, click the “Create key” button and select the “JSON” option. This will download a JSON file containing your service account credentials to your computer.
8. Rename the file: Finally, rename the JSON file to “service_account_credentials.json” and save it in a location where your code can access it.

You can invoke the lambda from the code that sends the emails with

lambda_client = boto3.client('lambda')
lambda_payload = {}
invoke_response = lambda_client.invoke(FunctionName=functionName, 
                     InvocationType='RequestResponse',
                     Payload=bytes(json.dumps(lambda_payload),encoding='utf-8'))
data = json.loads(invoke_response['Payload'].read().decode())

Then, however your data is formatted, you can substitute it into your email templates with

BODY_TEXT = "{0}".format(*data)
BODY_HTML = "<p>{0}</p>".format(*data)

Now, your newsletter is automated! Just update the google sheets in advance with whatever you want sent in the future days.

Here is an implementation I’ve created. Also, this article is part of a 4 part series. The next article is Let Subscribers Choose When they Receive Emails.