date - apply FilterCriteria "whenDateEqualToAny(dates)"- 要解析的日期数组(日期)的正确形式是什么？

Question

我想使用 google 表格的 ui 添加一些快速过滤器。目前我想让用户点击“显示上个月”只看到上个月的数据。日期写在第一列。现在我更喜欢在将值打印到工作表之前使用 google 工作表的过滤器，以允许用户进一步修改该过滤器。因此，我正在尝试使用 SpreadsheetApp.newFilterCriteria().whenDateEqualToAny(dates) 构建 filterCriteria，并且我正在解析一组有效日期。在documentation它说我必须输入一个“Date[]”——这不是指一组日期吗？

在错误信息和我的代码下面:

错误消息(链接到“var filterCriteria...”行):

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

我的代码:

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;
}

Answer 1

我相信你的目标如下。

• 您想使用基本过滤器隐藏除 dates 之外的值的行。
• 您想使用 Google Apps 脚本实现这一目标。

问题和解决方法:

目前阶段，whenDateEqualToAny(array)的array似乎要求是1的长度。我认为这就是你的问题的原因。因此，例如，当使用 var filterCriteria = SpreadsheetApp.newFilterCriteria().whenDateEqualToAny([dates[0]]) 时，不会发生错误。这种情况与Sheets API的setBasicFilter请求是一样的。不幸的是，这似乎是当前的规范。但是，官方文档说 The acceptable values. 使用复数形式。 Ref所以我也认为这对于TheMaster's comment提到的实际情况是不正确的.

为了达到你的目的，在这种情况下，我想提出以下2种模式。

模式一:

在此模式中，使用 setHiddenValues()，将脚本中除 dates 值之外的值设置为隐藏值。

修改后的脚本:

当您的脚本修改时，请修改如下。

从:

var filterCriteria = SpreadsheetApp.newFilterCriteria().whenDateEqualToAny(dates)

至:

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

模式二:

在此模式中，使用whenNumberBetween()，显示脚本中dates 的值。在这种情况下，需要将日期对象转换为序列号。

修改后的脚本:

当您的脚本修改时，请修改如下。

从:

var filterCriteria = SpreadsheetApp.newFilterCriteria().whenDateEqualToAny(dates)

至:

var filterCriteria = SpreadsheetApp.newFilterCriteria().whenNumberBetween(
  (dates[0].getTime() / 1000 / 86400) + 25569,
  (dates.pop().getTime() / 1000 / 86400) + 25569
).build();

• 从日期对象到序列号的转换引用自this thread .

引用资料:

• setHiddenValues(values)
• whenNumberBetween(start, end)