Comparing Dates in Google Scripts with Sheets Input-CodePudding

I am trying to create a pop up to warn a user they must update a part in inventory if the part date is more than 90 days old. The date on the sheet (Cell Q5) is autofilled from another sheet, but that shouldn't matter. The value for the cell on the spreadsheet is 9/2/2021. I've tried many things, but currently I am getting the value for Q5 showing up as NaN .

function CheckInvDate() {
  var ss = SpreadsheetApp.getActive().getId();
  var partsrange = Sheets.Spreadsheets.Values.get(ss, "BOM!A5:Q5");
  var currentDate = new Date();
  var parthist = new Date();
  parthist.setDate(currentDate.getDate() -90);
      
  for (var i = 0; i < partsrange.values.length; i  ){
    var name = partsrange.values [i][1]
    var partdate = partsrange.values [i][16]
    
  var parthisttime = new Date(parthist).getTime();
  var partdatetime = new Date(partdate).getTime();
  Logger.log("History "   parthisttime)
  Logger.log("Current "   partdatetime)
    
    SpreadsheetApp.flush()
        
//    if (parthist > partdate == "TRUE") {
//      SpreadsheetApp.getUi().alert('The price on '  name   ' is out of date. Please update price and try again.') 
//    }
  }
}

My last log was

[22-07-06 11:50:55:851 EDT] History 1649346655850

[22-07-06 11:50:55:853 EDT] Current NaN

I've seen a number of responses on Stack Overflow, but I can't understand them. They seem to refer to variables that I don't see in code, or commands I haven't seen before and I'm not sure if they are in date.

CodePudding user response：

Try this:

function CheckInvDate() {
  const ss = SpreadsheetApp.getActive();
  const vs = Sheets.Spreadsheets.Values.get(ss.getId(), "BOM!A5:Q5").values;
  let d = new Date();
  d.setDate(d.getDate() - 90)
  const dv = d.valueOf();
  const oldthan5 = vs.map(r => {
    if (new Date(r[16]).valueOf() < dv) {
      return r;
    }
  }).filter(e => e);
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(`<textarea rows="12" cols="100">${JSON.stringify(oldthan5)}</textarea>`).setWidth(1000), "Older Than 90 Days");
}

This outputs a dialog with the rows older than 90 days

CodePudding user response：

I went to try this on my script again after lunch, and for whatever reason I am no longer getting the NaN value. I made one change on the if statement to fix the logic, and now it is working correctly. Not sure what I did, but apparently the coding gods were unhappy with me before.

The only part I changed was

    if (parthisttime > partdatetime) {
      SpreadsheetApp.getUi().alert('The price on '  name   ' is out of date. Please update price and try again.') 
    }