Google Scripts comparing dates

Question

I am trying to get a script going that takes in the date of a cell, compares it to today, and runs if the difference is 4 days. I am not sure how to get the if statement to work. Currently it will send emails, but it will not check if the statement is correct. I am not sure how to incorporate it into the sheet.

So looking at my code so far,

 var checkStatus = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses").getRange("K:K"); 
 var status = checkStatus.getValue();*
 
  
 var formattedDate = Utilities.formatDate(new Date(), "GMT-4", "MM-dd-yyyy");
  
 if(status != "closed" && "NEED THIS TO BE DATE OF ENTRY" > (today+4)'{*
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data").getRange("A1");
    var emailAddress = emailRange.getValue();
    var message = 'It looks like there is a ticket that has not yet been closed in your sheet. Please check the sheet and ensure all customers are taken care of!'
    var subject = 'Tech Support Ticket Over-Due';
    MailApp.sendEmail(emailAddress, subject, message);
    }
}

Basically, I want it to email me if there are any dates in column A that are older than 4 days and do not carry the status of "Closed" in column L.

so do you have an issue or are you just looking for general advice? — Baby_Boy
Questioins on this site should be spectific. If you are looking for debugging help, you a should include minimal reproducible example. — Rubén
I am not sure how to get the if statement to work. Currently it will send emails, but it will not check if the statement is correct. I am not sure how to incorporate it into the sheet. — Noah Armstrong
developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… — Cooper
I see you have given pseudo code for your if block (which you stated was your true question, not sending an email). Great start - but provide examples of what you have tried to code as well. If you can't provide example of code you have written to try, this implies the question should be for resources to learn to achieve this. PS: welcome to SO — Elysiumplain

Diego Diego · Accepted Answer · 2020-11-05T18:16:58

Calculate the number of days between the submission date and today, then check if it's greater than 4.

Here's an example of how to calculate the day difference. (I removed the UTC adjustment for simplification. Please read that answer's comments and see what makes sense for your scenario.)

function daysBetween(startDate, endDate) {
  var millisecondsPerDay = 24 * 60 * 60 * 1000;
  return (endDate - startDate) / millisecondsPerDay;
}

So to loop through the responses and send an email to those that aren't "closed" and were submitted more than 4 days ago, you could do this:

function doSomething() {
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses").getDataRange().getValues();
  var now = new Date();
  
  for (var i = 0; i < responses.length; i++) {
    var response = responses[i];
    var submittedAt = response[0]; // Column A
    var status = response[10]; // Column K
    
    var daysSinceSubmission = daysBetween(submittedAt, now);
    
    if (status != "closed" && daysSinceSubmission > 4) {
      // Send email
    }
  }
}

Google Scripts comparing dates

1 Answers