Google Apps Script: sendEmail from sheet using cc and/or bcc, and avoid return of EMAIL_SENT to sheet

Question

I have a script that sends an email out to addresses on a sheet and then returns an entry in a column in the sheet that reads EMAIL_SENT.

function sendEmail()
{
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var numRows = sheet.getLastRow();
var EMAIL_SENT = "EMAIL_SENT";
var dataRange = sheet.getRange(startRow, 1, numRows, 2000)
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) 
{
var row = data[i];
var name = row [0].split(",")[1];
var emailAddress = row [2]; 
var Setting = Utilities.formatDate(new Date(row [2]), "CST", "MM-dd-YYYY");
var Place = row [4];
var emailSent = row[5];
if (emailSent != "EMAIL_SENT")
{  
var message = "message body";
var subject = "Email to Client";

MailApp.sendEmail
(emailAddress, subject, message,
{htmlBody:message, 
cc:'[email protected]'});

sheet.getRange(startRow + i, 6).setValue(EMAIL_SENT);
SpreadsheetApp.flush();  

}}}

As you can see from the script, I am also wanting to cc a copy of the email to an internal email address, but avoid the return of the EMAIL_SENT to the sheet for this internal email.

The script as written above sends two emails, but it does not seem to send the cc'd email to the internal email address, and it returns an instance of 'EMAIL_SENT' to the column in the source sheet.

Use a different method signature for MailApp, i.e. this one: developers.google.com/apps-script/reference/mail/… As far as not writing a value to the sheet... Then remove or make the responsible lines inaccessible. — tehhowch
Maybe you can check the email id to which email is being sent before setting the EMAIL_SENT data ? — Umair Mohammad

Diego Diego · Accepted Answer · 2018-03-30T20:31:15

I commented out some of your code and made some notes. Without seeing the sheet you were working from, it's a bit challenging to figure out where the error was, but this should work.

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = sheet.getLastRow();
  var EMAIL_SENT = "EMAIL_SENT";
  var dataRange = sheet.getRange(startRow, 1, numRows, 5/*2000*/); //Confused why you were selecting 2000 columns.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; i++) { //switched to i++ instead of ++i
    //var row = data[i]; This isn't needed
    var name = data[i][0]; //row [0].split(",")[1];
    var emailAddress = data[i][1]; //row [2]; 
    var Setting = Utilities.formatDate(new Date(/*row [2]*/), "CST", "MM-dd-YYYY"); //Looks like you were creating a Date from the email address (row[2])...?
    var Place = data[i][3];
    var emailSent = data[i][4];
    if (emailSent != "EMAIL_SENT" && emailAddress != "") {
      var message = "message body";
      var subject = "Email to Client";
      MailApp.sendEmail(emailAddress, subject, message,{htmlBody:message, cc:'[email protected]'});
      sheet.getRange(startRow + i, 5).setValue(EMAIL_SENT);
      SpreadsheetApp.flush();  
    }
  }
}

Here is how I would organize the data, but you can obviously adapt to your needs. When setting dataRange, I grabbed 5 columns as the data only extends to column E.

Google Apps Script: sendEmail from sheet using cc and/or bcc, and avoid return of EMAIL_SENT to sheet

1 Answers