My script is not triggering all emails based on timer-CodePudding

Heres a link to the google sheet with the script - https://docs.google.com/spreadsheets/d/1Qw8WefbVkS-AQXi1CcZ0z2CL-P0oNSZYqeT40oVF6go/edit#gid=0

I've set the script to trigger every minute to test if the emails are being issued. The script is running with no errors and the executions are showing its running every minute however I am not receiving the emails automatically.

The reason for the script is to remind a party when a process is due for review

I would like to be able to continually add data and have repeated emails in which it would send multiple emails about different processes to the same party. The script should be able to run daily and sent emails automatically. I changed the trigger to every minute to check if I would receive the emails however none have arrived. Please can someone help.

Code:

function reminder() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var data = sh.getDataRange().getValues()
  var d = new Date().getTime();
  for (var i=1;i<data.length;i  ){
    if (data[i][4]<=new Date(d 7*24*60*60*1000) && data[i][4]>=new Date(d 5*24*60*60*1000) && data[i][6]!=''){
      MailApp.sendEmail({to:data[i][3],
        subject: 'reminder ... week', 
        htmlBody: 'Hello ' data[i][1] ' The process for ' data[i][0] ' is due to review on ' data[i][4] ' Please review the content and contact the process team before its due date if amendments are required'
      })
      sh.getRange(i 1,7).setValue('sent')
    }
    else if (data[i][4]<=new Date(d 30*24*60*60*1000)  && data[i][4]>=new Date(d 28*24*60*60*1000)  && data[i][5]!=''){
      MailApp.sendEmail({to:data[i][3],
        subject: 'reminer ... month', 
        htmlBody: 'Hello ' data[i][1] ' The process for ' data[i][0] ' is due to review on ' data[i][4] ' Please review the content and contact the process team before its due date if amendments are required'
      })
      sh.getRange(i 1,6).setValue('sent')
    }
  }
}

CodePudding user response：

Issue:

Columns F and G are empty for all rows, so data[i][6]!='' and data[i][5]!='' will always be false. Because of this, the code will never enter the if and else if blocks, and therefore won't send any email.

Solution:

I'm not sure what the conditions for sending the email should be, but I guess you want to send an email in two different situations:

Date for Next Review is between 5 and 7 days after current date, and column G is empty.
Date for Next Review is between 28 and 30 days after current date, and column F is empty.

If that's the case, you should modify that last condition for both situations, since you want to send the email when column G (or F) is empty, not when it is populated.

That is to say, replace this:

if (data[i][4]<=new Date(d 7*24*60*60*1000) && data[i][4]>=new Date(d 5*24*60*60*1000) && data[i][6]!=''){

With this:

if (data[i][4]<=new Date(d 7*24*60*60*1000) && data[i][4]>=new Date(d 5*24*60*60*1000) && data[i][6]==''){

And this:

else if (data[i][4]<=new Date(d 30*24*60*60*1000)  && data[i][4]>=new Date(d 28*24*60*60*1000)  && data[i][5]!=''){

With this:

else if (data[i][4]<=new Date(d 30*24*60*60*1000)  && data[i][4]>=new Date(d 28*24*60*60*1000)  && data[i][5]==''){