如何将持续更新的Google Sheets主列表按行人名同步数据到对应子工作表

前置准备 #

• 预先将4名负责人的完整姓名录入主表Master Prospect List的空白列（例如Z1:Z4），避免后续配置硬编码
• 预先创建4个与负责人姓名完全匹配的子工作表，同步主表的表头行到每个子工作表的第一行

1. 主表新增数据自动同步到对应负责人子表 #

分两种场景选择方案：

• 子表仅用于查看、不需要额外编辑：直接使用QUERY函数实现实时同步
  每个子工作表A1单元格输入公式，将[负责人姓名]替换为当前子表对应的负责人姓名即可：
  =QUERY('Master Prospect List'!A:ZZ,"SELECT * WHERE B = '[负责人姓名]' AND B IS NOT NULL",1)
  该方案无需额外配置，主表新增对应负责人的数据会自动同步到子表，列结构与主表完全一致。
• 子表需要编辑额外内容、不希望同步操作覆盖已有编辑：使用Google Apps Script实现增量同步
  打开「扩展程序 > Apps Script」，粘贴以下代码，保存后添加onEdit触发器即可：

  function syncToSubSheet(e) {
    const masterSheet = e.source.getSheetByName("Master Prospect List");
    const editedRange = e.range;
    if (editedRange.getSheet().getName() !== "Master Prospect List" || editedRange.columnStart !== 2 || editedRange.rowStart === 1) return;
    const ownerName = editedRange.getValue();
    const targetSheet = e.source.getSheetByName(ownerName);
    if (!targetSheet) return;
    const rowData = masterSheet.getRange(editedRange.rowStart, 1, 1, masterSheet.getLastColumn()).getValues()[0];
    targetSheet.appendRow(rowData);
  }
  

2. 主表A列自动填充新增数据的日期时间 #

推荐使用更稳定的脚本方案，在上述Apps Script代码中新增逻辑即可，无需额外配置：

function onEdit(e) {
  const masterSheet = e.source.getSheetByName("Master Prospect List");
  const editedRange = e.range;
  if (editedRange.getSheet().getName() !== "Master Prospect List" || editedRange.rowStart === 1) return;
  // 填充日期时间逻辑
  const timeCell = masterSheet.getRange(editedRange.rowStart, 1);
  if (timeCell.getValue() === "") {
    timeCell.setValue(new Date());
    timeCell.setNumberFormat("yyyy-mm-dd hh:mm:ss");
  }
  // 同步到子表逻辑可直接放在此处复用
}

如果不想使用脚本，可使用公式方案：

• 打开「文件 > 设置 > 计算」，开启迭代计算，最大迭代次数设为1
• 选中A列从第二行开始的所有单元格，输入公式：=IF(B2<>"",IF(A2<>"",A2,NOW()),"")，按Ctrl+Enter批量填充即可

3. 主表按负责人自动匹配不同高亮颜色 #

使用条件格式实现：

• 选中主表除表头外的所有数据区域（例如A2:ZZ10000）
• 打开「格式 > 条件格式」，规则类型选择「自定义公式是」
• 为4名负责人分别创建4条规则，公式为=$B2="[对应负责人姓名]"，分别设置4种不同的高亮填充色，保存即可。注意B列前的$符号必须保留，确保整行的判断都基于B列的负责人姓名。

4. 国家代码列添加下拉选择列表 #

使用数据验证实现：

• 在主表任意空白列录入所有需要的国家代码选项（例如AA1单元格输入+86、AA2输入+1、AA3输入+44等）
• 选中国家代码列所有需要下拉选择的单元格，打开「数据 > 数据验证」
• 条件选择「从范围中选择列表」，范围选中你刚才录入国家代码的区域，勾选「在单元格内显示下拉列表」，保存即可。如果需要限制仅能输入列表内的内容，可勾选「拒绝输入无效数据」。

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