Google Sheets更新人员列表时如何自动移除对应复选框？

方案一：纯公式+辅助表（无需脚本） #

这个方法通过把培训状态和成员姓名绑定，彻底解决行位置变化导致的错位问题，适合不会写代码的用户：

• 1. 新建「培训状态存档」工作表
     用来永久存储每个成员的培训完成情况，列结构设为：姓名、Group 1完成、Group 2完成、Group 3完成，用TRUE/FALSE记录状态（后续可以用条件格式模拟复选框样式）。
• 2. 汇总表用关联公式匹配状态
     假设汇总表的姓名列是A列，Group 1列是B列，在B2单元格输入公式：

  =XLOOKUP(A2, '培训状态存档'!A:A, '培训状态存档'!B:B, FALSE)
  

  下拉填充到所有行，Group 2、Group 3列同理，把公式里的'培训状态存档'!B:B换成对应列（C:C、D:D）。

• 3. 用条件格式模拟复选框
     选中汇总表的培训列（B-D列），点击「格式」→「条件格式」：
  • 格式规则选择「自定义公式」，输入=B2=TRUE（对应当前列的第一个单元格）
  • 点击「格式样式」→「单元格样式」，选择带勾选框的样式；或者在「文本」里输入✓，未满足条件时显示□，模拟复选框效果。
• 4. 自动同步新成员
     如果要自动把原始数据的新成员同步到存档表，可以在存档表的A2单元格用Query公式：

  =QUERY('原始数据'!A:A, "SELECT A WHERE A IS NOT NULL", 1)
  

  这样新成员会自动加入存档表，培训状态默认是FALSE，手动修改即可。

方案二：Google Apps Script自动同步复选框（真实复选框） #

如果需要保留真实的复选框控件，用脚本实现状态与姓名的绑定，自动清理离职人员的复选框：

脚本代码 #

打开Google Sheets，点击「扩展程序」→「Apps脚本」，粘贴以下代码（替换工作表名称为你的实际表名）：

function syncTrainingCheckboxes() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rawSheet = ss.getSheetByName("原始数据"); // 替换为你的原始数据表名
  const summarySheet = ss.getSheetByName("汇总表"); // 替换为你的汇总表名
  const archiveSheet = ss.getSheetByName("培训状态存档"); // 用来存历史状态的表

  // 获取原始数据的姓名列表
  const rawNames = rawSheet.getRange(2, 1, rawSheet.getLastRow()-1, 1).getValues().flat();
  // 获取汇总表的姓名行和复选框区域
  const lastSummaryRow = summarySheet.getLastRow();
  if (lastSummaryRow < 2) return; // 没有数据时直接退出

  const summaryNameRange = summarySheet.getRange(2, 1, lastSummaryRow-1, 1);
  const summaryNames = summaryNameRange.getValues().flat();
  const checkboxRange = summarySheet.getRange(2, 2, lastSummaryRow-1, 3); // B-D列为复选框列

  // 先备份当前状态到存档表
  archiveSheet.clearContents();
  archiveSheet.getRange(1,1,1,4).setValues([["姓名", "Group1", "Group2", "Group3"]]);
  const currentStatus = summarySheet.getRange(2,1,lastSummaryRow-1,4).getValues();
  archiveSheet.getRange(2,1,currentStatus.length,4).setValues(currentStatus);

  // 遍历汇总表，清理离职人员的复选框，恢复在职人员的状态
  summaryNames.forEach((name, index) => {
    if (!rawNames.includes(name)) {
      // 清除离职人员的所有复选框
      checkboxRange.getCell(index+1, 1).setValue(false);
      checkboxRange.getCell(index+1, 2).setValue(false);
      checkboxRange.getCell(index+1, 3).setValue(false);
    } else {
      // 从存档表恢复在职人员的状态
      const archiveRow = archiveSheet.createTextFinder(name).findNext().getRow();
      const status = archiveSheet.getRange(archiveRow, 2, 1, 3).getValues()[0];
      checkboxRange.getCell(index+1, 1).setValue(status[0]);
      checkboxRange.getCell(index+1, 2).setValue(status[1]);
      checkboxRange.getCell(index+1, 3).setValue(status[2]);
    }
  });
}

// 创建定时触发，每周自动运行（可选）
function createWeeklyTrigger() {
  // 删除已存在的同名触发，避免重复
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === "syncTrainingCheckboxes") {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  // 创建每周一9点运行的触发（可根据你的数据刷新时间调整）
  ScriptApp.newTrigger("syncTrainingCheckboxes")
    .timeBased()
    .everyWeeks(1)
    .onWeekDay(ScriptApp.WeekDay.MONDAY)
    .atHour(9)
    .create();
}

使用步骤 #

1. 先运行createWeeklyTrigger函数，创建定时触发（或者手动点击运行syncTrainingCheckboxes来测试）；
2. 确保「培训状态存档」工作表已创建，脚本会自动备份状态；
3. 当原始数据刷新后，脚本会自动清理离职人员的复选框，同时保留在职人员的培训状态。

核心原理 #

不管哪种方案，核心都是让培训状态与成员姓名绑定，而非依赖行的位置。原始数据更新导致行顺序变化时，通过姓名匹配来获取对应状态，彻底避免错位问题。

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