Google Sheets根据单元格值动态保护非整行局部范围实现方法

问题说明 #

本问题与Stack Overflow已有问题《Protecting/unprotecting range based on another cell value》场景不同：该问题针对整行保护需求，而本问题聚焦多高度、局部宽度的非整行范围保护，经全站检索无对应解决方案，请勿将本问题标记为上述问题的重复。

需求梳理 #

需在「Outgoing」订单工作表实现如下权限控制：

• 现有权限基线：工作表A1:AS全域已设置保护，仅文件所有者及editor1@email.com、editor2@email.com两位指定编辑可修改；仅A2:M882范围开放给其他编辑录入新订单。
• 触发规则：当O列（Date Sent/发货日期列）单元格填入日期或任意非空值时，对应行A列至M列的区域需设置保护，仅所有者及上述两位指定编辑拥有修改权限。
• 实现约束：禁止每次脚本触发时新增单行保护条目，避免产生数百个零散受保护范围；需动态调整单个保护范围：每次O列录入值后，将保护范围自动更新为A2至当前触发行M列的连续区域，始终仅保留这一个动态保护条目。

实现代码 #

直接使用绑定编辑触发器的Apps Script即可，仅需调整配置段参数即可适配工作表，无需从零开发：

function onEditDynamicProtect(e) {
  // ========== 可自行调整的配置项 ==========
  const TARGET_SHEET = "Outgoing";
  const TRIGGER_COL = 15; // 触发列O对应的列序号
  const PROTECT_COL_START = 1; // 保护范围起始列A
  const PROTECT_COL_END = 13; // 保护范围结束列M
  const PROTECT_ROW_START = 2; // 保护范围起始行
  const ALLOWED_EDITORS = ["editor1@email.com", "editor2@email.com"]; // 拥有修改权限的指定编辑邮箱
  // ======================================

  // 触发条件校验，不满足则直接退出
  const editRange = e.range;
  const activeSheet = editRange.getSheet();
  if (activeSheet.getName() !== TARGET_SHEET) return;
  if (editRange.columnStart !== TRIGGER_COL || editRange.rowStart < PROTECT_ROW_START) return;
  if (!e.value) return; // O列内容被清空时不触发保护范围更新

  // 查找已存在的动态保护条目，不存在则新建，存在则直接更新范围
  const allProtections = activeSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  let dynamicProtection = allProtections.find(item => item.getDescription() === "dynamic_sent_order_lock");
  const currentEndRow = editRange.rowStart;
  const updatedRange = activeSheet.getRange(
    PROTECT_ROW_START,
    PROTECT_COL_START,
    currentEndRow - PROTECT_ROW_START + 1,
    PROTECT_COL_END - PROTECT_COL_START + 1
  );

  if (!dynamicProtection) {
    dynamicProtection = updatedRange.protect().setDescription("dynamic_sent_order_lock");
  } else {
    dynamicProtection.setRange(updatedRange);
  }

  // 重置保护权限：仅保留所有者+配置内指定编辑的权限
  const existingEditors = dynamicProtection.getEditors().map(user => user.getEmail());
  dynamicProtection.removeEditors(existingEditors);
  dynamicProtection.addEditors(ALLOWED_EDITORS);
  dynamicProtection.setDomainEdit(false);
}

部署步骤 #

• 打开目标Google Sheets，点击顶部菜单栏「扩展程序」-「Apps 脚本」，进入脚本编辑器。
• 删除编辑器内默认的空函数，把上述代码完整粘贴进去，修改配置项内的指定编辑邮箱为实际使用的邮箱地址，点击顶部保存按钮为项目命名。
• 点击编辑器左侧边栏的时钟图标（触发器菜单），选择「添加触发器」，按如下参数配置：
  • 选择要运行的函数：onEditDynamicProtect
  • 选择部署版本：Head
  • 选择事件源：来自电子表格
  • 选择事件类型：编辑时
  • 失败通知设置按需选择即可
• 点击保存，按照弹窗提示完成脚本授权，功能即可生效。

注：该脚本逻辑会永久维护唯一标识为dynamic_sent_order_lock的保护条目，每次触发仅更新该条目的覆盖范围，不会生成零散的单行保护规则。

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