email from google sheet if cell contains "X"

Question

I have a simple script based on Google's tutorial for sending emails from a sheet. I'd like to add in a statement that calls it only to run if an additional cell contains the value '3' or above.

I'm guessing I would need to include something like

if(sheet.getRange(6,5,numRows).getValue()>=3

Here's the current script

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


function SendEmail() {
  var ui = SpreadsheetApp.getUi();
  var file = SpreadsheetApp.getActive();
  var sheet = file.getSheetByName("Overview");  //Fetch appropriate sheet from workbook
  var startRow = 6; //First row of data to process
  var numRows = 60; //Number of rows to process
  //Fetch the range of cells E6:H60
  var dataRange = sheet.getRange(startRow, 5, numRows, 3);
  //Fetch values for each row in the Range
  var data = dataRange.getValues();
  for (var i=0; i < data.length; ++i) {
   // WHERE DOES THIS FIT??? if(sheet.getRange(6,5,numRows).getValue()>=3){      //change row and column in get range to match what you need
    var row = data[i];
    var emailAddress = row[0]; // Fifth column
    var emailSent = row[3]; // Eighth Column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, "Subject!", "Message here");
      sheet.getRange(startRow + i, 8).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
    }
  }
//}

sheet image

Thanks for any help you can give.

The current version always gets the value from F5, is that what you want? or do you want it dependent on something? The condition itself can be placed almost anywhere — Robin Gertenbach

Wim den Herder Wim den Herder · Accepted Answer · 2016-10-25T17:38:51

I've rewritten the script. Hope you like it! :)

var COLUMN_EMAIL_SENT = 7;
var COLUMN_VALIDATION = 5;
var COLUMN_EMAILADDRESS = 1;
var SHEET_NAME = "Overview";
var EMAIL_SENT = "EMAIL_SENT";

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  var values = sheet.getDataRange().getValues();
  for(var i=0;i<values.length;i++) {
    var row = values[i];
    var emailAddress = row[COLUMN_EMAILADDRESS-1];
    if(emailAddress && !(row[COLUMN_EMAIL_SENT-1] === EMAIL_SENT) && parseInt(row[COLUMN_VALIDATION-1],10) >= 3) {
      MailApp.sendEmail(emailAddress, "Subject!", "Message here");
      sheet.getRange(i + 1, COLUMN_EMAIL_SENT + 1).setValue(EMAIL_SENT);
    }
  }
}

email from google sheet if cell contains "X"

1 Answers