CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
THRILLHOUSE
THRILLHOUSE

Reputation: 163

How to make script send email everyday after date has past if criteria is not met

I have a spreadsheet that collects form responses that need to be checked by employees. I need it to send a reminder email If column A is not checked starting 3 days after the timestamped submission date.

I was able to make the email send 3 days after the timestamp if column A is not checked, but I cannot figure out how to make the script send every day, starting 3 days after the timestamp until column A is checked TRUE.

Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1fplP0xdzunRk4e4CRU3-csbyf6iuD8fjpyYUZM2SxfU/edit?usp=sharing

function sendDay3ReminderEmail() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1000;   // Number of rows to process
  var numOfColumns = sheet.getLastColumn();
  var dataRange = sheet.getRange(startRow, 1, numRows, 
numOfColumns);
  var data = dataRange.getValues(); 
  var dt=new Date();
  var dv=new 
Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  for (var i=0;i<data.length;i++) {
    var row = data[i];
    var reportNumber = row[20];  // 21st column U
    var emailAddress = row[22];  // 23rd column W
    var ssLink= row[23];  // 24th column X
    var message = ("Report #"+reportNumber+" on the Bully Report 
Spreadsheet has been not been reviewed in '3' days. Please review 
the issue and indicate its status once addressed. "+ssLink);      
// sixth column
    var d=new Date(row[21]);// 22nd column V
    if (!row[0] && new 
Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf()==dv) {
      var subject = ("Your attention is needed on Bullying Report 
Number "+reportNumber);
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

Currently, the script sends an email if column A is not checked on day 3 after the timestamp. I need it to send every day after day 3 if column A is not checked.

Upvotes: 0

Views: 76

Answers (1)

Vineet Kulkarni
Vineet Kulkarni

Reputation: 61

Change:

if (!row[0] && new 
Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf()==dv)

to

if (!row[0] && new 
Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf()<=dv)

and it should keep sending out the emails after the 3rd day.

The key requirement though is that the email should be sent once per day. This can be achieved one of two ways:

  1. Make sure the script is invoked only once per day -> Easier way
  2. Rewrite the date in column V to next day's date (if the script has write permission) -> Bad choice.

Do revert back with your exact requirement if this does not solve the problem for you.

Upvotes: 2

Related Questions