CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Victor Zucconi
Victor Zucconi

Reputation: 1

How to utilize if function to select which rows of data to consider in Google Sheets?

So, I'm trying to create a script for Google Sheets that sends an e-mail to A*, with the subject B*, and body C*, and that writes "Sent" (or whatever else) on column D*, and checks for that column to know when to jump to the next one or not. So far I got:

function sendMailtest() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheetByName('Sheet');
  var n=sheet.getLastRow();
  for (var i = 2; i < n+1 ; i++ ) 
  {
    var check = sheet.getRange(i,4);
    if (check == "Sent") {
      break;
    } else {
    var emailAddress = sheet.getRange(i,1).getValue();
    var subject = sheet.getRange(i,2).getValue();
    var message = sheet.getRange(i,3).getValue();
    MailApp.sendEmail(emailAddress, subject, message);
    check.setValue("Sent");
    }
  }
}

It actually works perfectly for how simple it is, my only problem is that no matter what I do with the IF statement, it always completely ignores it and sends an email for each row regardless of the information in column D. I've tried using break, continue, both with and without else{} so far, without any success. What am I missing here?

Upvotes: 0

Views: 35

Answers (1)

Boris Baublys
Boris Baublys

Reputation: 1203

var check = sheet.getRange (i, 4);

Here check is a range, but in

if (check == "Sent")

you are comparing it to text, so the equality is never satisfied. If you fix the code like this, then everything works well:

function sendMailtest() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheetByName('Sheet');
  var n=sheet.getLastRow();
  for (var i = 2; i < n+1 ; i++ ) 
  {
    var check = sheet.getRange(i,4).getValue();
    if (check != "Sent") {
      var emailAddress = sheet.getRange(i,1).getValue();
      var subject = sheet.getRange(i,2).getValue();
      var message = sheet.getRange(i,3).getValue();
      //    MailApp.sendEmail(emailAddress, subject, message);
      Logger.log(emailAddress + ":" + subject + ":" + message);
      sheet.getRange(i,4).setValue("Sent");
    }
  }
}

Upvotes: 1

Related Questions