Google sheets apps script FILTER returning empty array-CodePudding

Sheet "Main" contains all production data such as production date, machine, product, production quantity, etc. In another sheet "AT Guidecard Tracking", I am entering production date and machine. I want the 3rd column to automatically provide which product was run for the entered combination of machine and production date in form of a dropdown. There might be two or more products on same machine on same date. I have written the following piece of code in apps script:

// Get active sheet 
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
         
var listMain = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main");
  
// Get active cell in active sheet
var activeCell = ss.getActiveCell()

if(activeCell.getColumn() == 2 && activeCell.getRow() > 1 && ss.getName() == "AT Guidecard Tracking"){
    
    activeCell.offset(0,1).clearContent().clearDataValidations();
    
    if(activeCell.isBlank() == false){
      
      var machine = activeCell.getValue(); // Col 2
      var prodDate = activeCell.offset(0,-1).getValue(); // Col 1
      //Logger.log(prodDate);

      // Filter main proudction data based on machine and date 
      var MainData = listMain.getRange(2,1,listMain.getLastRow()-1,13).getValues();
      //Logger.log(MainData);

      // compute prod type using FILTER
      var prodTypeList = MainData.filter(function(item){
        return item[0] == prodDate && item[1] === machine; 
      })

      var distinct = (value,index,self) => {
          return self.indexOf(value) === index;
        }     
      
      var prodTypeValidationList = prodTypeList.map(x => x[3]).filter(distinct).sort();
      
      var prodTypeValidationRule = SpreadsheetApp.newDataValidation().requireValueInList(prodTypeValidationList).setAllowInvalid(false).build();      
      
      // Apply validation rule to adjacent cell using offset
      activeCell.offset(0,1).setDataValidation(prodTypeValidationRule);      
      
      
    }    
      
}

However, I am getting an empty list in Col 3 (data validation) when I run this. Both the date columns in sheet "Main" and "AT Guidecard Tracking" are formatted as Date.

For example, I enter date 19/07/2021 (stored in variable prodDate) in sheet "AT Guidecard Tracking" and Machine MS-02. There is a corresponding entry in sheet "Main" for this combination. When I debug and look at the data stored on variable MainData, the dates exactly match! (See attached image).

Why is the filter returning an empty array?

CodePudding user response：

I'd guess that this is never true item[0] == prodDate because one Date() object can never equal another Date() object however if you compare the valueOf() or the getTime() then you can make that kind of a comparison.

try this:

var prodDate = new Date(activeCell.offset(0,-1).getValue()).valueOf(); // Col 1

then try new Date(item[0]).valueOf() == prodDate

be careful that both dates are of the exact same Datetime

To Answer your question:

let dt = new Date();
let today = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
let yesterday = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate() - 1).valueOf();