google-apps-scriptdatefilter

Apps script : How do I return data filtered by month year?

I need to get the data filtered by month and year with dates in column A. I tried something like this, but have a problem with the date formatting

function testMonthYear() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()  
  var sheet = ss.getSheetByName("test");
  var data = sheet.getDataRange().getValues();
  
  Logger.log("data : " + JSON.stringify(data) +" "  + data.length)
  
  var filteredData = data.filter(function(r) {return Utilities.formatDate(r[0],"GMT+2",'MMMMyyyy' ==  "December2021")});
  
  Logger.log("filteredData : " + filteredData + "  "  + filteredData.length )  

}

Here's a simple test sheet : https://docs.google.com/spreadsheets/d/1P58YMZZ5VrVCrTR8huhhhnmsOMZvKnzRqEqua2dICpo/edit#gid=0

Solution

  • In your script, how about the following modification?

    From:

    var filteredData = data.filter(function(r) {return Utilities.formatDate(r[0],"GMT+2",'MMMMyyyy' ==  "December2021")});

    To:

    var filteredData = data.filter(function (r) { return Utilities.formatDate(r[0], "GMT+2", 'MMMMyyyy') == "December2021" });

    or

    var filteredData = data.filter(r => Utilities.formatDate(r[0], "GMT+2", 'MMMMyyyy') == "December2021");

    Reference: