I can't get correct filter of dates in columns.

My filter conditions (AND) return months from other years than specified :

function filterColumnDates() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Filter Dates");

  let monthFilter = sheet.getRange("A4").getValue();
  let yearFilter = sheet.getRange("B4").getValue();  
  Logger.log("monthFilter : "     monthFilter   " - yearFilter : "   yearFilter)
  
  currentMonth = yearFilter  " "    monthFilter
  Logger.log("currentMonth : "     currentMonth )

  var headers = sheet.getRange("A2:NF2").getValues()[0]; 
  //Logger.log("headers : "     JSON.stringify(headers)   " - "   headers.length) 
  Logger.log("typeof(headers[0]) : "   typeof(headers[0])) // typeof(headers[0]) : object
  
  let filteredDates = headers.filter(c => Utilities.formatDate(c, "GMT", "YYYY")== yearFilter && Utilities.formatDate(c, "GMT", "MMMM") == monthFilter)  
  Logger.log("filteredDates : "     JSON.stringify(filteredDates)   " filteredDates.length : "   filteredDates.length)  // filteredDates.length : 36

  let colStart = headers.indexOf(filteredDates[0])
  Logger.log("colStart : "     colStart) // colStart : 0
  
  let colEnd = headers.indexOf(filteredDates[filteredDates.length - 1])
  Logger.log("colEnd : "     colEnd ) // colEnd : 369
}

Script edited to show typeof dates.

Here's the sheet I should have shared : https://docs.google.com/spreadsheets/d/1cE4FRKeVb8EMqm4q9brpjTcLPpZBH4qXud99lto--WQ/edit?usp=sharing

CodePudding user response：

When I saw your showing script, at let filteredDates = headers.filter(c => Utilities.formatDate(c, "GMT", "YYYY")== yearFilter && Utilities.formatDate(c, "GMT", "MMMM") == monthFilter), you are using the week year. I thought that this might be the reason of your issue. So how about the following modification?

From:

let filteredDates = headers.filter(c => Utilities.formatDate(c, "GMT", "YYYY")== yearFilter && Utilities.formatDate(c, "GMT", "MMMM") == monthFilter)

To:

let filteredDates = headers.filter(c => Utilities.formatDate(c, "GMT", "yyyy")== yearFilter && Utilities.formatDate(c, "GMT", "MMMM") == monthFilter);

Reference:

• Date and Time Patterns

  y: Year

  Y: Week year