How to debug why a Google Script is not reading information from rows?

Question

I am attempting to write a script to email employees account details after they have been hired. The script finally sends an email. and does not send duplicates. However it is not reading the information as expected across each row. I have no idea what is going on. I am also truly lost over the line

sheet.getRange(startRow + i-18,1).setValue(EMAIL_SENT);

at the bottom and why I had to put in the -18 in order to get the EMAIL_SENT written to the correct row in the sheet.

Here is the script:

var EMAIL_SENT = 'EMAIL_SENT';

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var startRow = 2; // First row of data to process

  var numRows = 55; // Number of rows to process



  var dataRange = sheet.getRange(startRow, 1, numRows, 9);


  // Fetch values for each row in the Range.

  var data = dataRange.getValues();

  var subject = "MCS Account Info";

    for (i in data) {
 //  clear variables between loops    
      var email1 = "";

      var ccs = "";

      var message = "";

      var row = data[i];

      var email1 = row[4];
      if(row[4] !== "") var ccs = row[4];
      if(row[5] !== "") var ccs = ccs + ", " + row[5];
      if(row[6] !== "") var ccs = ccs + ", " + row[6];
      if(row[7] !== "") var ccs = ccs + ", " + row[7];

      var message = row[8]; 
 // just so I can see what is in each variable will be removed when working    
      Logger.log("email - ",row[3],"subject - ",subject,"message - ",message,"bcc - ",ccs);

      if (row[0] !== EMAIL_SENT) {
        if(row[2] !== "") {
          MailApp.sendEmail(row[3], subject, message, {
            bcc: ccs
          });
          sheet.getRange(startRow + i-18,1).setValue(EMAIL_SENT);
          SpreadsheetApp.flush();
        }
      }
    }
}

and a link to the test sheet I am using: https://docs.google.com/spreadsheets/d/1CzrMlsgwvkzp_w4IjZcOkSl7rU9T8qnBYBYi2luwWkg/edit?usp=sharing

In case the link does not work this is a sheet with 9 columns.

A - EMAIL_SENT message
B - # to look up info from another sheet
C - Username
D - User's Email address
E - H Additional Email Addresses for BCC
G - Message to send

Sheet will have several hundred entries as the year progresses right now I am looking at the first 56 rows even though my sheet only has 2 entries in it.

Answer 1

Try this:

function sendEmails() {
  var sheet=SpreadsheetApp.getActiveSheet();
  var startRow=2; 
  var numRows=55; 
  var dataRange=sheet.getRange(startRow,1,numRows,9);
  var data=dataRange.getValues();
  var subject="MCS Account Info";
  for (var i=0;i<data.length;i++) {
    var ccs="";
    var row=data[i];
    var email1=row[4];
    if(row[4]!="") var ccs=row[4];
    if(row[5]!="") var ccs=ccs + ", " + row[5];
    if(row[6]!="") var ccs=ccs + ", " + row[6];
    if(row[7]!="") var ccs=ccs + ", " + row[7];
    var message=row[8]; 
    if (row[0]!='EMAIL_SENT' && row[2]!="") {
      MailApp.sendEmail(row[3], subject, message, {bcc: ccs});
      sheet.getRange(i+startRow,1).setValue('EMAIL_SENT');
    }
  }
}