Office Script实现Excel获取A列最新日期并调整过滤逻辑

修改后的脚本实现 #

下面是调整后的脚本，核心逻辑是先提取A列的有效日期，找出最新的日期后计算28天前的阈值，再执行过滤：

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // 获取A列（服务器时间列）的已使用数据范围
  let dateColumnRange = selectedSheet.getRange("A:A").getUsedRange();
  let dateValues = dateColumnRange.getValues();
  
  let latestDate: Date | null = null;
  // 遍历A列，筛选并找出最新的日期
  for (let row of dateValues) {
    let cellValue = row[0];
    if (cellValue instanceof Date) {
      if (!latestDate || cellValue > latestDate) {
        latestDate = cellValue;
      }
    }
  }

  if (!latestDate) {
    console.log("A列未找到有效日期数据");
    return;
  }

  // 计算最新日期往前推28天的阈值日期
  let filterDate = new Date(latestDate);
  filterDate.setDate(filterDate.getDate() - 28);
  
  // 配置并应用自动筛选
  let autoFilter = selectedSheet.getAutoFilter();
  // 如果没有设置过筛选范围，先绑定A列所在的整行数据区域
  if (!autoFilter.getRange()) {
    autoFilter.apply(dateColumnRange.getEntireRow(), 0);
  }
  autoFilter.apply(autoFilter.getRange(), 0, {
    filterOn: ExcelScript.FilterOn.custom,
    criterion1: "<" + filterDate.toLocaleDateString()
  });
}

脚本关键点说明 #

• 高效获取数据：用getUsedRange()只处理A列有数据的部分，避免遍历空单元格
• 日期校验：通过instanceof Date判断单元格值是否为有效日期，确保计算准确
• 异常处理：增加了无有效日期的判断，避免脚本运行报错
• 索引修正：Office Script中列索引从0开始，A列对应索引0，修正了原脚本中索引1的错误

入门学习指引 #

• 核心对象操作：先掌握Workbook、Worksheet、Range这三个核心对象的基础用法，比如获取工作表、读取单元格值、修改数据的方法
• 日期处理：重点学习JavaScript Date对象的使用，Office Script会自动将Excel的日期值转换为JS Date对象，直接调用Date的方法即可进行日期计算
• 筛选API：熟悉AutoFilter对象的各类方法，包括apply（应用筛选）、clear（清除筛选），以及不同筛选类型（自定义筛选、值筛选等）的参数配置
• 调试技巧：用console.log()输出中间变量，在编辑器的控制台查看结果，快速排查问题

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