为Google Sheets编写脚本实现三个定时更新的不同范围保护

以下是满足需求的Google Apps Script实现，包含自动移除旧保护、动态生成新保护范围，以及配置时间驱动触发器的步骤：

完整脚本代码 #

function manageSheetProtections() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getActiveSheet(); // 若需指定特定工作表，替换为getSheetByName("工作表名称")
  
  // 移除所有旧的保护规则
  const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  protections.forEach(protection => {
    if (protection.canEdit()) {
      protection.remove();
    }
  });

  // 获取B列最后一行数据行号
  const lastRow = sheet.getRange("B:B").getValues().filter(String).length;
  if (lastRow < 2) {
    // 若B列无有效数据（起始行是2），直接退出
    return;
  }

  // 定义需要保护的三个范围
  const protectedRanges = [
    sheet.getRange(2, 2, lastRow - 1, 2), // B-C列：起始行2，列2，行数lastRow-1，列数2
    sheet.getRange(2, 5, lastRow - 1, 1), // E列：起始行2，列5，行数lastRow-1，列数1
    sheet.getRange(2, 7, lastRow - 1, 2)  // G-H列：起始行2，列7，行数lastRow-1，列数2
  ];

  // 为每个范围设置保护并指定允许编辑的账号
  const allowedEditors = ["abc@abc.com", "xyz@xyz.com"];
  protectedRanges.forEach(range => {
    const protection = range.protect().setDescription("自动生成的范围保护");
    // 移除默认的编辑权限（仅保留指定账号）
    const editors = protection.getEditors();
    protection.removeEditors(editors);
    // 添加允许编辑的账号
    protection.addEditors(allowedEditors);
    // 若需要禁止所有者以外的用户删除保护，可取消以下注释
    // protection.setWarningOnly(false);
  });
}

// 用于创建每日凌晨2点的时间驱动触发器
function createDailyTrigger() {
  // 先删除已有的同名触发器，避免重复
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === "manageSheetProtections") {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  // 创建每日凌晨2点的触发器
  ScriptApp.newTrigger("manageSheetProtections")
    .timeBased()
    .everyDays(1)
    .atHour(2)
    .create();
}

使用步骤 #

• 粘贴脚本：打开目标Google Sheets，点击菜单栏「扩展程序」→「Apps 脚本」，清空默认代码后粘贴上述脚本。
• 指定工作表（可选）：如果需要针对特定工作表而非当前活动表，将getActiveSheet()替换为getSheetByName("你的工作表名称")。
• 初始化验证：先手动运行一次manageSheetProtections函数，确认保护规则正常生成（首次运行需完成脚本授权）。
• 创建自动触发器：运行createDailyTrigger函数，即可自动创建每日凌晨2点执行的触发器；也可手动创建：
  • 在脚本编辑器左侧点击「触发器」图标 →「添加触发器」
  • 选择运行函数为manageSheetProtections，事件源选「时间驱动」，类型选「日计时器」，时间指定为凌晨2点。

关键逻辑说明 #

• 旧保护清理：遍历工作表所有范围保护，移除可编辑的旧规则，确保每次运行都是全新的保护配置。
• 动态范围计算：通过过滤B列非空值获取最后一行，保证保护范围始终覆盖到最新数据行。
• 权限严格控制：移除所有默认编辑者后，仅添加指定的两个账号，精准限制编辑权限。

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