能否通过Apps Script将Query(Filter())输出修改同步至Google Sheets源表？

联系人列表存储在「Schedule」工作表中，「CRR-NBD FMS」工作表的G6单元格为月份下拉框，当选择的月份与「Schedule」工作表A列匹配时，对应行通过Query(Filter())公式展示在「CRR-NBD FMS」中。由于公式输出不可编辑，已完成自动化配置，但需要实现：编辑「CRR-NBD FMS」工作表H9:AI列的复选框时，修改同步至「Schedule」源表的对应行。

当前使用的Apps Script代码如下：

function updateSchedule() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var crrnbdSheet = ss.getSheetByName("CRR-NBD FMS");
  var scheduleSheet = ss.getSheetByName("Schedule");

  var editedRange = crrnbdSheet.getRange("H9:AI");
  var editedValues = editedRange.getValues();
  var scheduleRange = scheduleSheet.getRange("H9:AI");

  scheduleRange.setValues(editedValues);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Update Schedule', 'updateSchedule')
    .addToUi();
}

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var crrnbdSheet = ss.getSheetByName("CRR-NBD FMS");
  var scheduleSheet = ss.getSheetByName("Schedule");

  var editedRange = crrnbdSheet.getRange("H9:AI");
  var editedValues = editedRange.getValues();
  var scheduleRange = scheduleSheet.getRange("H9:AI");

  scheduleRange.setValues(editedValues);
}

• 行匹配错误：「CRR-NBD FMS」中的行是筛选后的结果，与「Schedule」表的行并非一一对应，直接将H9:AI全量覆盖到源表同范围会导致数据错乱。
• 效率低下：每次编辑都同步整个范围，而非仅修改的单元格。
• 无范围校验：未判断编辑操作是否发生在目标范围（H9:AI）内，可能触发不必要的同步。

以下代码通过**匹配唯一标识（月份+联系人，假设「CRR-NBD FMS」的B列是联系人，对应「Schedule」的B列）**定位源表行，仅同步修改的单元格：

function onEdit(e) {
  const ss = e.source;
  const editedSheet = ss.getActiveSheet();
  const editedCell = e.range;

  // 校验是否在目标工作表和范围内
  if (editedSheet.getName() !== "CRR-NBD FMS") return;
  const col = editedCell.getColumn();
  const row = editedCell.getRow();
  if (row < 9 || col < 8 || col > 35) return; // H是第8列，AI是第35列

  // 获取当前编辑行的匹配标识：月份（A列）+联系人（B列）
  const targetMonth = editedSheet.getRange(row, 1).getValue();
  const targetContact = editedSheet.getRange(row, 2).getValue();
  if (!targetMonth || !targetContact) return;

  // 定位源表中对应的行
  const scheduleSheet = ss.getSheetByName("Schedule");
  const scheduleData = scheduleSheet.getDataRange().getValues();
  let targetRowInSchedule = -1;

  for (let i = 0; i < scheduleData.length; i++) {
    const sheetMonth = scheduleData[i][0];
    const sheetContact = scheduleData[i][1];
    if (sheetMonth === targetMonth && sheetContact === targetContact) {
      targetRowInSchedule = i + 1; // 转换为工作表行号（从1开始）
      break;
    }
  }

  // 找到对应行后同步复选框值
  if (targetRowInSchedule !== -1) {
    const targetColInSchedule = col; // 假设H-AI列在两张表中位置一致
    scheduleSheet.getRange(targetRowInSchedule, targetColInSchedule).setValue(editedCell.getValue());
  }
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('自定义菜单')
    .addItem('手动同步Schedule', 'manualSyncSchedule')
    .addToUi();
}

// 手动同步整个范围的辅助函数
function manualSyncSchedule() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const crrnbdSheet = ss.getSheetByName("CRR-NBD FMS");
  const scheduleSheet = ss.getSheetByName("Schedule");

  // 获取CRR-NBD FMS中H9:AI的数据及对应匹配标识
  const dataRange = crrnbdSheet.getRange(9, 1, crrnbdSheet.getLastRow() - 8, 35); // A9:AI最后一行
  const allData = dataRange.getValues();
  const scheduleData = scheduleSheet.getDataRange().getValues();
  const scheduleRowMap = new Map();

  // 先构建源表的月份+联系人到行号的映射，提升效率
  for (let i = 0; i < scheduleData.length; i++) {
    const key = `${scheduleData[i][0]}|${scheduleData[i][1]}`;
    scheduleRowMap.set(key, i + 1);
  }

  // 遍历每一行同步数据
  allData.forEach((row, idx) => {
    const currentRow = idx + 9;
    const month = row[0];
    const contact = row[1];
    if (!month || !contact) return;

    const key = `${month}|${contact}`;
    const targetRow = scheduleRowMap.get(key);
    if (targetRow) {
      // 同步H-AI列（索引7到34，对应第8到35列）
      const checkboxValues = row.slice(7, 35);
      scheduleSheet.getRange(targetRow, 8, 1, 28).setValues([checkboxValues]);
    }
  });
}

1. onEdit触发器：
   • 仅当编辑操作发生在「CRR-NBD FMS」的H9:AI范围内时触发。
   • 通过当前行的**月份（A列）+联系人（B列）**作为唯一标识，在源表中查找对应行。
   • 仅同步修改的单个单元格值，避免全量覆盖。
2. 手动同步函数：
   • 提供手动批量同步的选项，适合一次性更新所有复选框状态。
   • 先构建源表的标识映射，提升遍历匹配的效率。

注意：如果联系人列表的唯一标识不是「月份+联系人」，请根据实际情况修改匹配逻辑（比如使用唯一ID列）。

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