title: Google Script Email loop
body: <p>I am relatively new to programming, and have recently been working on a script to send emails from a google spreadsheet when a cell in a certain column is changed. The recipient is assigned based off of an email address in another column in the same row as the change. I am having difficulty getting my code to stop running after the first email. As it is, the script runs indefinitely (at least until I run out of emails for the day). </p>
  
  <p>Here is the code:</p>
  
  <pre><code>    function sendNotification() {
  
        var sheet = SpreadsheetApp.getActiveSheet();
    //Get Active cell
    var mycell = sheet.getActiveSelection();
    var cellcol = mycell.getColumn();
    var cellrow = mycell.getRow();
    var address = sheet.getRange("C" + cellrow).getValue();
    var streetAddress = sheet.getRange("F" + cellrow).getValue();
    var startRow = 2;  
    var numRows = 2000;  
    // Fetch the range of cells A2:O2000
    var dataRange = sheet.getRange(startRow, 1, numRows, 15)
    // Fetch values for each row in the Range.
    var data = dataRange.getValues();
    for (i in data) {
      var row = data[i];
      var emailAddress = address;  // First column
      var message = streetAddress +" Has been Submitted for permitting!";             // Second column
      var subject = "The above Address has been Submitted For Permitting! We     will Follow up with you when it has been approved.";
    //Check to see if column is H to trigger
        if (cellcol == 8 &amp;&amp; sheet.getName() == "Sheet1" &amp;&amp; mycell !== "")
        {
    //Send the Email
        MailApp.sendEmail(emailAddress, message, subject);
        }
    //End sendNotification
    }
    }
  </code></pre>
  
  <p>What can I do to resolve this? Would a loop be the best option? How would I implement this?</p>
  
  <p>How about this approach? </p>
  
  <p>var EMAIL_SENT = "EMAIL_SENT";</p>
  
  <pre><code> function onEdit(e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var mycell = e.range;
    var cellcol = mycell.getColumn();
    var cellrow = mycell.getRow();
    var emailAddress = sheet.getRange("C" + cellrow).getValue();
    var streetAddress = sheet.getRange("F" + cellrow).getValue();
    var subject = "The above Address has been Submitted For Permitting! We     will follow up with you when it has been approved."
    // Fetch values for each row in the Range
      var message = streetAddress +" Has been Submitted for permitting!";  
      var emailSent = sheet.getRange("O" + cellrow).getValue();    
      if ( cellcol == 8 &amp;&amp; sheet.getName() == "Sheet1" &amp;&amp; emailSent !=      EMAIL_SENT) {  // Prevents sending duplicates
        MailApp.sendEmail(emailAddress, subject, message);
        sheet.getRange(cellrow, 15).setValue(EMAIL_SENT);
        // Make sure the cell is updated right away in case the script is     interrupted
        SpreadsheetApp.flush();
      }
    }
  </code></pre>