如何用Office脚本在Excel网页版中高亮距离到期不足3个月的日期

可以实现，Office脚本是Excel网页版官方提供的自动化能力，完全可以替代原宏的到期检查高亮功能，实现效果和原逻辑一致。

实现思路 #

• 读取指定到期日期列的所有单元格值
• 计算当前日期和每个到期日的间隔，判断是否不足3个月
• 对符合条件的条目设置背景高亮，不符合的清除原有高亮避免误提示，还可以额外增加已过期条目的高亮规则

可直接使用的脚本代码 #

function main(workbook: ExcelScript.Workbook) {
  // 请修改为你实际的工作表名称
  const targetSheet = workbook.getWorksheet("证书清单");
  // 请修改为你存储到期日期的单元格范围，示例为D2:D100，不含表头
  const dateRange = targetSheet.getRange("D2:D100");
  const dateValues = dateRange.getValues();
  // 计算3个月后的日期阈值
  const today = new Date();
  const warnThreshold = new Date(today.setMonth(today.getMonth() + 3));

  // 逐行检查日期
  for (let i = 0; i < dateValues.length; i++) {
    const expireDate = new Date(dateValues[i][0] as string);
    // 跳过空单元格
    if (!expireDate.getTime()) continue;
    // 请修改为你需要高亮的行范围，示例为当前行的A列到E列
    const currentRow = targetSheet.getRange(`A${i+2}:E${i+2}`);
    if (expireDate < warnThreshold && expireDate > new Date()) {
      // 距到期不足3个月，设置黄色高亮，可自定义颜色代码
      currentRow.getFormat().getFill().setColor("#FFFF00");
    } else if (expireDate < new Date()) {
      // 已过期设置红色高亮，不需要可删除此段
      currentRow.getFormat().getFill().setColor("#FF0000");
    } else {
      // 正常有效期内清除高亮
      currentRow.getFormat().getFill().clear();
    }
  }
}

使用方法 #

• 打开Excel网页版，点击顶部菜单栏「自动执行」>「新建脚本」
• 把上述代码粘贴到右侧代码编辑区，按照注释修改适配你表格的参数
• 点击「运行」测试效果，确认符合预期后保存脚本即可
• 如果需要实现打开表格自动触发，可搭配Power Automate创建流，触发条件设置为「当文件被打开时」，执行动作选择运行该脚本即可

注意事项 #

• 存储到期日期的列需要设置为日期格式，避免脚本无法识别日期值
• 如果表格行数是动态增长的，可以把代码中的固定范围修改为获取工作表已使用范围，不需要每次新增数据都调整参数

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