Google Script Email loop

Question

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).

Here is the 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 && sheet.getName() == "Sheet1" && mycell !== "")
      {
  //Send the Email
      MailApp.sendEmail(emailAddress, message, subject);
      }
  //End sendNotification
  }
  }

What can I do to resolve this? Would a loop be the best option? How would I implement this?

How about this approach?

var EMAIL_SENT = "EMAIL_SENT";

 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 && sheet.getName() == "Sheet1" && 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();
    }
  }

Your loop runs 2000 times, since you're fetching 2000 rows. The IF control on line 22 should check that value on current row and column 8 (H) is not empty row[8] != "". Instead you're checking mycell != "" that references a cell outside the loop. Also cellcol == 8 doesn't seem necessary. — Fredster
The thing is, I only want to send the email if the edited cell is in column 8. I think I was approaching this all wrong. Does this look any better? — AFliss

Fredster Fredster · Accepted Answer · 2016-05-17T07:30:08

If you use the onEdit() the function will be triggered every time you edit the cell. Is this what you want? What I assume you're looking for is a script that:

Reads every row of the active sheet
If the 8th column is not empty, sends an e-mail to the address in the first column
The whole script is trigger upon the user requests (not when the user edits a cell)

In this case the first approach is better, the sendNotification(). Also a loop is necessary to read all the rows. And the IF statement should be something like if (row[8] != "") then send the e-mail.

In this case row which was defined as row = data[i] in your first script will have the values of all the cells in the row being read in the loop. So row[8] will have the value of the 8th (column H), which you want to check for emptiness, thus if(row[8] != "").

Also, if I understand correctly the e-mail adress should be emailAddress = row[1] inside the loop, because the email address is different every row.

Google Script Email loop

2 Answers