CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Rock
Rock

Reputation: 107

Google sheets apps script FILTER returning empty array

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).

enter image description here

Why is the filter returning an empty array?

Upvotes: 0

Views: 329

Answers (1)

Cooper
Cooper

Reputation: 64032

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();

Upvotes: 2

Related Questions