Google脚本锁定单元 [英] Google Script Lock Cells

问题描述

我正在尝试实现一个脚本，该脚本在条件为true时锁定一定范围的单元格.这是我的文档的链接:

I am trying to implement a script that locks a certain range of cells when a condition is true. Here is the link to my document:

https://docs.google.com/spreadsheets/d/1XShGxlz2fA2w2omth-TvYc7cK0nXvVMrhwRKafzVjOA/edit?usp = sharing

基本上，我是与一群人共享此文档的，因此他们在B栏中填写了他们的邮件地址，并在C栏中输入了数字1，从而使每个插槽的计数器都增加了.我想做的是在每个插槽都装满后锁定它们，以便其他人无法再编辑这些插槽，但是问题出在我的if语句 if(cell1 == 10)上.即使未实现if条件，该范围也始终被锁定.这是我的代码:

Basically I am sharing this document with a group of people so they fill their mail addresses in column B and put a number 1 in column C so it increments my counters for each slot. What I am trying to do is to lock each slot when it is full so other people can no more edit these slots but the problem is with my if statement if (cell1 == 10). The range is always locked even if the if condition is not realized. Here is my code :

function onOpen() {


  var ss = SpreadsheetApp.getActive();
  var source =         SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

 var cell=60;
 var cell1 = source.getRange("G2").getValue();

 if (cell1 == 10){

// Protect range B2:B11, then remove all other users from the list of editors.
var ss = SpreadsheetApp.getActive();
var range = ss.getRange('B2:B11');
var protection = range.protect().setDescription('Sample protected range');

// Ensure the current user is an editor before removing others. Otherwise, if the user's edit
// permission comes from a group, the script will throw an exception upon removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}

  }


}

推荐答案

正如安迪(Andy)在评论中所说，如果单元格G2不等于10，则需要显式删除保护.(此代码删除了所有保护).

As Andy says in the comments, you need to explicitly remove the protection if cell G2 does not equal 10. (This code removes all protections).

阅读保护类页，您可以从中获取摘要，我无法了解编辑器特权将如何影响您的需求，因此，如果其他人是编辑者，则此脚本将起作用.如果您不希望他们成为编辑者，则必须添加相关代码.

Reading the Protection Class page, where you got the snippets from, I couldn't get a handle on the way editor privileges would factor into your needs, so this script will work if others are editors. If you don't want them to be editors, you'll have to add the relevant code.

function onOpen() {

  var ss = SpreadsheetApp.getActive();
  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var cell = source.getRange("G2").getValue();
  var range = ss.getRange('B2:B11');

  if (cell == 10) {

    // Protect range B2:B11 if cell 'G2' = 10
    var protection = range.protect().setDescription('Sample protected range');
    Logger.log

  } else {

    // Remove protection if cell 'G2' is anything other than 10
    var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);

    for (var i = 0; i < protections.length; i++) {
      var protection = protections[i];
      protection.remove();
    }
  }  
} 

这篇关于Google脚本锁定单元的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！