Send Email from Google Sheets using Apps Script

In this post I described how to find the number of rows in a spreadsheet column that contained data. Let’s use that as the basis for a simple script for sending email from Google Sheets.

Click image to enlarge

Click image to enlarge

To send email to the addresses in column 2 – let’s say, to send a newsletter – we need our script to follow a few distinct steps:

  • Calculate how many mails to send
  • Get the range of cells that contains those addresses
  • Get the recipients’ email addresses from those cells
  • Loop through those addresses sending each an email

We’ll be making use of the getRange() method:

getRange(row, column, optNumRows, optNumColumns)

First, the number of emails to send. For this we’ll use the value provided by COUNTA and saved in cell E1, as described in this post. We pass the row and column to getRange(), but without the optional arguments, so it only looks in one cell. I’ve also chained the getValue() method to read the cell contents:

var numRows = sheet.getRange(1, 5).getValue();

Secondly, we need to derive the corresponding range of cells in the sheet. So, in our case, column B starting at row 2, and extending by the number of rows we just saved in numRows:

var startRow = 2; // starting row of data to process
var dataRange = sheet.getRange(startRow, 2, numRows, 1);

Now to extract the values:

var data = dataRange.getValues();

The variable data now contains a two-dimensional range of objects; we’ll loop through these to get the individual addresses.

In each pass through the loop we’ll send an email:

MailApp.sendEmail(emailAddress, subject, message);

Here’s the code to enter in the Script Editor. In it, you’ll see I’ve also used the onOpen() trigger to add a menu item with which to send the mails, as described in this post.

function sendEmails() {

// subject and message
var message = "This was sent from Google Sheets"; 
var subject = "Hello from Google Sheets";

var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // Starting row of data to process
var numRows = sheet.getRange(1, 5).getValue();
var dataRange = sheet.getRange(startRow, 2, numRows, 1); // Get range of cells
var data = dataRange.getValues(); // Get values for each row
for (i in data) { // loop for each row
 var row = data[i];
 var emailAddress = row[0]; // Only one column is returned, so index zero
 MailApp.sendEmail(emailAddress, subject, message); // send email
 }
}

function onOpen() {
 SpreadsheetApp.getUi()
 .createMenu('Send mail').addItem('Send', 'sendEmails').addToUi();
}

The first time you run the script, Google Drive will ask for some authentication – then it should send the email to each address listed.