Marking a column as “email_sent” once script sends email to avoid duplicate emails

Question

Here is my code. I have a spreadsheet with 8 column headers, linked here https://docs.google.com/spreadsheets/d/1ZQp9GIzLrkEsOF6k_TwAZeUi7v5u94GBrDEKoB3Wk-8/edit?usp=sharing I am trying to get column I to have "email_sent" added to it to prevent sending duplicates every time someone submits details to the sheet. The script is running and sending an email formatted as per the Var = message section but I am having issues with it placing an "email_sent" message in column I and not sending the email on the next trigger of the script. I think it is the "sheet.getRange(rowData + i, 'I2:I1000').setValue('EMAIL_SENT');" section that is not working but unsure what is wrong.

function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName("Email"));
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange("A2:I1000");
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var rowData = data[i];
var emailAddress = rowData[1];
var recipient = rowData[0];
var message1 = rowData[3];
var message2 = rowData[4];
var message3 = rowData[5];
var message4 = rowData[6];
var message5 = rowData[7];
var emailSent = rowData[9];
if (emailSent != 'EMAIL_SENT') {  // Prevents sending duplicates
var message = 'Hi ' + recipient + ',\n\n' + message1 + ' ' + message2 + 
',\n\n' + message3 + ',\n' + message4 + ',\n' + message5;
var subject = 'Medical Questionairre Check';
MailApp.sendEmail(emailAddress, subject, message);
 sheet.getRange(rowData + i, 'I2:I1000').setValue('EMAIL_SENT');
  // Make sure the cell is updated right away in case the script is 
  interrupted
  SpreadsheetApp.flush();

 }
}
}

Answer 1

Fixing EMAIL_SENT Issue

I think this should do it. I changed the getRange() parameters to i+2, 9. Basically you just want a row and a column. The column is 'I' which column 9 and the row is normally i + 1 if you started the loop at the top but since you started the loop at row 2 then I think it should i + 2. So check it out.

Lately I've been adding the a check on the MailApp.getRemainingDailyQuota() to guarantee that I don't run myself out of emails. I also had the message and the subject on the wrong side of the condition.

function sendEmails() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("Email"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  for (var i = 1; i < data.length; ++i) 
  {
    var rowData = data[i];
    var emailAddress = rowData[1];
    var recipient = rowData[0];
    var message1 = rowData[3];
    var message2 = rowData[4];
    var message3 = rowData[5];
    var message4 = rowData[6];
    var message5 = rowData[7];
    var emailSent = rowData[9];
    var message = 'Hi ' + recipient + ',\n\n' + message1 + ' ' + message2 + ',\n\n' + message3 + ',\n' + message4 + ',\n' + message5;
    var subject = 'Medical Questionairre Check';
    if (emailSent != 'EMAIL_SENT' && MailApp.getRemainingDailyQuota()>0 && emailAddress && subject && message) 
    {       
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(i+1, 9).setValue('EMAIL_SENT');// Make sure the cell is updated right away in case the script is interrupted
     }
  }
}

Please be sure that you have a column title in each column including mail_sent column.

Answer 2

To change it an elegant way would be to start at a line of which you know for sure that it is not empty and find from there the last non-blank row with getNextDataCell().

Sample:

var notBlank = sheet.getRange("A1");
var lastRow = notBlank.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow():
sheet.getRange(lastRow, 16, column.length, 1).setValue('Processed');

This would find the last row before the first blank row (could be an interdispersed blank row between non-empty rows).

If you want to find the very last row of a sheet, then use getLastRow().

Sample:

var lastRow = sheet.getLastRow();
sheet.getRange(lastRow, 16, column.length, 1).setValue('Processed');

Careful:

Make sure that you really want to set the data into the last non-blank row (this means overwriting already existent data).

Kudos to ziganotschka's wonderful answer.