Google AppScript: Send Multiple Emails by reading data from Spreadsheet

This article shows how to read list of email addresses, subject and message body from Google Spreadsheet and send separate emails to all of them with Google AppScript.

First, let’s see a simple example of sending single message to a particular email address.

Send Single Email

Here, AppScript’s MailApp class is used to send email. We can also use GmailApp class to do so.

function sendEmail() {
  var recipient = 'your.name@example.com'; // YOUR EMAIL ADDRESS
  var subject = 'Test';
  var body = 'Testing 123';
  MailApp.sendEmail(recipient, subject, body);
  //GmailApp.sendEmail(recipient, subject, body);  
}

Read data from SpreadSheet and Send Multiple Emails

In the code below, a spreadsheet is opened by its id and then the data in it is read.

To get the spreadsheed id:

– Open Google Spreadsheet
– See the URL in your browser’s address bar
– The URL will be something like this: https://docs.google.com/spreadsheets/d/9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx/edit#gid=0
– So, in this case, the spreadsheet id is “9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx”

The data in the spreadsheet is in the following format:

name	email	subject	message
John Root	john@example.com	Hello There!	My first message
Liza Wood	liza@example.com	Thank you friend	My second message
Tom Hanks	tom@example.com	Welcome abroad	My third message
Sam Roy	sam@example.com	Happy Birthday	My fourth message

/**
 * MailApp Class
 * https://developers.google.com/apps-script/reference/mail/mail-app
 */
function sendMultipleEmails() {
  var sheetId = '9tXNUNpM9tXNUNp0BydbYIIYLMxxxxxxxxxx'; // ID of spreadsheet
  var ss = SpreadsheetApp.openById(sheetId);
  
  var sheet = ss.getActiveSheet();
  //var sheet = ss.getSheetByName('Sheet1'); // Sheet1 is the name of the Sheet
  
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  /**
   * we start from i=1
   * i=0 is first row, we skip the first row as it contains heading info only
   */
  for (i=1; i < values.length; i++) {
    var name = values[i][0]; // first column data
    var email = values[i][1]; // second column data
    var subject = values[i][2]; // third column data
    var message = 'Hi ' + name + ', <br><br>' + values[i][3]; // fourth column data
    
    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: message
    }); 
  }  
}

Hope this helps. Thanks.