Google Sheets onEdit()中e.source返回首个工作表而非编辑表的问题

问题背景 #

我有一张用于库存和定价的Google Sheet，同时创建了另一张表格，通过ImportRange()从原表导入不含定价的批发库存内容给客户查看。但ImportRange()只会复制内容，不保留原表格式，所以我用onEdit()触发器脚本同步格式，脚本绑定在原表上。

当前使用的setFormatOnEdit()脚本：

function setFormatOnEdit(e) {
  if (!e)
    throw new Error("This function is automatically called, do not run this manually")
  const lock = LockService.getScriptLock()
  if (lock.tryLock(350000)) {
    try {
      const {
        master,
        sources
      } = variables_();
      const { range, source } = e;
      const { editRange } = sources.find(({ sheetName }) => sheetName == source.getSheetName());
      const srcRange = source.getRange(editRange);
      const editedSheetName = range.getSheet().getSheetName();
      if (editedSheetName != srcRange.getSheet().getSheetName())
        return;
      //Reformatting Code Here
    } catch ({stack}) {
      console.error(stack)
    } finally {
      lock.releaseLock();
      console.log("Done");
    }
  } else { console.error("Timeout") }
}

variables_()函数：

function variables_() {
  const masterSpreadsheetId = "###"

  const sourceSheetName1 = "Mixed Oak"
  ...
  const sourceSheetName13 = "Misc/Random Inventory"

  const sourceSheet1Range = "Mixed Oak!A1:I"
  ...
  const sourceSheet13Range = "Misc/Random Inventory!A1:I"

  return {
    master: { masterSpreadsheetId },
    sources: [
      { sheetName: sourceSheetName1, editRange: sourceSheet1Range },
      ...
      { sheetName: sourceSheetName13, editRange: sourceSheet13Range }
    ]
  };
}

遇到的问题：无论编辑原表中的哪个工作表，e.source始终返回原表的首个工作表（定价汇总表，不需要同步），导致无法根据实际编辑的工作表匹配同步目标表格式。

核心问题分析 #

代码里的错误逻辑：
const { editRange } = sources.find(({ sheetName }) => sheetName == source.getSheetName());
这里的e.source是整个电子表格对象，不是被编辑的工作表。调用source.getSheetName()会返回电子表格默认激活的工作表（也就是第一个工作表），这就是为什么不管编辑哪个表，都只会匹配到定价汇总表的原因。

修复后的脚本 #

修改关键逻辑，用range.getSheet()获取实际被编辑的工作表对象，调整后的setFormatOnEdit()函数：

function setFormatOnEdit(e) {
  if (!e)
    throw new Error("This function is automatically called, do not run this manually")
  const lock = LockService.getScriptLock()
  if (lock.tryLock(350000)) {
    try {
      const { master, sources } = variables_();
      const { range } = e;
      const editedSheet = range.getSheet();
      const editedSheetName = editedSheet.getName();

      // 匹配当前编辑工作表的配置，没找到直接返回（比如编辑的是定价汇总表）
      const sourceConfig = sources.find(({ sheetName }) => sheetName === editedSheetName);
      if (!sourceConfig) return;

      const { editRange } = sourceConfig;
      const srcRange = editedSheet.getRange(editRange.split('!')[1]); // 提取范围部分

      // 这里执行你的格式化逻辑，比如同步到目标表
      const targetSpreadsheet = SpreadsheetApp.openById(master.masterSpreadsheetId);
      const targetSheet = targetSpreadsheet.getSheetByName(editedSheetName);
      if (targetSheet) {
        const targetRange = targetSheet.getRange(srcRange.getA1Notation());
        // 直接复制格式，替代自定义格式化代码
        srcRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.FORMAT_ONLY);
      }

    } catch ({stack}) {
      console.error(stack)
    } finally {
      lock.releaseLock();
      console.log("Done");
    }
  } else { console.error("Timeout") }
}

更优格式同步方案 #

1. 直接用copyTo()复制格式：上面修复后的代码里已经用到，用copyTo(destRange, SpreadsheetApp.CopyPasteType.FORMAT_ONLY)可以一键复制格式，比自己写格式化逻辑高效且不易出错。
2. 限制触发器触发范围：可以在脚本里判断编辑的范围是否在editRange内，只有当编辑的单元格属于需要同步的范围时才执行格式复制，减少不必要的脚本运行。
3. 避免遍历行：不要用条件格式遍历所有行，copyTo()是批量操作，速度更快。

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