Google Sheets错误输入后自动锁定失效 求助（附Apps Script代码）

原代码核心问题 #

• 仅弹出密码输入框，未实际执行工作表锁定/解锁操作，这是功能失效的关键。
• 判断条件value[i][0] === "FALSE"可能类型不匹配：若E列是布尔值FALSE，应直接比较=== false；若为文本型"FALSE"，才用字符串比较。
• 遍历所有行时，只要存在符合条件的单元格就重复调用protection()，会导致无限弹窗，逻辑不合理。

修正后的完整代码 #

function protection() {
  const ui = SpreadsheetApp.getUi();
  const pin = "0000";
  let attempt = "";

  // 限制最多3次密码尝试，避免无限循环
  for (let i = 0; i < 3; i++) {
    const response = ui.prompt("输入解锁密码");
    // 处理用户点击取消的情况
    if (response.getSelectedButton() === ui.Button.CANCEL) {
      ui.alert("解锁已取消");
      return;
    }
    attempt = response.getResponseText();
    if (attempt === pin) {
      const sheet = SpreadsheetApp.getActive().getSheetByName("Master");
      // 移除保护（解锁）
      const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
      if (protections.length > 0) {
        protections[0].remove();
      }
      ui.alert("工作表已解锁");
      return;
    } else if (i === 2) {
      ui.alert("密码错误次数过多，操作终止");
    }
  }
}

function duplicate() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Master");
  const lastRow = sheet.getLastRow();
  // 仅读取有数据的行，避免无效遍历
  const range = sheet.getRange(1, 5, lastRow); // E列，第1行到最后一行
  const values = range.getValues();
  let hasError = false;

  // 检查是否存在错误标记
  for (let i = 0; i < lastRow; i++) {
    // 根据E列实际数据类型调整：布尔值用===false，文本用==="FALSE"
    if (values[i][0] === false) {
      hasError = true;
      break; // 找到错误即停止遍历，提升效率
    }
  }

  if (hasError) {
    // 避免重复添加保护
    const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
    if (protections.length === 0) {
      const protection = sheet.protect();
      // 保留自身编辑权限，防止锁死自己
      const me = Session.getEffectiveUser();
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
      SpreadsheetApp.getUi().alert("检测到错误输入，工作表已锁定");
    }
  }
}

关键优化说明 #

• 真正实现锁定逻辑：添加sheet.protect()创建工作表保护，以及移除保护的代码，补上原代码缺失的核心功能。
• 错误检测优化：仅遍历有数据的行，找到第一个错误即停止，减少无效计算；修正值的类型判断逻辑。
• 密码交互优化：限制3次尝试上限，处理用户取消操作的场景，避免无限弹窗。
• 权限安全设置：锁定时保留自身编辑权限，防止误操作导致无法解锁表格。
• 重复操作防护：锁定前检查是否已有保护，避免重复执行保护操作。

触发器验证 #

确保触发器设置符合以下要求：

• 事件类型：电子表格编辑时
• 触发函数：duplicate
• 部署账户：你的Google账号

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