I got this code from the Google Sheets tutorial and it works, I want to send an email response after my peers fill out a google form. The email works, so the first step is complete, but it continues to send an email even though I have assigned it not send duplicate emails. My sheet is as follows:
Time Stamp/Email Address/Message/Complete for columns A-D
date-time/[email protected]/ Test1/ EMAIL_SENT
date-time/ [email protected]/ Test2/ EMAIL_SENT
date-time/[email protected]/ Test3/ EMAIL_SENT
// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = 'EMAIL_SENT';
/**
* Sends non-duplicate emails with data from the current spreadsheet.
*/
function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = 3; // Number of rows to process
// Fetch the range of cells B1:D3
var dataRange = sheet.getRange(startRow, 1, numRows, 3);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[1]; // Second column
var message = row[2]; // Third column
var emailSent = row[3]; // Fourth column
if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
var subject = 'Your PFB Request';
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 4).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
}
var dataRange = sheet.getRange(startRow, 1, numRows, 3);
getRange is not a zero-based method. Your definition defines a range that is only 3 columns wide. It does NOT include the "Complete" column.
Change this to :
var dataRange = sheet.getRange(startRow, 1, numRows, 4);
The "Complete" column will equate to row[3] (on a zero-based basis). Currently row[3] has no value, probably "undefined", and would explain why the "IF" statement is not working.