0

I have a script which is working fine. But I'm looking for some improvement. In column H I have a date (start date for some event) in column K i have dropdown menu with some statuses. The script runs once per day and it checks the date in column H and if it's tomorrow it puts "Planned ENG" status in column K. The improvements I'm trying to add are:

  1. Comparing dates: currently the date in "H" must be in format dd:mm:yy hh:mm:ss (14/08/2019 01:00:00). The dates are filled by another script and most of the times are in this format. But sometimes we (a lot of co-workers) need to add the date manually and if it's only dd:mm:yy or it's 14/08/2019 00:00:00 the script not working. Can I compare only dd:mm:yy format?
  2. This will be little harder. I need if the date in column H is Monday the value of H to be changed in Friday.

I have tried:

  1. change toLocaleDateString() to toDateString() but it returns error:

    "TypeError: Cannot find function toDateString in object . (line 18, file "plannedENG")"

  2. Here I just think that I have to use getDay function but I'm not sure how.
function defaultValue() {
  var eng_status ='Planned ENG';
  var prop = new properties();
  var tomorrow = new Date();
  tomorrow.setDate(tomorrow.getDate()+1);
//  Logger.log(tomorrow.toLocaleDateString());
  Logger.log(tomorrow.toLocaleDateString());
  for (var key in prop.enabledSheetNames) {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(prop.enabledSheetNames[key]);
    var data = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).getValues();
    for (var i = 0; i < data.length; i++) {
//      Logger.log(data[i][4].toString());
      var indexOfReqID = data[i][4].toString().indexOf("REQ-SF");
      if (indexOfReqID > -1) {
//        Logger.log('It is in sheet:' + prop.enabledSheetNames[key]);
//        Logger.log('It is in row:' + (i+1));
//        Logger.log(data[i][7].toLocaleDateString());
        if (data[i][7].toLocaleDateString() == tomorrow.toLocaleDateString() && data[i][10] == '') {
//          Logger.log('OK');
          ss.getRange((i+1), 11).setValue(eng_status);
        } 
      }   
    }
  }
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Dimitar Dimitrov
  • 1
  • 2
  • 1
    Hi Dimitar. This is more of a general JavaScript "how to compare Date objects?" question (as opposed to Apps Script specific) which presumably has many answers on StackOverflow already. [Try this one, for instance](https://stackoverflow.com/questions/2698725/comparing-date-part-only-without-comparing-time-in-javascript/6202196#6202196). I would recommend comparing the dates as numbers, not as strings. Using any math operation or `.getTime()` on a Date object will convert it to a number in milliseconds (e.g. `+date1 > +date2` works to determine if `date2` comes before `date1`). – dwmorrin Aug 13 '19 at 11:20
  • Possible duplicate of [Comparing date part only without comparing time in JavaScript](https://stackoverflow.com/questions/2698725/comparing-date-part-only-without-comparing-time-in-javascript) – dwmorrin Aug 13 '19 at 11:23
  • Welcome. Would you please share a copy of your spreadsheet (excluding any private or confidential information), and include an example of a successful outcome. – Tedinoz Aug 14 '19 at 06:41
  • Hello, https://docs.google.com/spreadsheets/d/1yI25rwaw2RkEm-NDUNGVtKMvbSwYDUhSEvoIDBleRLM/edit?usp=sharing – Dimitar Dimitrov Aug 16 '19 at 09:54

0 Answers0