如何嵌套/合并Google Apps Script的onEdit()函数？解决标识符重复问题

错误原因 #

你在同一个onEdit函数作用域内，重复使用const声明了sheet、sheetName、sheetConfig等变量。JavaScript中，const/let声明的变量不允许在同一作用域内重复定义，这就是报错的直接原因。

修复方案 #

推荐将两个逻辑块封装为独立函数，既解决变量冲突问题，也让代码结构更清晰、易维护：

function onEdit(e) {
  onEditTimeValue(e);
  onEditHourValue(e);
}

// 处理时间值编辑逻辑
function onEditTimeValue(e) {
  const sheet = e.range.getSheet();
  const sheetName = sheet.getName();
  const sheetConfig = { 
    "Sun": { targetColumn: 4 }, // Column D
    "Mon": { targetColumn: 8 }  // Column H
  };
  if (!(sheetName in sheetConfig)) return;
  const scheduleSheet = e.source.getSheetByName("Scheduler");
  const range = e.range;
  const column = range.getColumn();
  const row = range.getRow();
  if (row < 3 || (column !== 9 && column !== 13)) return;
  const nameColumn = column + 1;
  const nameCell = sheet.getRange(row, nameColumn).getValue();
  const updatedValue = range.getValue();
  if (!nameCell) return;
  const scheduleNames = scheduleSheet.getRange("A3:A").getValues().flat();
  const scheduleRow = scheduleNames.indexOf(nameCell);
  if (scheduleRow === -1) return;
  const targetColumn = sheetConfig[sheetName].targetColumn;
  scheduleSheet.getRange(scheduleRow + 3, targetColumn).setValue(updatedValue);
}

// 处理小时值编辑逻辑
function onEditHourValue(e) {
  const sheet = e.range.getSheet();
  const sheetName = sheet.getName();
  const sheetConfig = { 
    "Sun": { targetColumn: 5 }, // Column E
    "Mon": { targetColumn: 9 }  // Column I
  };
  if (!(sheetName in sheetConfig)) return;
  const scheduleSheet = e.source.getSheetByName("Scheduler");
  const range = e.range;
  const column = range.getColumn();
  const row = range.getRow();
  if (row < 3 || (column !== 12 && column !== 16)) return;
  const nameColumn = column - 2;
  const nameCell = sheet.getRange(row, nameColumn).getValue();
  const updatedValue = range.getValue();
  if (!nameCell) return;
  const scheduleNames = scheduleSheet.getRange("A3:A").getValues().flat();
  const scheduleRow = scheduleNames.indexOf(nameCell);
  if (scheduleRow === -1) return;
  const targetColumn = sheetConfig[sheetName].targetColumn;
  scheduleSheet.getRange(scheduleRow + 3, targetColumn).setValue(updatedValue);
}

替代方案（重命名变量） #

如果不想拆分函数，也可以给重复变量添加唯一后缀来区分：

function onEdit(e) {
  // 时间值处理逻辑
  const sheetTime = e.range.getSheet();
  const sheetNameTime = sheetTime.getName();
  const sheetConfigTime = { 
    "Sun": { targetColumn: 4 }, // Column D
    "Mon": { targetColumn: 8 }  // Column H
  };
  if (!(sheetNameTime in sheetConfigTime)) return;
  const scheduleSheetTime = e.source.getSheetByName("Scheduler");
  const rangeTime = e.range;
  const columnTime = rangeTime.getColumn();
  const rowTime = rangeTime.getRow();
  if (rowTime < 3 || (columnTime !== 9 && columnTime !== 13)) return;
  const nameColumnTime = columnTime + 1;
  const nameCellTime = sheetTime.getRange(rowTime, nameColumnTime).getValue();
  const updatedValueTime = rangeTime.getValue();
  if (!nameCellTime) return;
  const scheduleNamesTime = scheduleSheetTime.getRange("A3:A").getValues().flat();
  const scheduleRowTime = scheduleNamesTime.indexOf(nameCellTime);
  if (scheduleRowTime === -1) return;
  const targetColumnTime = sheetConfigTime[sheetNameTime].targetColumn;
  scheduleSheetTime.getRange(scheduleRowTime + 3, targetColumnTime).setValue(updatedValueTime);

  // 小时值处理逻辑
  const sheetHour = e.range.getSheet();
  const sheetNameHour = sheetHour.getName();
  const sheetConfigHour = { 
    "Sun": { targetColumn: 5 }, // Column E
    "Mon": { targetColumn: 9 }  // Column I
  };
  if (!(sheetNameHour in sheetConfigHour)) return;
  const scheduleSheetHour = e.source.getSheetByName("Scheduler");
  const rangeHour = e.range;
  const columnHour = rangeHour.getColumn();
  const rowHour = rangeHour.getRow();
  if (rowHour < 3 || (columnHour !== 12 && columnHour !== 16)) return;
  const nameColumnHour = columnHour - 2;
  const nameCellHour = sheetHour.getRange(rowHour, nameColumnHour).getValue();
  const updatedValueHour = rangeHour.getValue();
  if (!nameCellHour) return;
  const scheduleNamesHour = scheduleSheetHour.getRange("A3:A").getValues().flat();
  const scheduleRowHour = scheduleNamesHour.indexOf(nameCellHour);
  if (scheduleRowHour === -1) return;
  const targetColumnHour = sheetConfigHour[sheetNameHour].targetColumn;
  scheduleSheetHour.getRange(scheduleRowHour + 3, targetColumnHour).setValue(updatedValueHour);
}

说明 #

• 拆分函数的方案更优，不仅彻底解决变量冲突，还让代码模块化，后续修改和扩展更方便。
• 重命名变量的方案通过给变量添加Time/Hour后缀生成唯一标识符，快速解决重复声明问题，但代码冗余度较高。

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