google script to send conditional email

Question

I'm trying to run a script on a google sheet to send an email only if a certain column does not equal "y". If that's true, then I want it to send the email and then put a "y" in the column to prevent sending a duplicate. This is not working though - I keep getting duplicate emails sent. Here's the code:

 function sendEmails() {   
   var sheet = SpreadsheetApp.getActiveSheet();
   var startRow = 2;    // First row of data to process   
   var numRows = 600;   // Number of rows to process   
   // Fetch the range of cells A2:B3   
   var dataRange = sheet.getRange(startRow, 1, numRows, 600) 
   //Fetch values for each row in the Range.   
   var data = dataRange.getValues();   
   for (i in data) {
     var row = data[i];
     if (row[19]!= 'y') {
       var emailAddress = "[email protected]"; // First column
       var message = "A new trip request has been submitted.  Here's what you need to know.  Trip #: " + row[1] + " " + row[4] + " to:" +
 row[10] + " Depart Date: " + row[8];       // Second column
       var subject = "New Trip Request  " + row[1];
       MailApp.sendEmail(emailAddress, subject, message);
       sheet.getRange(i+2,19).setValue('y');
     }
   }
 }

There is a possibility that you are referring to wrong column number in this line of code 'if (row[19]!= 'y') {'. — KRR
It was replacing the column header with a y in column 19 but I think I fixed that. So I'm pretty sure 19 is the right one. — Luke Smoker
By the way, I ran your code once, and it maxed out the number of emails my account could send in one day from one execution of your code. It's trying to send something like 600 emails because the FOR loop evaluates to true on every iteration. — Alan Wells

Alan Wells Alan Wells · Accepted Answer · 2014-12-19T23:13:20

The condition checking for a "y" value has a hard coded value:

if (row[19]!= 'y') {

Replace the 19 with "i"

if (row[i]!= 'y') {

google script to send conditional email

2 Answers