当下拉菜单使用应用程序脚本更改为“已锁定”时保护行

1 回答

• 1

  将此代码添加到电子表格的脚本编辑器中，并为下面提到的函数设置onEdit触发器 .

  将代码中的email@gmail.com字符串替换为您的实际电子邮件

  注意：此脚本假定工作表的第一行可能有三个选项，具体取决于控制该范围的保护 .

  锁定：锁定特定列

  解锁：解锁特定列

  空白：什么都不做

  function onEdit(e){
    var x = [e.range.columnStart,e.range.rowStart,e.range.columnEnd,e.range.rowEnd]
    if (x[0]==x[2] && x[1]==1 && x[3]==1){
      var lockRangeStart = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,x[0]).getA1Notation();
      var lastCol = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getLastRow();
      var lockRangeName = (lockRangeStart+":"+lockRangeStart.match(/(\D*)/)[1]+lastCol);
      var lockRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(lockRangeName);
      if(e.value == "Lock"){
        var protection = lockRange.protect().setDescription('Locked Range');
        protection.addEditor("email@gmail.com");
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }
      }
      else  if(e.value == "Unlock") {
        var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
        var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
        for (var i = 0; i < protections.length; i++) {
          var protection = protections[i];
          if (protection.canEdit() && protection.getRange().getA1Notation() == lockRangeName ) {
            protection.remove();
          }
        }
      }
    }
  }
  

  回复于 2024-05-15T12:21:59+08:00