Google Sheets Apps Script保护设置公式识别与用户权限问题

核心问题解决逻辑 #

• 针对最后一行识别偏差问题：弃用原生getLastRow()直接取行的逻辑，遍历全表单元格区分公式/非公式内容，仅统计非公式列中手动输入的非空单元格所在行，取最大行号作为实际需保护的最后一行，自动忽略整列公式延伸出的无效空行。
• 针对指定用户可编辑需求：新增允许编辑的邮箱配置项，保护规则生效后自动将配置列表内的账号添加为受保护范围的编辑器，保留对应账号的编辑权限。

修改后完整可直接使用代码 #

function installedOnOpen(e) {
  // 配置项：需要设置自动保护的工作表名称，支持同时配置多个表
  const sheetNames = ["Sheet1"];
  // 配置项：允许编辑保护范围的用户邮箱列表，无需额外授权可留空数组
  const allowedEditorEmails = ["user1@example.com", "user2@example.com"];

  const sheets = e.source.getSheets().filter(s => sheetNames.includes(s.getSheetName()));
  if (sheets.length == 0) return;

  sheets.forEach(s => {
    // 清除该表历史范围保护规则，避免重复叠加
    const oldProtections = s.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    oldProtections.forEach(p => p.remove());

    const maxRow = s.getMaxRows();
    const maxCol = s.getMaxColumns();
    // 一次性读取全表值和公式信息，减少接口调用提升运行速度
    const allValues = s.getDataRange().getValues();
    const allFormulas = s.getDataRange().getFormulas();
    let actualLastRow = 0;

    // 遍历所有单元格，跳过公式单元格，仅统计手动输入内容的有效行
    for (let col = 0; col < maxCol; col++) {
      for (let row = 0; row < maxRow; row++) {
        if (allFormulas[row]?.[col] === "" && allValues[row]?.[col] !== "") {
          actualLastRow = Math.max(actualLastRow, row + 1);
        }
      }
    }

    if (actualLastRow != 0) {
      // 仅对有效手动输入内容的范围加保护
      const newProtect = s.getRange(1, 1, actualLastRow, maxCol).protect();
      // 先移除所有默认编辑器权限
      newProtect.removeEditors(newProtect.getEditors());
      // 回填配置内的允许编辑用户权限
      if (allowedEditorEmails.length > 0) {
        newProtect.addEditors(allowedEditorEmails);
      }
      // 关闭域内全体用户可编辑权限
      if (newProtect.canDomainEdit()) newProtect.setDomainEdit(false);
    }
  });
}

使用注意事项 #

• 使用前修改sheetNames数组，替换为你实际需要加保护的工作表名称
• 修改allowedEditorEmails数组，填入所有需要保留编辑权限的用户邮箱即可
• 原有打开触发的安装型触发器不需要重新配置，直接替换原有脚本代码即可生效
• 公式列的自动计算逻辑不受保护影响，有效保护行以下的空白区域所有用户均可正常输入新内容

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