Google Sheets Script自动扩展条件格式至最新数据末行

实现功能 #

• 打开表格时自动在顶部菜单栏生成自定义操作入口，不需要每次手动进脚本编辑器运行
• 点击按钮即可自动识别当前工作表的真实数据末行，无需手动选中范围
• 自动更新工作表内所有已配置的条件格式规则的应用范围，保留原有判定逻辑和格式配置，仅扩展覆盖到最新导入的数据行
• 完全兼容ArrayFormula配置场景，不需要提前预留空白行，不会出现导入数据无法覆盖预留行的问题

部署步骤 #

• 打开目标Google Sheets文件，点击顶部菜单栏「扩展程序」→「Apps Script」，进入脚本编辑页面
• 删除编辑器中默认生成的空白示例代码，将下方完整脚本粘贴到编辑区
• 点击编辑器顶部的保存按钮，给脚本项目设置任意名称（比如「格式范围更新工具」），保存完成后关闭编辑器回到表格页面
• 刷新表格页面，等待5-10秒，顶部菜单栏会出现名为「表格工具」的自定义菜单

完整脚本代码 #

// 表格打开时自动加载自定义菜单
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('表格工具')
    .addItem('更新条件格式到最新行', 'extendConditionalFormatRanges')
    .addToUi();
}

// 核心逻辑：扩展所有条件格式规则的应用范围
function extendConditionalFormatRanges() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  // 识别真实数据末行：默认统计A列非空单元格数量，可根据你的实际数据列修改括号内的列标
  const targetColumn = activeSheet.getRange('A:A');
  const lastDataRow = targetColumn.getValues().filter(row => row[0] !== '' && row[0] !== null).length;

  // 读取当前表所有已存在的条件格式规则
  const existingRules = activeSheet.getConditionalFormatRules();
  const updatedRules = [];

  existingRules.forEach(rule => {
    const originalRanges = rule.getRanges();
    const newApplyRanges = [];
    originalRanges.forEach(range => {
      // 保留原规则的起始位置、列覆盖范围，仅将结束行更新为最新数据末行
      const adjustedRange = activeSheet.getRange(
        range.getRow(),
        range.getColumn(),
        lastDataRow - range.getRow() + 1,
        range.getNumColumns()
      );
      newApplyRanges.push(adjustedRange);
    });
    // 重建带新范围的规则
    updatedRules.push(rule.copy().setRanges(newApplyRanges).build());
  });

  // 将更新后的规则写回表格
  activeSheet.setConditionalFormatRules(updatedRules);
  SpreadsheetApp.getUi().alert(`操作完成，所有条件格式已覆盖至第${lastDataRow}行`);
}

使用注意事项 #

• 每次完成CSV追加导入操作后，点击顶部「表格工具」→「更新条件格式到最新行」，等待弹窗提示成功即可，不需要逐条修改条件格式规则
• 如果你的表格A列存在空值、不是连续有数据的列，请修改代码中getRange('A:A')的列标为你表格中数据连续无空值的列（例如'C:C'），避免末行识别不准
• 脚本不会修改原有条件格式的判定规则、单元格样式，也不会影响已配置的ArrayFormula运行
• 脚本每次运行都会实时计算当前最新数据末行，不需要提前在表格中预留空白行

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