Send Email for New Row added using google script

Question

I am trying to send email notifications when a new row has been added in my google sheet. I have tried everything that has been recommended in other posts but I cannot get it to work. When i execute my script an email is sent for every row that is in the spreadsheet and the body of the message does not contain the information from the row.

The way it should work is as follows:

In my "Issues" spreadsheet, when I select OPEN from the dropdown list, column 34 will auto populate with "Ready to Email" and column 35 (emailsent column) will be blank. When my "email" function executes, it will get the range of the spreadsheet and check to see if column 35 for that new row is set to "Ready to Email" and check if the "EmailSent" column does not equal "SENT". If this is true then it will send the the email, and mark that row as "SENT". If for some reason the email does not send, that row should not be marked as "SENT".

Can someone explain why I'm getting an email for every row and why the email body does not include the information that I want. I have looked at other posts, but no luck. Here is my script.

function email() 
{

  //setup function
  var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Issues");  
  var StartRow = 2; //first row of data to process
  var LastRow = ActiveSheet.getLastRow();
  var RowRange = LastRow - StartRow + 1;
  var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,35);
  var AllValues = WholeRange.getValues();
  var message = "";    
  for (var i=0;i<AllValues.length;i++) 
   
    {
      var CurrentRow = AllValues[i];
      var EmailSent = CurrentRow[34]; 
      if (CurrentRow[33] == "Ready to Email" && EmailSent!= "sent") 
      {
        message +=                      //set HTML template for information
          "<p><b>Issue Status: </b>" + CurrentRow[0] + "</p>" +
          "<p><b>Group: </b>" + CurrentRow[1] + "</p>" +
          "<p><b>LifeCycle Status: </b>" + CurrentRow[2] + "</p>" +  
          "<p><b>Issue Number: </b>" + CurrentRow[3] + "</p>" +
          "<p><b>Priority: </b>" + CurrentRow[4] + "</p>" +
          "<p><b>Opened By: </b>" + CurrentRow[10] + "</p>"; 
        var setRow = i + StartRow;
        var SendTo = "[email protected]";
        var Subject = "New Issue reported:  " + CurrentRow[3];    //set subject line   
        //send the actual email  
           MailApp.sendEmail
                  ({
                        to: SendTo,
                        cc: "",
                        subject: Subject,
                        htmlBody: message,
                  });
  
         ActiveSheet.getRange(setRow, 35).setValue("sent");  //update the row if email is sent
    }  
  }
}

Here is what my email looks like. As you can see, my email only include the labels and no values from the rows.

I'm unable to reproduce your problem. Your code works as expected for me. Are you sure that you are using the correct indexes? Remember that in your spreadsheet your columns and rows start at index 1, i.e. col A = col 1 but in javascript arrays are zero-based so col 1 = [0]. In your script CurrentRow[33] is actually column 34 (AH) on your sheet and 'Send' should be in column 35 or AI. You will also have an issue with var message = ""; being outside the loop, I believe this should be inside the loop. Please share a sample sheet is you can't resolve the issue. — James D
@James D, Thank you so much for the help. Your suggestion worked!! As you suggested, I added var message = " "; to be inside the loop and it worked. — destiny931

Unknown Unknown · Accepted Answer · 2020-04-27T08:40:33

Posting it as an answer for Stack's workflow purposes. As James D said, the indexes were wrong:

Rows and Columns start and index 1, not 0.

So in the example it should be CurrentRow[34] instead of CurrentRow[35], and var message = ""; should be inside the loop.

Send Email for New Row added using google script

1 Answers