CODEWITHSUNDEEP
dategoogle-apps-scriptfilter
Michel
Michel

Reputation: 65

Apps script : I can't get correct filter of dates in columns

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

Upvotes: 0

Views: 130

Answers (1)

Tanaike
Tanaike

Reputation: 201358

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:

Upvotes: 3

Related Questions