apply FilterCriteria "whenDateEqualToAny(dates)" - What is the correct form of the date array (dates) to parse?

user_11497551 0
Viewed 795

I want to add some quick filters using the ui of google sheets. Currently I want to allow the user to click "show last month" to only see the data of the last month. The dates are written in the first column. Now I prefer to use the filter of google sheets before just printing the values into the sheet, to allow the user to further modify that filter. Thus I am trying to build filterCriteria using SpreadsheetApp.newFilterCriteria().whenDateEqualToAny(dates) and I am parsing an array of valid dates. In the documentation it says I have to put a "Date[]" - doesn't that mean an array of dates?

Below the error message and my code:

Error message (linked to the line "var filterCriteria..."):

"Exception: The boolean condition can not have multiple values for equality checks for non-data source objects"

My code:

function showLastMonth() {
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName('evaluation')
  var now = new Date()
  var thisYear = now.getFullYear()
  var thisMonth = now.getMonth()
  
  if(thisMonth == 0){var startMonth = 11; var startYear = thisYear - 1}
  else{var startMonth = thisMonth - 1; var startYear = thisYear}
  var startDate = new Date(startYear, startMonth, 1)
  var endDate = new Date(thisYear, thisMonth, 0)
  var dates = getDateArray(startDate, endDate)
  var filter = sheet.getFilter()

  if(filter == null ){
    var range = sheet.getDataRange()
    var filter = range.createFilter()
  }
  var filterCriteria = SpreadsheetApp.newFilterCriteria().whenDateEqualToAny(dates)
  filter.setColumnFilterCriteria(1, filterCriteria)  
}

getDateArray = function(startDate, endDate){

  var startYear = startDate.getFullYear()
  var startMonth = startDate.getMonth()
  var dateArray = []; dateArray.push(startDate)
  var date = startDate; var day = date.getDay()-1
  while(date<endDate){
    day++
    date = new Date(startYear, startMonth, day)
    if(date<=endDate){dateArray.push(date)}
  }
  return dateArray;
}
google-apps-script google-sheets filter date
1 Answers

I believe your goal as follows.

  • You want to hide the rows of the values except for dates using the basic filter.
  • You want to achieve this using Google Apps Script.

Issue and workaround:

In the current stage, it seems that array of whenDateEqualToAny(array) is required to be the length of 1. I think that this is the reason of your issue. So for example, when var filterCriteria = SpreadsheetApp.newFilterCriteria().whenDateEqualToAny([dates[0]]) is used, no error occurs. This situation is the same with the setBasicFilter request of Sheets API. Unfortunately, it seems that this is the current specification. But, the official document says The acceptable values. which uses the plural form. Ref So I also think that this is not correct for the actual situation as mentioned by TheMaster's comment.

In order to achieve your goal, in this case, I would like to propose the following 2 patterns.

Pattern 1:

In this pattern, using setHiddenValues(), the values except for the values of dates in your script are set as the hidden values.

Modified script:

When your script is modified, please modify as follows.

From: 
var filterCriteria = SpreadsheetApp.newFilterCriteria().whenDateEqualToAny(dates)
To: 
var obj = dates.reduce((o, e) => Object.assign(o, {[`${e.getFullYear()}\/${e.getMonth() + 1}\/${e.getDate()}`]: true}), {});
var range = sheet.getRange("A1:A");
var dispValues = range.getDisplayValues();
var hiddenValues = range.getValues().reduce((ar, [a], i) => {
  if (a instanceof Date && !obj[`${a.getFullYear()}\/${a.getMonth() + 1}\/${a.getDate()}`]) {
    ar.push(dispValues[i][0]);
  }
  return ar;
}, []);
var filterCriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(hiddenValues).build();

Pattern 2:

In this pattern, using whenNumberBetween(), the values of dates in your script are shown. In this case, it is required to convert the date object to the serial number.

Modified script:

When your script is modified, please modify as follows.

From: 
var filterCriteria = SpreadsheetApp.newFilterCriteria().whenDateEqualToAny(dates)
To: 
var filterCriteria = SpreadsheetApp.newFilterCriteria().whenNumberBetween(
  (dates[0].getTime() / 1000 / 86400) + 25569,
  (dates.pop().getTime() / 1000 / 86400) + 25569
).build();
  • The conversion from the date object to the serial number was referred from this thread.

References:

user_71086530
Related