title: Send Email for New Row added using google script
body: <p>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.</p>
  
  <p>The way it should work is as follows:</p>
  
  <p>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".  </p>
  
  <p>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.</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>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&lt;AllValues.length;i++) 
     
      {
        var CurrentRow = AllValues[i];
        var EmailSent = CurrentRow[34]; 
        if (CurrentRow[33] == "Ready to Email" &amp;&amp; EmailSent!= "sent") 
        {
          message +=                      //set HTML template for information
            "&lt;p&gt;&lt;b&gt;Issue Status: &lt;/b&gt;" + CurrentRow[0] + "&lt;/p&gt;" +
            "&lt;p&gt;&lt;b&gt;Group: &lt;/b&gt;" + CurrentRow[1] + "&lt;/p&gt;" +
            "&lt;p&gt;&lt;b&gt;LifeCycle Status: &lt;/b&gt;" + CurrentRow[2] + "&lt;/p&gt;" +  
            "&lt;p&gt;&lt;b&gt;Issue Number: &lt;/b&gt;" + CurrentRow[3] + "&lt;/p&gt;" +
            "&lt;p&gt;&lt;b&gt;Priority: &lt;/b&gt;" + CurrentRow[4] + "&lt;/p&gt;" +
            "&lt;p&gt;&lt;b&gt;Opened By: &lt;/b&gt;" + CurrentRow[10] + "&lt;/p&gt;"; 
          var setRow = i + StartRow;
          var SendTo = "myemail@test.com";
          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
      }  
    }
  }</code></pre>
  </div>
  </div>
  </p>
  
  <p>Here is what my email looks like. As you can see, my email only include the labels and no values from the rows.</p>
  
  <p><a href="https://i.stack.imgur.com/JSBBv.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JSBBv.jpg" alt="sample email"></a></p>