Send an email for every row in a Google Sheet

This tutorial will teach you how to send an email for every row in a Google Sheet.

Goal

You have a list of email addresses in a sheet and you want to send an email to each person on this list.

[note]

Please ensure that you have the necessary permissions (or consent) from users before sending them email. Many countries have laws governing email communication so please ensure that you follow them. This post does not cover these additional requirements.

Furthermore, please be aware that Google restricts the amount of email that can be sent from Google Sheets via Apps Script. So, the solution described in this post may not be useful if you need to send large volumes of email (eg. tens of thousands of email).

[/note]

Prerequisites

This tutorial assumes that you're familiar with:

• Google Sheets

• Coding using Google Apps Script (just a basic understanding is sufficient)

• Basic coding concepts such as loops and arrays (ideally you're familiar with JavaScript)

[note]

If you don't know coding or you don't know Apps Script, I've written a number of articles to teach you the basics of coding using Google Sheets and Apps Script.

[/note]

This tutorial is divided into four sections

• Create a sheet called Emails containing email addresses.

• Create a sheet called Templates that contains the subject and body of the email message.

• Create an Apps Script to read data from your sheet and send emails.

• Run your script to send emails.

Section 1: Create a sheet containing email addresses

• Create a new Google Sheet.

• Rename the sheet from Sheet1 to Emails.

• Enter a header for the column that will contain email addresses. I entered "Email Addresses".

• Enter the email addresses of users you want to email.

Section 2: Create a sheet called Templates containing the subject and body of the email message

• Create a new sheet called Templates.

• Enter the email subject and body in the sheet. Enter the subject in cell A2 and the body in cell A5.

Section 3: Create an Apps Script to read data from your sheet and send emails

First, open the script editor by selecting Extensions → Apps Script. Then, replace the function [code]myFunction[/code] with the code below.

/**
 * Returns a rectangular grid of values in a given sheet.
 * @param {string} sheetName The name of the sheet.
 * @return {object[][]} A two-dimensional array of values in the sheet.
 */
function getData(sheetName) {
  var data = SpreadsheetApp.getActive().getSheetByName(sheetName).getDataRange().getValues();
  return data;
}

/**
 * Sends an email for each row.
 */
function sendEmails() {
  var templateData = getData("Templates");
  var emailSubject = templateData[1][0]; //Cell A2 (contains the email subject)
  var emailBody = templateData[4][0]; //Cell A5 (contains the email body)
  var emailData = getData("Emails");
  var headerRow = emailData.shift(); //Remove the header row
  emailData.forEach(function (row) {
    var email = row[0];
    MailApp.sendEmail(email, emailSubject, emailBody);
  });
}

Understanding the output of the getData() function

The data returned by the [code]getData()[/code] function is a two-dimensional array of values in a sheet, indexed by row and then by column. Consider the following statement.

emailData = getData("Emails");

The output of the [code]getData("Emails")[/code] function is the data structure below. It is a two-dimensional array of values in the Emails sheet.

[
  [ "Email Addresses" ],
  [ "rob@example.com"  ],
  [ "jack@example.com" ],
  [ "paige@example.com" ],
  [ "terry@example.com" ],
  [ "margaret@example.com" ],
  [ "jenny@example.com" ],
  [  "frank@example.com" ],
  [ "paul@example.com" ]
]

Below is a representation of the above data structure in tabular form. To access a value, use the row index followed by the column index. To access the email address "paige@example.com", use [code]emailData[3][0][/code] (3 is the row index and 0 is the column index).

Understanding how the sendEmails() function works

First, we use the [code]getData()[/code] function to get the email subject and body from the Templates sheet.

var templateData = getData("Templates");
var emailSubject = templateData[1][0]; //Cell A2 (contains the email subject)
var emailBody = templateData[4][0]; //Cell A5 (contains the email body)

Next, we get the data in the Emails sheet.

var emailData = getData("Emails");

We remove the header row from [code]emailData[/code] since it isn't an email address. The [code]shift()[/code] function removes the first value in an array and returns it.

var headerRow = emailData.shift(); //Remove the header row

Then, we iterate through each row in [code]emailData[/code], get the email address from the row and send an email.

emailData.forEach(function (row) {
  var email = row[0];
  MailApp.sendEmail(email, emailSubject, emailBody);
});

Section 4: Run your script to send emails

To run your script, choose the [code]sendEmails[/code] function from the drop down menu and click the play icon.

You'll be asked to authorize the script. Once you authorize it, your script will run and emails will be sent. Here is a screenshot of an email that was sent when I ran the script.

Future work

There are several ways in which the script can be improved. Here are a few:

• Check if the data in a row is a valid email address before sending the email. The script currently does not validate the data in the sheet. This is important to do if the email addresses are entered by users directly since there could be typos.

• There is no way for a recipient to unsubscribe from receiving the email. If you're sending these emails to your colleagues at work, they can just email or call you if they don't want to receive the emails. But if you're sending newsletters or emails with promotional content, you could be required by law to collect opt-in consent and/or provide additional options for recipients to unsubscribe.

• There are no checks in place to prevent duplicate emails from being sent. For example, if you click the play icon twice by mistake, your users are going to get two emails. This would lead to a bad user experience and users could mark your emails as SPAM.

• The email is currently not personalized. In general, users (myself included) prefer receiving emails that are relevant and personalized. This could be as simple as addressing the user by their name instead of a generic "Hi there". You can also get more sophisticated with messaging but at that point it is probably better to use an Email CRM system versus Google Sheets and Apps Script.

Summary

In this tutorial, you learned how to send an email for every row in a Google sheet.

Thanks for reading!