Google Apps Script跨表下拉联动筛选setColumnFilterCriteria报错求助

报错根因 #

• 方法入参类型不符合接口要求：setColumnFilterCriteria 接收两个参数，分别是列索引、FilterCriteria类构造生成的筛选规则实例，不能直接传入从单元格读取的字符串值
• 原代码直接将下拉单元格读取的选中值作为第二个参数传入，类型不匹配直接触发运行错误
• 原代码中“存在筛选就删除重建”的逻辑冗余，可直接复用现有筛选对象修改规则，减少不必要的操作

修复后可运行代码 #

/* 全局配置参数 */
const dropSS           = "Dashboard";     // 下拉列表所在工作表
const primaryDropRange = "I7";            // 下拉列表绑定单元格

// 表格打开时自动初始化下拉数据验证规则
function onOpen(e) {
  const dataSS           = "Lists";         // 下拉选项数据源所在工作表
  const primaryDataRange = "E1:E5";         // 下拉选项的数据源范围
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 读取数据源并拍平为一维选项列表
  const primaryDropList = ss.getSheetByName(dataSS)
                             .getRange(primaryDataRange)
                             .getValues()
                             .flat();
  // 为目标单元格绑定数据验证规则
  const dropTarget = ss.getSheetByName(dropSS).getRange(primaryDropRange);
  const validationRule = SpreadsheetApp.newDataValidation()
                                         .requireValueInList(primaryDropList)
                                         .build();
  dropTarget.setDataValidation(validationRule);
}

// 触发Data表联动筛选
function filterBA(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const selectedValue = ss.getSheetByName(dropSS).getRange('I7').getValue();
  const dataSheet = ss.getSheetByName('Data');
  let sheetFilter = dataSheet.getFilter();

  // 无现有筛选时，基于全量数据范围创建新筛选
  if (!sheetFilter) {
    sheetFilter = dataSheet.getDataRange().createFilter();
  }

  // 构造合规的列筛选规则
  // 列索引从筛选范围首列（A列）从0开始计数：E列对应索引4
  const colFilterRule = SpreadsheetApp.newFilterCriteria()
                                       .setVisibleValues([selectedValue])
                                       .build();
  
  // 传入正确类型的参数设置筛选规则
  sheetFilter.setColumnFilterCriteria(4, colFilterRule);
}

落地配置说明 #

• 要实现下拉值切换自动触发筛选，可在Apps Script项目中为filterBA函数绑定简单onEdit触发器，当Dashboard工作表I7单元格内容变更时自动执行筛选逻辑，无需手动运行函数
• setVisibleValues方法要求传入数组结构的入参，即使仅筛选单个值，也需要将值包裹为数组格式传入
• 现有列索引配置无需调整，按从筛选范围起始列从0计数的规则，E列对应的索引值4和代码设置完全匹配

内容的提问来源于stack exchange，提问作者Will