The if condition in Google Apps Script to send an email from Google sheets doesn't work

Question

The aim of the script below is to send an email from a Google sheet, BUT I only want it to send if a condition is met.

In my spreadsheet in column M (row 12), the value is either TRUE or FALSE. The cell shows TRUE or FALSE based on an AND script that runs inside the cell. For example, one condition for TRUE is that column P is empty (rather than showing EMAIL_SENT). The AND statement works fine.

But when I run the script, it just sends emails regardless of this condition. So something is wrong with the condition...

I based it on this tutorial here... but I just ran it like that and the original doesn't seem to work for me either, which doesn't help I guess!

// Create a date object for the current date and time.
var now = new Date();

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";
var SENDYESNO = "FALSE";

function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;  // First row of data to process
var numRows = 2;   // Number of rows to process
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(startRow, 1, numRows, 16)
// 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 title = row[0];
var name = row[2];
var link = row[9];
var emailAddress = row[10];  // First column
var message = row[14];       // Second column
var emailSent = row[16];     // Third column
var date = row[17];
var yesNo = row[12];
if (yesNo != SENDYESNO) {  // Prevents sending duplicates
  var subject = "Sending emails from a Spreadsheet";
  GmailApp.sendEmail(emailAddress, subject, "Dear " + title + " " +  name + ", \n\nThis is an email report of your link. \n\nYour link is " + link + " \n\nKind regards,\nName ", {
                     from: "[email protected]",
                     name: "My name"});
  sheet.getRange(startRow + i, 16).setValue(EMAIL_SENT);
  sheet.getRange(startRow + i, 17).setValue(now);
  // Make sure the cell is updated right away in case the script is interrupted
  SpreadsheetApp.flush();
}
}
}

Karl_S Karl_S · Accepted Answer · 2017-02-24T16:51:06

I would guess your value is Column M is a boolean true/false and not a text string. You are comparing it to a text string. Try changing

var SENDYESNO = "FALSE";

to

var SENDYESNO = false;

to test against the boolean. I would also change the logic to test for true and then change the if test from != to ===

var SENDYESNO = true;
//more code...
if (yesNo === SENDYESNO) { //continue with your code...

The if condition in Google Apps Script to send an email from Google sheets doesn't work

1 Answers