Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsfor-in-loop

Unable to properly add value to a cell using GAS

I adopted the script below and I am having two issues.

  1. The "Email Sent" value does not update in the correct row. Let's say I sent an email to the recipient in row 5, instead of appearing in column 10 of row 5 "Email Sent" appears in column 10 of row 21.
  2. Sometimes "Email Sent" does not appear at all even though I know the email went out.

I've tried everything I can think of but I can't make it work.

//Send approval or non-approval of time off request
function sendLeaveRequestDecisions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 2");
  var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());
  var data = dataRange.getValues();
  for (i in data) {

    var startRow = 2; //First row of data to process
    var rowData = data [i];
    var startdate = rowData [2];
    var enddate = rowData [3];
    var type = rowData [4];
    var email = rowData [5];
    var recipient = rowData [1];
    var decision = rowData [7];
    var comments = rowData [8];
    var emailstatus = rowData [9]
    var emailPattern = /^[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;
    var validEmailAddress = emailPattern.test(email); 

    if (validEmailAddress == true && emailstatus != "Email Sent") {    


        var message = "<HTML><BODY>"
        + "<P>Dear " + recipient + ","
        + "<br /><br />"
        + "<P>The following request:"
        + "<br /><br />"
        + "<b>Type:  </b>" + type + "<br />" 
        + "<b>From:  </b>" + startdate + "<br />" 
        + "<b>To:  </b>" + enddate + "<br />" 
        + "<br /><br />" 
        + "<b>is </b>" + decision + "<br />"   
        + "<b>Comments </b>" + comments + "<br />"
        + "<br /><br />"    
        + "Diane"
        + "<br /><br />" 
        + "</HTML></BODY>";      
      MailApp.sendEmail(email, "Regarding your leave request", "", {htmlBody: message});   
   sheet.getRange(i + 2,10).setValue("Email Sent"); 
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
}
  }
Solution

  • Try this:

    Mostly what was wrong was that you didn't escape the forward slashes contained in your regex expression. I'm surprised it ran at all. It runs okay for me. You might like to take a look at Utilities.formatString() for building your message. Just click on Help/API Reference. From there it's down at the bottom left where it says Script Service/Utilities.

    Personally, I wouldn't put flush in a loop like that. I moved startRow and emailPattern out of the loop as well. They don't change. The less you put in a loop the faster it runs. I commented out the MailApp.sendmail because I didn't actually want to send email and I assume that you got that right.

    function sendLeaveRequestDecisions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Index Generation");
  var dataRange = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn());
  var data = dataRange.getValues();
  var startRow = 2; //First row of data to process
  var emailPattern = /^[a-z0-9!#$%&'*+\/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+\/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|aero|asia|biz|com|coop|edu|gov|info|int|jobs|mil|mobi|name|museum|name|net|org|pro|tel|travel)\b/;
  for (var i=0;i<data.length;i++){
    var rowData = data [i];
    var startdate = rowData[2];
    var enddate = rowData[3];
    var type = rowData[4];
    var email = rowData[5];
    var recipient = rowData[1];
    var decision = rowData[7];
    var comments = rowData[8];
    var emailstatus = rowData[9];
    var validEmailAddress = emailPattern.test(email); 
    if (validEmailAddress == true && emailstatus != "Email Sent") {
      var message = "<HTML><BODY>" + "<P>Dear " + recipient + "," + "<br /><br />" + "<P>The following request:" + "<br /><br />" + "<b>Type:  </b>" + type + "<br />" + "<b>From:  </b>" + startdate + "<br />" + "<b>To:  </b>" + enddate + "<br />"  + "<br /><br />" + "<b>is </b>" + decision + "<br />"   + "<b>Comments </b>" + comments + "<br />"+ "<br /><br />" + "Diane"+ "<br /><br />" + "</HTML></BODY>";          
      //MailApp.sendEmail(email, "Regarding your leave request", "", {htmlBody: message});   
      sheet.getRange(i + 2,10).setValue("Email Sent"); 
    }
  }                                             
  //SpreadsheetApp.flush();                                             
}