为何改用Range替代单个单元格数组的onEdit脚本无法生效？

问题 #

我有一段能针对A2-A4单元格正常运行的代码，它的onEdit函数可以自动恢复工作表MySheet中误删的复选框，代码如下：

//The function onEdit ensures that checkboxes deleted  (by mistake) in the sheet are immediately re-created.
function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActive();
  if(spreadsheet.getSheetName()=='MySheet') {   //to avoid executing for another sheet
    var checkboxCells = [
      'A2','A3','A4'];   
    var range = e.range;
    var value = range.getValue();
    var a1Notation = range.getA1Notation();
    if (checkboxCells.indexOf(a1Notation) != -1 && value != 'TRUE' && value != 'FALSE') {
      range.insertCheckboxes();     
    }  
  }
};

但因为涉及大量单元格，我尝试改用一维命名范围MyRange，修改后的代码如下却无法正常工作：

//The function onEdit ensures that checkboxes deleted  (by mistake) in the sheet are immediately re-created.
function onEdit(e) {
  var spreadsheet = SpreadsheetApp.getActive();
  if(spreadsheet.getSheetName()=='MySheet') {   //to avoid executing for another sheet
    var checkboxCells =  spreadsheet.getSheetByName('MySheet')
                                    .getRange('MyRange').getA1Notation();
    var range = e.range;
    var value = range.getValue();
    var a1Notation = range.getA1Notation();
    if (checkboxCells.indexOf(a1Notation) != -1 && value != 'TRUE' && value != 'FALSE') {
      range.insertCheckboxes();     
    }  
  }
};

请问这是为什么？

问题原因 #

核心问题出在checkboxCells的类型和内容上：

• 原代码里checkboxCells是单个单元格A1符号的数组（比如['A2','A3','A4']），能通过indexOf准确判断编辑的单元格是否在目标范围内。
• 修改后，getRange('MyRange').getA1Notation()返回的是整个命名范围的A1字符串（比如MyRange是A2到A10的话，会返回"A2:A10"），这是一个单一字符串而非数组。用单个单元格的A1符号（比如"A3"）去调用这个字符串的indexOf，根本找不到匹配项，导致条件永远不成立，代码无法执行。

修正方案 #

需要把命名范围内每个单元格的A1符号提取成数组，才能适配原代码的逻辑。修改后的代码如下：

// 自动恢复MySheet中误删的复选框
function onEdit(e) {
  var sheet = e.source.getSheetByName('MySheet');
  if (!sheet || e.range.getSheet() !== sheet) return;

  // 获取命名范围并提取所有单元格的A1符号数组
  var myRange = sheet.getRange('MyRange');
  var checkboxCells = [];
  var numRows = myRange.getNumRows();
  var numCols = myRange.getNumColumns();
  
  // 遍历范围内的所有单元格，收集A1符号
  for (var row = 1; row <= numRows; row++) {
    for (var col = 1; col <= numCols; col++) {
      checkboxCells.push(myRange.offset(row-1, col-1, 1, 1).getA1Notation());
    }
  }

  var editedA1 = e.range.getA1Notation();
  var editedValue = e.range.getValue();

  // 判断是否在目标范围内且值不是复选框的布尔值
  if (checkboxCells.includes(editedA1) && editedValue !== true && editedValue !== false) {
    e.range.insertCheckboxes();
  }
};

额外优化点：

• 用e.source获取触发编辑的表格，比SpreadsheetApp.getActive()更精准，避免切换表格时出错。
• 用includes()替代indexOf()，代码可读性更强。
• 提前判断工作表是否匹配，不符合直接返回，减少无效执行逻辑。

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