Google Sheets email once based on cell values

Question

This script works, except it sends multiple emails, This script check Column F for any value above 0 then sends an email.Column F is where i manually enter data. Trying to get it to only send one email and put a "Email Sent" in column H. Then check if column H has a "Email Sent" if so don't send email again.

Here Is what my sheet looks like

https://docs.google.com/spreadsheets/d/1o5jKMDECIFozrwAQx6EDvPL1NkoMQ1nbpkd6AdS9ULk/edit?usp=sharing

function sendEmails() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses"); //get the sheet "Form Responses"
  var data = sSheet.getRange(2, 1, sSheet.getLastRow(),sSheet.getLastColumn()).getValues(); //get the values of your table, without the header
 var lastRow = sSheet.getLastRow();//gets the last row of entered data

 var yourColumn = "H";//set your column
 var yourFunction = ("Email Sent");//whatever function or formula or data you want,just don't forget to use lastRow do do the row number
  sSheet.getRange(yourColumn+lastRow).setValue(yourFunction);

  for (var i = 0; i < data.length; i++) { // For each element of your tab

var row = data[i];

if(row[5] > 0) {  // If the person have got at least 1 report outstanding (column E)

    var body = "Good day "+ row[0] + " you have " + row[5] + " Internal Audit reports outstanding.";
    GmailApp.sendEmail(row[1],"Outstanding Internal Audit Reports", body); // Sending a mail to alert

  }
 } 
}

Cooper Cooper · Accepted Answer · 2018-04-20T04:16:24

Try This:

function sendEmailsToTechs() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Form Responses");
  //var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var data=rg.getValues();
  var hRange=sh.getRange(2,8,sh.getLastRow(),1);
  var hValues=hRange.getValues();
  for (var i=1;i<data.length;i++){
    var row = data[i];
    if(row[5] && row[7]!='Email Sent') {
      var body = "Good day "+ row[0] + " you have " + row[5] + " Internal Audit reports outstanding.";
      GmailApp.sendEmail(row[1],"Outstanding Internal Audit Reports", body); 
      //Logger.log(body);
      hValues[i-1][0]='Email Sent';  
    }
  }
  hRange.setValues(hValues);//Puts the values into the spreadsheet all at one time. 
}

Google Sheets email once based on cell values

1 Answers