title: Google Script email send ignoring #N/A values in row
body: <p>Essentially i'm also pulling email addresses from a separate sheet via a formula so that when new form entry is made, column B populates the proper email (name is included in the form). The problem I'm facing is that for the tool to be efficient I need to run the formula all the way down indefinitely so that when new entry comes it simply looks up the name and column B pulls the correct email address. However, the below code returns an error because it cannot handle #N/A where no email exists yet or even "" with IFERROR in the formula.</p>
  
  <p>Ideally the code will stop if #N/A is encountered and only continues when there is an email in CurrentRow[1] has an email. The code works perfectly fine if empty rows are totally blank. </p>
  
  <p>The current version here comes from what I've read and researched, including the post: <a href="https://stackoverflow.com/questions/45645662/email-only-new-rows-in-google-sheets">Email only new rows in Google sheets</a>. I've researched and lots of similar examples exist but having tried 40 or so variations i could not get mine to work. </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-js lang-js prettyprint-override"><code>function sendEmail() {
  
   //setup function
   var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
   var StartRow = 2;
   var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
   var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
   var AllValues = WholeRange.getValues();
  
   var message = "";
   
    //iterate loop
   for (i in AllValues) {
  
   //set current row
   var CurrentRow = AllValues[i];
  
   //define column to check if sent
   var KamEmail = CurrentRow[1];
  
   //if row has been sent, then continue to next iteration
     if (CurrentRow[1] == "#N/A") {break;}
       
    else;
      
       
   //set HTML template for information
    message +=
        "&lt;p&gt;Hi " + CurrentRow[1] + "&lt;/p&gt;" +
        "&lt;p&gt;Your constituent has submitted an Agenda blog pitch" + "&lt;/p&gt;" +
        "&lt;p&gt;&lt;b&gt;Name of constituent: &lt;/b&gt;" + CurrentRow[2] + "&lt;/p&gt;" +  
        "&lt;p&gt;&lt;b&gt;Submission Snapshot: &lt;/b&gt;" + CurrentRow[3] + "&lt;/p&gt;" +
        "&lt;p&gt;&lt;b&gt;Link to Submission: &lt;/b&gt;" + CurrentRow[4] + "&lt;/p&gt;" +
        "&lt;p&gt;&lt;b&gt;Date of Submission: &lt;/b&gt;" + CurrentRow[7] + "&lt;/p&gt;&lt;br&gt;&lt;br&gt;";
    
    //set the row to look at
    var setRow = parseInt(i) + StartRow;
  
    //mark row as "sent"
    ActiveSheet.getRange(setRow, 9).setValue("Sent to KAM");
  }
  
   //define who to send grants to 
   var SendTo = CurrentRow[1];
  
   //set subject line
    var Subject = "RE: Registrations";
  
  
    //send the actual email  
    MailApp.sendEmail({
        to: SendTo,
        cc: "",
        subject: Subject,
        htmlBody: message,
  });
  }</code></pre>
  </div>
  </div>
  </p>