If Statement condition not being followed in For Loop

[Karine Jarzecki]

Hello all,

Having some issues setting up a basic function to send an email when a new row has been added to a spreadsheet. Because I have it set up to go through each row, I want to make sure that an email is not sent when the word "Approved" is in the cell. Here is my code:

function SendEmail() 

{

  //setup function

  var ActiveSheet = SpreadsheetApp.getActive().getSheetByName("Completed");

  var StartRow = 3; //first row of data to process

  var LastRow = ActiveSheet.getLastRow();

  var RowRange = LastRow - StartRow + 1;

  var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,13);

  var AllValues = WholeRange.getValues();

  var message = "";    

  for (var i=0;i<AllValues.length;i++) 

   

    {

      var CurrentRow = AllValues[i];

      //var Approval = CurrentRow[11]; 

      if (CurrentRow[11] == "Approved") 

      {

        message +=               

          "<p><b>Characteristic 1: </b>" + CurrentRow[0] + "</p>" +

          "<p><b> Characteristic 2 : </b>" + CurrentRow[1] + "</p>" +

          "<p><b> Characteristic 3 : </b>" + CurrentRow[3] + "</p>" +  

        var setRow = i + StartRow;

        var SendTo = "MY EMAIL";

        var Subject = "New Trial Approval: " + CurrentRow[0] + " / " + CurrentRow[1]; 

           MailApp.sendEmail

                  ({

                        to: SendTo,

                        cc: "",

                        subject: Subject,

                        htmlBody: message,

                  });

           ActiveSheet.getRange(setRow, 11).setValue("Email Sent"); 

           message = "";

    }  else {

      continue

      

    }

  }

}

When the highlighted yellow text is "CurrentRow[11] = Approved", the function will work but then always send an email even when that cell value has been overrided with "Email Sent"? But when it is ==, then no email is sent. I'm new to Apps Script so I hope it is just something easy I am missing here. I've made sure that there's no weird formatting happening in the cell; it's literally the word "Approved".

Thanks,

[Stephen Schlager]

Do you know about logging statements? They are good way to figure out what's going on in your code.

For example right before your if condition you might say:

      console.log("CurrentRow[11] is equal to " + CurrentRow[11]);

  if (CurrentRow[11] == "Approved")

 

This would tell you what you are comparing to "Approved".

The thing about arrays is they start at index 0, so when you use CurrentRow[11] you are actually getting the 12th item in the array...

But later in the code

        ActiveSheet.getRange(setRow, 11).setValue("Email Sent"); 

The 11 is actually the 11th column, since the getRange function starts from column 1, not column 0. It's confusing when you first begin, but it's so common, it becomes secondary.

But use log statements to check what your code is doing...

Here's the modified code with console.log statements.

function SendEmail(){

  //setup function

  var ActiveSheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");

  var StartRow = 3; //first row of data to process

  var LastRow = ActiveSheet.getLastRow();

  var RowRange = LastRow - StartRow + 1;

  console.log("Row range: " + RowRange);

  var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,13);

  var AllValues = WholeRange.getValues();

  var message = "";    

  for (var i=0;i<AllValues.length;i++) 

   

  {

    var CurrentRow = AllValues[i];

    //var Approval = CurrentRow[11]; 

    console.log("CurrentRow[11] is equal to '" + CurrentRow[11] + "' --- CurrentRow[10] is equal to '" + CurrentRow[10] + "'");

    if (CurrentRow[10] == "Approved") 

    {

    console.log("Equal to 'Approved', sending email.");

      message +=               

        "<p><b>Characteristic 1: </b>" + CurrentRow[0] + "</p>" +

        "<p><b> Characteristic 2 : </b>" + CurrentRow[1] + "</p>" +

        "<p><b> Characteristic 3 : </b>" + CurrentRow[3] + "</p>";

      var setRow = i + StartRow;

      var SendTo = "youremail";

      var Subject = "New Trial Approval: " + CurrentRow[0] + " / " + CurrentRow[1]; 

          MailApp.sendEmail

                ({

                      to: SendTo,

                      cc: "",

                      subject: Subject,

                      htmlBody: message,

                });

          ActiveSheet.getRange(setRow, 11).setValue("Email Sent"); 

          message = "";

    }  else {

      console.log("Not equal to 'Approved', continuing");

      continue ;      

    }

  }

}

First Run (two emails are sent for rows 3 and 6):

Second Run (no emails are sent):

[Stephen Schlager]

The above pictures are correct, except the columns should read like so: