如何用Apps Script保护Google Sheets并批量解锁指定单元格和范围

前置疑问答复 #

• 新增工作表自动应用：可通过配置Apps Script的onChange触发器实现，触发条件选择「新增工作表」，即可在每次新建工作表后自动运行保护规则，无需手动执行。
• 现有手动保护规则的影响：如果目标工作表已存在全表保护规则，重复调用sheet.protect()会报错，脚本内已新增旧保护清理逻辑，不会冲突。
• 运行通知：脚本全程后台静默执行，不会向编辑用户发送任何通知，仅当用户尝试编辑锁定区域时，才会弹出系统默认的权限不足提示。

代码报错原因 #

你调用getRange时传入多个范围字符串的写法不符合方法规范，getRange单次仅支持生成单个范围对象，批量范围需要先遍历范围字符串数组，逐一生成范围对象后再统一传入。

完整可运行代码 #

// 配置项：允许编辑的范围列表
const ALLOW_EDIT_RANGES = [
  'B3:U27', 'W3:AP27', 'E29:E31', 'I29:I31', 'M29:M31', 'Q29:Q31', 'U29:U31', 'Z29:Z31', 'AD29:AD31', 'AH29:AH31', 'AL29:AL31', 'AP29:AP31', 
  'B29', 'F29', 'J29', 'N29', 'R29', 'W29', 'AA29', 'AE29', 'AI29', 'AM29', 'C29', 'G29', 'K29', 'O29', 'S29', 'X29', 'AB29', 'AF29', 'AJ29', 'AN29', 
  'D29', 'H29', 'L29', 'P29', 'T29', 'Y29', 'AC29', 'AG29', 'AK29', 'AO29', 'B31', 'F31', 'J31', 'N31', 'R31', 'W31', 'AA31', 'AE31', 'AI31', 'AM31', 
  'C31', 'G31', 'K31', 'O31', 'S31', 'X31', 'AB31', 'AF31', 'AJ31', 'AN31', 'D31', 'H31', 'L31', 'P31', 'T31', 'Y31', 'AC31', 'AG31', 'AK31', 'AO31', 
  'B33:C33', 'F33:G33', 'J33:K33', 'N33:O33', 'R33:S33', 'W33:X33', 'AA33:AB33', 'AE33:AF33', 'AI33:AJ33', 'AM33:AN33' ,'D33:E33', 'H33:I33', 
  'L33:M33', 'P33:Q33', 'T33:U33', 'Y33:Z33', 'AC33:AD33', 'AG33:AH33', 'AK33:AL33', 'AO33:AP33'
];
// 配置项：需要跳过保护的工作表名称关键词，不需要则留空数组
const SKIP_SHEETS = [];

// 批量处理所有现有工作表的保护规则
function protectAllSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 保留表格原有所有编辑用户的权限
  const validEditors = ss.getEditors().map(user => user.getEmail());
  const allSheets = ss.getSheets();
  
  allSheets.forEach(sheet => {
    // 跳过指定工作表
    if (SKIP_SHEETS.some(keyword => sheet.getName().includes(keyword))) return;
    // 清理该表已有的全表保护规则，避免冲突
    const existingProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
    existingProtections.forEach(pro => pro.remove());
    
    // 新建全表保护
    const protection = sheet.protect().setDescription('全表锁定，仅开放指定可编辑范围');
    // 生成所有可编辑范围对象数组
    const unprotectedRanges = ALLOW_EDIT_RANGES.map(rangeStr => sheet.getRange(rangeStr));
    protection.setUnprotectedRanges(unprotectedRanges);
    
    // 同步原有编辑权限
    protection.addEditors(validEditors);
    // 移除不在编辑列表内的异常权限
    const currentEditors = protection.getEditors().map(user => user.getEmail());
    const toRemove = currentEditors.filter(email => !validEditors.includes(email));
    protection.removeEditors(toRemove);
    // 关闭域编辑权限（企业域账号适用）
    if (protection.canDomainEdit()) protection.setDomainEdit(false);
  });
}

// 单工作表保护函数，供新增工作表触发器调用
function protectNewSheet(e) {
  if (e.changeType === 'INSERT_GRID') {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const validEditors = ss.getEditors().map(user => user.getEmail());
    const newSheet = e.source.getActiveSheet();
    
    // 清理默认保护
    const existingProtections = newSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
    existingProtections.forEach(pro => pro.remove());
    
    const protection = newSheet.protect().setDescription('全表锁定，仅开放指定可编辑范围');
    const unprotectedRanges = ALLOW_EDIT_RANGES.map(rangeStr => newSheet.getRange(rangeStr));
    protection.setUnprotectedRanges(unprotectedRanges);
    
    protection.addEditors(validEditors);
    const currentEditors = protection.getEditors().map(user => user.getEmail());
    const toRemove = currentEditors.filter(email => !validEditors.includes(email));
    protection.removeEditors(toRemove);
    if (protection.canDomainEdit()) protection.setDomainEdit(false);
  }
}

使用说明 #

1. 首次运行先执行protectAllSheets函数，给所有现有工作表批量设置保护规则，运行时按照提示授权脚本权限即可。
2. 要实现新增工作表自动应用规则，按以下步骤配置触发器：
   • 点击Apps Script编辑器左侧「触发器」按钮
   • 点击「添加触发器」
   • 选择要运行的函数为protectNewSheet
   • 选择事件源为「电子表格」
   • 选择事件类型为「变更」
   • 保存配置即可。

内容的提问来源于stack exchange，提问作者Ádám Hegedüs