Google Script指定单元格范围onEdit触发失效问题求助

问题背景 #

我有一个名为“Steps”的工作表，计划通过下拉菜单实现交互：当选中Trenching、Chemical Use、Fall Protection等特定选项时，弹出嵌入Google表单的侧边栏。目前侧边栏及表单UI功能正常，但onEdit()函数无法触发或触发异常，需排查原因。

原代码 #

function onEdit(e) {

var editRange = { // G13:K136
  top : 13,     //first row to check
  bottom : 136, //last row to check
  left : 7,     //first column to check (G)
  right : 11    // last column to check (K)
};

var myRow = e.range.getRow();
if (myRow >= editRange.top || myRow <= editRange.bottom) return;

var myCol = e.range.getColumn();
if (myCol >= editRange.left || myCol <= editRange.right) return;

if(e.Value == "Trenching") 
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Trenching.html").setTitle("Trenching / Excavation"));}
  else if (e.Value == "Pressurized Lines")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Pressure.html").setTitle("Pressure Testing"));}
  else if (e.Value == "Chemical Use")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Chemical.html").setTitle("Chemical Use"));}
  else if (e.Value == "Fall Protection")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Falls.html").setTitle("Fall Protection"));}
  else if (e.Value == "Confined Space")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Confined.html").setTitle("Confined Spaces"));}
  else if (e.Value == "Lockout-Tagout")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("LOTO.html").setTitle("Lockout-Tagout"));}
  else if (e.Value == "Rigging")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Rig.html").setTitle("Rigging"));}
  else if (e.Value == "Heavy Machinery")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Heavy.html").setTitle("Heavy Machinery"));}
  else if (e.Value == "Hot Work")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Hot.html").setTitle("Hot Work"));}
  else if (e.Value == "Pipe Connections")
{SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Pipe.html").setTitle("Pipe Connections"));}
}

错误原因分析 #

1. 范围判断逻辑完全反转
   原代码中，行和列的判断条件逻辑错误：

   // 错误逻辑：只要行在13到136之间，就直接退出函数
   if (myRow >= editRange.top || myRow <= editRange.bottom) return;
   // 正确逻辑：行不在13-136范围内才退出
   if (myRow < editRange.top || myRow > editRange.bottom) return;
   

   这直接导致在目标编辑区域（G13:K136）内的操作被拦截，无法执行后续侧边栏逻辑。

2. 事件对象属性拼写错误
   Google Apps Script的onEdit事件对象中，单元格值的属性是小写的e.value，原代码写成大写e.Value，会导致无法读取下拉选项值，所有匹配条件都不成立。

3. 未限制目标工作表
   原代码没有判断当前编辑的是否是“Steps”工作表，其他工作表的编辑也会触发该函数，可能引发不必要的异常。

修复后的代码 #

function onEdit(e) {
  // 仅在"Steps"工作表中触发
  if (e.range.getSheet().getName() !== "Steps") return;

  var editRange = { // G13:K136
    top: 13,
    bottom: 136,
    left: 7,
    right: 11
  };

  var myRow = e.range.getRow();
  // 行不在目标范围内则退出
  if (myRow < editRange.top || myRow > editRange.bottom) return;

  var myCol = e.range.getColumn();
  // 列不在目标范围内则退出
  if (myCol < editRange.left || myCol > editRange.right) return;

  // 用小写e.value读取选中值，用switch简化逻辑
  switch(e.value) {
    case "Trenching":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Trenching.html").setTitle("Trenching / Excavation"));
      break;
    case "Pressurized Lines":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Pressure.html").setTitle("Pressure Testing"));
      break;
    case "Chemical Use":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Chemical.html").setTitle("Chemical Use"));
      break;
    case "Fall Protection":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Falls.html").setTitle("Fall Protection"));
      break;
    case "Confined Space":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Confined.html").setTitle("Confined Spaces"));
      break;
    case "Lockout-Tagout":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("LOTO.html").setTitle("Lockout-Tagout"));
      break;
    case "Rigging":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Rig.html").setTitle("Rigging"));
      break;
    case "Heavy Machinery":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Heavy.html").setTitle("Heavy Machinery"));
      break;
    case "Hot Work":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Hot.html").setTitle("Hot Work"));
      break;
    case "Pipe Connections":
      SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("Pipe.html").setTitle("Pipe Connections"));
      break;
  }
}

补充说明 #

• 用switch替代多个else if，逻辑更清晰易维护；
• 新增工作表判断，确保仅在目标工作表中触发；
• 修正范围判断逻辑和事件对象属性拼写，保证函数能正确响应目标区域的下拉选择。

内容的提问来源于stack exchange，提问作者J.Raab