如何通过Google脚本按单元格颜色与大洲锁定Google Sheets编辑权限

实现目标 #

• 基础规则：仅锁定**包含非空内容、背景色为纯白色（色值#ffffff）**的单元格
• 叠加维度：结合单元格所属大洲匹配对应编辑权限，非对应大洲的授权用户无法编辑锁定单元格
• 兼容原有逻辑：可保留之前按指定背景色清空单元格内容的功能

原有代码问题 #

之前提供的代码仅实现了指定背景色单元格的内容清空，没有接入权限锁定逻辑，也未做白色非空单元格过滤、大洲维度权限匹配，还存在冗余的输入标记语法错误。 #

完整可运行代码 #

function lockDualDimensionCells() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Sheet1");

  // 按需修改以下配置项
  const CONFIG = {
    whiteCellColor: "#ffffff",
    continentColumn: 1, // 大洲字段所在列，1对应A列，B列填2，以此类推
    // 各大洲对应拥有编辑权限的用户邮箱列表
    continentAuthMap: {
      "亚洲": ["asia_edit@xxx.com"],
      "欧洲": ["eu_edit@xxx.com"],
      "北美洲": ["na_edit@xxx.com"],
      "南美洲": ["sa_edit@xxx.com"],
      "非洲": ["af_edit@xxx.com"],
      "大洋洲": ["oc_edit@xxx.com"]
    }
  }

  // 先清除历史保护规则，避免重复锁定
  const oldProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  oldProtections.forEach(rule => rule.remove());

  const dataRange = sheet.getDataRange();
  const bgColors = dataRange.getBackgrounds();
  const cellValues = dataRange.getValues();
  const totalRows = dataRange.getLastRow();
  const totalCols = dataRange.getLastColumn();

  // —— 如需保留原有「清空C23单元格取色对应背景色内容」的逻辑，取消下方注释即可 ——
  /*
  const clearTargetColor = sheet.getRange('C23').getBackground();
  const cellFormulas = dataRange.getFormulas();
  for (let i = 0; i < bgColors.length; i++) {
    for (let j = 0; j < bgColors[i].length; j++) {
      if (bgColors[i][j] === clearTargetColor) cellValues[i][j] = '';
    }
  }
  cellFormulas.forEach((row, rowIdx) => row.forEach((formula, colIdx) => {
    if (formula) cellValues[rowIdx][colIdx] = formula;
  }));
  dataRange.setValues(cellValues).setBackgrounds(bgColors);
  */

  // 遍历单元格执行锁定逻辑
  for (let row = 0; row < totalRows; row++) {
    // 获取当前行所属大洲
    const currentContinent = cellValues[row][CONFIG.continentColumn - 1]?.toString().trim();
    const allowedEmails = CONFIG.continentAuthMap[currentContinent] || [];

    for (let col = 0; col < totalCols; col++) {
      const cellVal = cellValues[row][col];
      const cellBg = bgColors[row][col].toLowerCase();
      // 判定是否为需要锁定的目标单元格：白色背景+非空内容
      const needLock = cellBg === CONFIG.whiteCellColor && cellVal !== "" && cellVal !== null;

      if (needLock) {
        const targetRange = sheet.getRange(row + 1, col + 1);
        const protectionRule = targetRange.protect()
          .setDescription(`锁定-${currentContinent}白格`);
        // 清空默认编辑权限
        protectionRule.removeEditors(protectionRule.getEditors().map(user => user.getEmail()));
        // 添加对应大洲授权用户
        if (allowedEmails.length) protectionRule.addEditors(allowedEmails);
        // 保留文档所有者编辑权限
        protectionRule.addEditor(Session.getEffectiveUser().getEmail());
      }
    }
  }
}

使用注意事项 #

• 首次运行脚本需要按弹窗提示完成Google账号授权，授予脚本表格编辑、权限管理的权限
• 配置项里的大洲列位置、对应授权邮箱必须和实际表格结构一致，否则权限匹配会失效
• 表格内容、单元格格式、人员权限调整后，重新运行一次脚本即可自动更新锁定规则

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