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:'internalemail@internalemail.com'});

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.

Answer 1

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:'internalemail@internalemail.com'});
      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.