如何在Google Apps Script中实现等效于指定FILTER公式的数组筛选并返回结果？

我明白你想要把Sheets里的嵌套FILTER公式转换成Apps Script代码的需求，这样你就能拿到筛选后的数组并替换成新值。先理清楚你的原公式逻辑，再一步步修正代码里的问题：

原公式逻辑拆解 #

你的公式=FILTER( FILTER($10:$500, Beginning_Date <= Date_Range, End_Date >= Date_Range, Jobs_Range=Job), Employee_Name = Names_Range)其实做了两步筛选：

1. 先从第10行到500行的数据集里，筛选满足以下三个条件的行：
   • Beginning_Date列的日期 ≤ 指定的Date_Range
   • End_Date列的日期 ≥ 指定的Date_Range
   • Jobs_Range列的值等于目标Job
2. 再从第一步的结果中，筛选Employee_Name列的值等于Names_Range的行

原脚本的核心问题 #

你的代码里有几个关键错误导致筛选不到结果：

• 错误地使用SpreadsheetApp.newFilterCriteria()创建筛选器对象，然后直接和数组元素比较——这个对象是给Sheets内置筛选功能用的，不能直接在数组filter()方法里做判断
• 单独提取dateFilter、jobFilter这些变量的逻辑不对，应该直接在主数据集的筛选回调里逐个判断条件
• 日期转换后，没有对应到数据集中Beginning_Date和End_Date的正确列索引（注意Apps Script数组是从0开始计数的，和Sheets的列号差1）
• 最后合并筛选条件时，用r[1]==dateFilter这种写法完全错误，因为dateFilter是数组，不是单个值

修正后的完整代码 #

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Replace Projected Hours')
  menu.addItem("Replace Hours","ReplaceProjectedHours").addToUi();
}

function ReplaceProjectedHours() {
  const ss = SpreadsheetApp.getActive();
  const sn = ss.getSheetByName('Projected Hours');
  
  // 直接取第10到500行的数据（对应原公式的$10:$500），减少不必要的计算
  const sheetData = sn.getRange(10, 1, 491, sn.getLastColumn()).getValues(); 
  
  // 获取所有命名范围的值
  const dateRangeBeginning = ss.getRangeByName("Date_Range_Beginning").getValue();
  const dateRangeEnd = ss.getRangeByName("Date_Range_End").getValue();
  const targetJob = ss.getRangeByName("Job").getValue();
  const targetEmployee = ss.getRangeByName("Employee_Name").getValue();
  const projectedHoursPerWeek = ss.getRangeByName("Projected_Hours_per_Week").getValue();

  // 把日期转换成Sheets内部的序列号（和你的转换逻辑一致）
  const startDateNum = dateRangeBeginning.getTime()/1000/86400 + 25569;
  const endDateNum = dateRangeEnd.getTime()/1000/86400 + 25569;

  // 这里需要你根据实际表格调整列索引！
  // 比如：假设Beginning_Date在B列（索引1），End_Date在C列（索引2），Jobs_Range在D列（索引3），Employee_Name在E列（索引4）
  // 请根据你的实际表格修改这些索引值！
  const BEGINNING_DATE_COL = 1;
  const END_DATE_COL = 2;
  const JOB_COL = 3;
  const EMPLOYEE_COL = 4;

  // 执行等效于嵌套FILTER的筛选
  const filteredResults = sheetData.filter(row => {
    // 跳过空行
    if (!row[0]) return false;

    // 把当前行的日期转换成序列号（如果你的日期列已经是日期格式，getValues会返回Date对象）
    const rowBeginDateNum = row[BEGINNING_DATE_COL] ? (row[BEGINNING_DATE_COL].getTime()/1000/86400 + 25569) : 0;
    const rowEndDateNum = row[END_DATE_COL] ? (row[END_DATE_COL].getTime()/1000/86400 + 25569) : 0;

    // 第一层筛选条件：日期范围 + 匹配Job
    const firstFilterPass = rowBeginDateNum <= endDateNum && rowEndDateNum >= startDateNum && row[JOB_COL] === targetJob;
    
    // 第二层筛选条件：匹配员工姓名（只有第一层通过才判断）
    if (firstFilterPass) {
      return row[EMPLOYEE_COL] === targetEmployee;
    }
    return false;
  });

  // 打印筛选结果，验证是否正确
  Logger.log("筛选到的行数：" + filteredResults.length);
  Logger.log(filteredResults);

  // 接下来你可以用filteredResults做替换操作，比如：
  if (filteredResults.length > 0) {
    // 假设要把筛选到的行的某一列替换成projectedHoursPerWeek，比如第6列（索引5）
    const updatedRows = filteredResults.map(row => {
      row[5] = projectedHoursPerWeek; // 修改对应列的值
      return row;
    });

    // 把修改后的数据写回表格（注意要对应原行的位置，这里需要你根据实际情况调整写入范围）
    // sn.getRange(10, 1, updatedRows.length, updatedRows[0].length).setValues(updatedRows);
  }
}

关键说明 #

1. 列索引调整：一定要根据你的实际表格修改BEGINNING_DATE_COL、END_DATE_COL这些常量的值，因为Apps Script的数组索引是从0开始的（比如A列是索引0，B列是1，以此类推）
2. 空行处理：筛选时跳过空行，避免无效数据干扰
3. 日期比较：直接在回调里把当前行的日期转换成序列号，和目标日期范围比较，逻辑和原公式完全一致
4. 嵌套筛选逻辑：先判断第一层条件，通过后再判断第二层，和原公式的嵌套FILTER逻辑完全对应

如果筛选结果还是不对，建议先用Logger.log打印几行原始数据，确认列索引和日期转换是否正确，比如：

// 打印前5行原始数据，检查列对应关系
Logger.log(sheetData.slice(0,5));

内容来源于stack exchange