Google Sheets按条件拆分工作表及实现双向联动单元格的技术求助

问题一：按指定列（VALUE）拆分生成子工作表 #

通过Google Apps Script可实现自动按指定列拆分并生成对应工作表，操作步骤如下：

1. 打开目标Google Sheet，点击扩展程序 > Apps 脚本进入脚本编辑器
2. 替换默认代码为以下脚本（可根据实际列名/位置调整参数）：

function splitSheetByValue() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("PEOPLE");
  const data = sourceSheet.getDataRange().getValues();
  const header = data[0];
  const valueColIndex = header.indexOf("VALUE"); // 定位VALUE列索引
  if (valueColIndex === -1) {
    SpreadsheetApp.getUi().alert("未找到VALUE列");
    return;
  }

  // 提取唯一VALUE值，避免重复创建工作表
  const uniqueValues = [...new Set(data.slice(1).map(row => row[valueColIndex]).filter(val => val !== ""))];

  uniqueValues.forEach(value => {
    let targetSheet = ss.getSheetByName(`VALUE ${value}`);
    // 不存在则新建工作表并写入表头
    if (!targetSheet) {
      targetSheet = ss.insertSheet(`VALUE ${value}`);
      targetSheet.getRange(1, 1, 1, header.length).setValues([header]);
    }
    // 筛选对应VALUE的数据并写入子表
    const filteredData = data.filter(row => row[valueColIndex] === value);
    targetSheet.getRange(2, 1, filteredData.length - 1, filteredData[0].length).setValues(filteredData.slice(1));
  });
}

3. 保存脚本后点击运行按钮完成授权，执行即可生成拆分后的子工作表；后续源表数据更新时，重新运行该函数即可同步子表数据。

问题二：双向动态联动编辑 #

通过onEdit触发器实现子表与源表的双向同步编辑，具体实现如下：

核心脚本（整合双向同步逻辑） #

将以下代码添加到同一脚本编辑器中：

function onEdit(e) {
  const ss = e.source;
  const activeSheet = ss.getActiveSheet();
  const sourceSheet = ss.getSheetByName("PEOPLE");
  const header = sourceSheet.getDataRange().getValues()[0];
  const nameColIndex = header.indexOf("NAME");
  const valueColIndex = header.indexOf("VALUE");
  const eventCols = header.filter(col => col.startsWith("EVENT")).map(col => header.indexOf(col));

  // 子表编辑同步到源表
  if (activeSheet.getName().startsWith("VALUE ")) {
    const editedRow = e.range.getRow();
    const editedCol = e.range.getColumn();
    // 跳过表头行，仅处理EVENT列编辑
    if (editedRow === 1 || !eventCols.includes(editedCol - 1)) return;

    const name = activeSheet.getRange(editedRow, nameColIndex + 1).getValue();
    if (!name) return;

    // 匹配源表对应NAME的行并更新
    const sourceData = sourceSheet.getDataRange().getValues();
    const targetRowIndex = sourceData.findIndex(row => row[nameColIndex] === name) + 1;
    if (targetRowIndex === 0) return;
    sourceSheet.getRange(targetRowIndex, editedCol).setValue(e.value);
  }

  // 源表编辑同步到对应子表
  if (activeSheet.getName() === "PEOPLE") {
    const editedRow = e.range.getRow();
    const editedCol = e.range.getColumn();
    if (editedRow === 1) return;

    const value = activeSheet.getRange(editedRow, valueColIndex + 1).getValue();
    const name = activeSheet.getRange(editedRow, nameColIndex + 1).getValue();
    if (!value || !name) return;

    const targetSheet = ss.getSheetByName(`VALUE ${value}`);
    if (!targetSheet) return;

    // 匹配子表对应NAME的行并更新
    const targetData = targetSheet.getDataRange().getValues();
    const targetRowIndex = targetData.findIndex(row => row[nameColIndex] === name) + 1;
    if (targetRowIndex === 0) return;
    targetSheet.getRange(targetRowIndex, editedCol).setValue(e.value);
  }
}

注意事项 #

• 确保NAME列的值唯一，否则会出现匹配错误
• 首次运行脚本需完成权限授权，按提示操作即可
• 若子表名称含特殊字符，需调整脚本中的命名逻辑

内容的提问来源于stack exchange，提问作者Bleiz Del Sette