1

I have the task of trying to automate a email where if two conditions are met, then send the email. I am mostly there.

Code is as follows:

function dateSender() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("emails");
    var Lastrow =sheet.getLastRow();
  for (var i = 2;i<=Lastrow;i++){
      var person = sheet.getRange(i,8).getValue();
  var Emails = sheet.getRange(i,2).getValue();
  var body = sheet.getRange(i,9).getValue();
  var currentValue = sheet.getRange(i,3).getValue();
    var Date = sheet.getRange(i,5).getValue();
  if (currentValue == ("'Open'") && Date == today){ 
    MailApp.sendEmail(Emails,person,body)
  }
}
}

it works as far as the if cell is open then sends, but when I ad the date portion things don't send. I want it to be if the cell value is open, and the date is equal to today, then send the email.

Thanks in advance.

Sven
  • 47
  • 5
  • also change `MailApp.sendEmail(Emails,person,body)` to `MailApp.sendEmail(Emails,person,subject,body)` and define a subject for your email. – Marios Sep 17 '20 at 15:06
  • No change unfotunately – Sven Sep 17 '20 at 15:09
  • can you see whether the dates in your **sheet** are actually date objects? you can do that by using the `isdate()` formula: http://prntscr.com/uizpvm – Marios Sep 17 '20 at 15:09
  • it would be easier to solve your issue if you could share a copy of your file with us. Of course remove all the sensitive information and share a copy with us (public link so we can see it and make a copy of it ) – Marios Sep 17 '20 at 15:10
  • Says true when I do that. Only have one date at the moment as I am testing it still – Sven Sep 17 '20 at 15:11
  • 1
    also change that `currentValue == ("'Open'")` to that `currentValue == "Open"` and let me know if you see any difference (together with the change I recommended in the first comment) – Marios Sep 17 '20 at 15:11
  • Ask 1 question per post. Strike off secondary notes. Read the duplicate questions' answers for how to compare dates. @Marios You should read too. Your first comment is wrong. – TheMaster Sep 17 '20 at 15:26
  • @Sven here is the solution to your problem: http://prntscr.com/uj09gx – Marios Sep 17 '20 at 15:41
  • @TheMaster I don't believe this is a duplicate for so many reasons. First of all, the issue is not how he compares the dates. The issue is that he is using the reserved word `Date`. Secondly, he made a mistake in the if condition regarding the `"'Open'"` value. Then he forgot to put a subject and he used the body as the subject. He didn't use the name of the sender. Last but not least, the post you are referring as a duplicate shows solutions that the dates are defined manually by the user. None of them worked in that case, so I don't believe these two posts are duplicate. – Marios Sep 17 '20 at 15:44
  • @TheMaster For the above reasons, I am voting to open this question, but I am not going to answer since I don't want to get reputation points for something like that. I already posted my answer as a comment picture and I am fine with that. – Marios Sep 17 '20 at 15:44
  • @Marios You're free to vote however you see fit. But to defend my close, OP says: `it works as far as the if cell is open then sends, but when I ad the date portion things don't send.`. Which clearly means `currentValue == ("'Open'")` works. But the date comparision doesn't work. Although Date is a reserved word, it still isn't a problem.. The rest of the errors are irrelevant. In fact only one error should be focused per question. We're not a code debugging service. Even if we look at MailApp, `sendEmail(recipient, subject, body)` is a valid signature. (1/2) – TheMaster Sep 17 '20 at 15:59
  • *shows solutions that the dates are defined manually by the user* OP needs to first know how to do the simplest thing. Then if He has trouble, He can ask a specific question. The linked question is how I believe questions should be asked in this site: short , simple and highly focused on 1 and only one issue.(2/2) – TheMaster Sep 17 '20 at 16:02
  • @Marios In addition, I highly recommend against your comparison method of adding day+month+year. High chance of false positives: 2020-1-15 and 2020-2-14 will be equal as per your method, if I'm not mistaken. Also against getDisplayValue(string) and then converting to date. – TheMaster Sep 17 '20 at 16:13
  • @TheMaster that is true. Thanks for the feedback. – Marios Sep 17 '20 at 16:37

0 Answers0