Google Sheets列内复选框数量限制脚本故障排查求助

我想把Google Sheets某列中选中的复选框数量限制为17个，用Google Apps Script写了脚本，但勾选到17个后，对应区域没被锁定。

关键细节 #

• 表格第1列和前2行是标识信息，复选框从第2列第3行开始
• 每列最多允许17个选中的复选框，最后一行用COUNTIF(COLUMN CHECKBOX RANGE, TRUE)统计该列选中数量，以此作为限制依据
• 首次使用Google Apps Script，缺乏编程经验

我写的脚本（无报错但无法生效） #

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet Name'); 
  var lr = sheet.getLastRow(); // 获取COUNTIF所在的最后一行
  var lc= sheet.getLastColumn(); // 获取最后一列
  var range = sheet.getRange(lr, 2,1,lc-1); // 获取最后一行从第2列开始的单元格范围
  var data = range.getValues(); // 获取该范围的值
  
  for(var i = 2; i < data.length; i++) // 从第2列开始遍历所有列
  {
    var range2 = sheet.getRange(3,i,lr-2,1); // 获取第i列从第3行到倒数第二行的范围
    if( data[i] >= 17) // 检查该列最后一行的统计值是否≥17
    {
      range2.protect().setDescription('已达最大勾选数量'); // 保护该列的复选框区域
      range2.setBackground('#f4c7c3'); // 将该列背景设为红色
    }
  }
} 

问题分析与修正方案 #

1. 数组索引错误：data是二维数组，data[0]才对应最后一行的所有列数据，原脚本里data[i]取值逻辑错误，应该对应数组的索引偏移。
2. 循环范围错误：原循环起始值和数组长度不匹配，导致部分列没有被正确遍历。
3. 未处理解除保护场景：如果后续取消勾选使数量低于17，原脚本没有解除保护、恢复背景的逻辑。
4. 保护权限问题：默认保护会限制所有人编辑（包括脚本所有者），需要设置允许自己编辑的权限。

修正后的脚本 #

function onEdit(e) {
  const sheetName = 'Sheet Name'; // 替换成你的实际工作表名称
  const maxChecks = 17;
  const sheet = e.source.getSheetByName(sheetName);
  if (!sheet) return;

  // 获取编辑单元格的位置信息
  const editedCol = e.range.getColumn();
  const editedRow = e.range.getRow();
  const lastRow = sheet.getLastRow();

  // 只处理复选框区域（第2列及以后、第3行及以上、非最后统计行）
  if (editedCol < 2 || editedRow < 3 || editedRow === lastRow) return;

  // 获取当前列的选中数量统计值
  const countValue = sheet.getRange(lastRow, editedCol).getValue();
  const checkboxRange = sheet.getRange(3, editedCol, lastRow - 2, 1);

  if (countValue >= maxChecks) {
    // 创建保护并设置权限
    const protection = checkboxRange.protect();
    protection.setDescription('已达最大勾选数量（17个）');
    const currentUser = Session.getEffectiveUser();
    protection.addEditor(currentUser);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
    checkboxRange.setBackground('#f4c7c3');
  } else {
    // 数量低于限制时，解除对应列的保护并恢复背景
    const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    protections.forEach(protection => {
      if (protection.getDescription() === '已达最大勾选数量（17个）' && 
          protection.getRange().getColumn() === editedCol) {
        protection.remove();
        checkboxRange.setBackground(null);
      }
    });
  }
}

使用说明 #

1. 将脚本中的'Sheet Name'替换为你的工作表实际名称
2. 保存脚本后回到表格测试：当某列选中复选框数量达到17时，该列复选框区域会被保护，无法新增勾选；取消勾选使数量低于17时，保护自动解除，背景色恢复默认。

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