Google App Scripts - Auto Sending Emails (specific issue delete row and appendRowL)

Question

I'm trying to use Google App Scripts to send HTML emails for me that I wrote. My code works to the point where I can get the e-mails to send however as you'll see in my code I have 4 different templates that I want to send . . As you'll see I have some code to change the template by 1 to find the right template and dateMath to increase the date for the next e-mail to send.

The issue that I'm running across right now is with deleteRow and appendRow. I have 2 users that I'm trying to e-mail and when I run the code deleteRow and appendRow replaces one of the users with the other. It'll make more sense with my screen shots. Any insight here? Before Script

After Script

function sendEmails(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var today = Utilities.formatDate(new Date(), "MDT", "dd/MM/yyyy");
  for(var i = 1; i<data.length; i++){
    
    if(data[i][3] !== ""){ //skip if is empty
     
      if(isValidDate(data[i][3])){ //skip if isn't a valid date
        
        var formattedDate = Utilities.formatDate(data[i][3], "MDT", "dd/MM/yyyy"); 

        if(formattedDate == today){ //send email if checkin date is today
          
          if(data[i][4] == 1) {  
           var template = HtmlService.createTemplateFromFile('Template1');
          } else if(data[i][4] == 2){
            var template = HtmlService.createTemplateFromFile('Template2');
          } else if(data[i][4] == 3){
            var template = HtmlService.createTemplateFromFile('Template3');
          } else {
            var template = HtmlService.createTemplateFromFile('Template4');
          }                                                                                                      
          var email = data[i][2];
          var firstName = data[i][0];
          var lastName = data[i][1];
          template.firstName = firstName;

          var subject = "Your Next Steps ";
        
          var bcc = "spencer@kwwestfield.com";
  
          var message = template.evaluate();
        
          GmailApp.sendEmail(email,
           subject,
           message.getContent(), {
             htmlBody: message.getContent(),
             bcc: bcc
          });
          
          //change the template
          //delete the row
          sheet.deleteRow(i+1);
          //add the row again
          var newTemplate = data[i][4] + 1;
          var newSend = dateMath(data[i][3], 8);
          sheet.appendRow([firstName, lastName, email, newSend, newTemplate]);
          
        }
      } 
    }
  }
}

/**
 * Does math on dates
 * Triggered from functions
 * Input: date = the orginal date, d = +- number of days
 * Output: a new date
 */
function dateMath(date,d){
  var result = new Date(date.getTime()+d*(24*3600*1000));
  return result
}                  


/**
 * Figures out if is a data
 * Triggered from functions
 * Input: d: any
 * Output: boolean
 */
function isValidDate(d) {
  if ( Object.prototype.toString.call(d) !== "[object Date]" ){
    return false;
  } else {
    return true; 
  }
}

Answer 1

Because you're deleting a row, the i value no longer corresponds to the same range. For example, you have an array with 5 elements:

[A, B, C, D, E]

If you delete element 0, you'll get

[B, C, D, E]

Now you increment your iterator so i++; // i = 1 and so the next value that you'll operate on is not "B", but instead "C".

You can try yourself by running this

function test() {
  var letters = ["A", "B", "C", "D", "E"];
  for (var i=0; i<letters.length; i++) {
    Logger.log("i : " + i + " || Letter: " + letters[i]);
    letters.shift();
  }
}

To fix your code, add a row variable that is independent of your array iterator i.

function sendEmails(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var today = Utilities.formatDate(new Date(), "MDT", "dd/MM/yyyy");
  var row = 2; // Starting at row 2
  for(var i = 1; i<data.length; i++){

    if(data[i][3] !== ""){ //skip if is empty

      if(isValidDate(data[i][3])){ //skip if isn't a valid date

        var formattedDate = Utilities.formatDate(data[i][3], "MDT", "dd/MM/yyyy"); 

        if(formattedDate == today){ //send email if checkin date is today

          if(data[i][4] == 1) {  
           var template = HtmlService.createTemplateFromFile('Template1');
          } else if(data[i][4] == 2){
            var template = HtmlService.createTemplateFromFile('Template2');
          } else if(data[i][4] == 3){
            var template = HtmlService.createTemplateFromFile('Template3');
          } else {
            var template = HtmlService.createTemplateFromFile('Template4');
          }                                                                                                      
          var email = data[i][2];
          var firstName = data[i][0];
          var lastName = data[i][1];
          template.firstName = firstName;

          var subject = "Your Next Steps ";

          var bcc = "spencer@kwwestfield.com";

          var message = template.evaluate();

          GmailApp.sendEmail(email,
           subject,
           message.getContent(), {
             htmlBody: message.getContent(),
             bcc: bcc
          });

          //change the template
          //delete the row
          sheet.deleteRow(row);
          row--; // Deleted a row
          //add the row again
          var newTemplate = data[i][4] + 1;
          var newSend = dateMath(data[i][3], 8);
          sheet.appendRow([firstName, lastName, email, newSend, newTemplate]);

        }
      } 
    }
    row++; // Go to the next row
  }
}