Protect specified range in google Sheet with GAS

user_15645185 0
Viewed 85
function Lock(){

  var sh = SpreadsheetApp.openById(ssID).getSheetByName('6');
  var protection = sh.protect().setDescription('BVDATA');
  var me = Session.getEffectiveUser();
protection.addEditor(me).setUnprotectedRanges(sh.getRange("F1:F").createTextFinder("^(?!OK).*$").matchEntireCell(true).useRegularExpression(true).findAll());
  protection.removeEditors(protection.getEditors());

  if (protection.canDomainEdit()){
  protection.setDomainEdit(false);
  }
}

@Tanaike guided me to this. Now i have a problem, that code will automatically protect 1 cell F if it value is OK. Is there a way for me it will protect F:G(like F5:G5 will be protected if F5 value OK) if F has a value of OK?. I tried modification :

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F1:G").CreateTextFinder("^(?!OK).*$").MatchEntireCell(true).useRegularExpression(true).findAll());

but it only protects cell G when the value is OK.

google-apps-script google-sheets
1 Answers

I believe your goal as follows.

  • You want to protect the cells of the columns "F" and "G", only when the cell value is OK.

In order to achieve this, how about the following modification?

From:

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F1:F").createTextFinder("^(?!OK).*$").matchEntireCell(true).useRegularExpression(true).findAll());

To:

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F1:F").createTextFinder("^(?!OK).*$").matchEntireCell(true).useRegularExpression(true).findAll().map(r => r.offset(0, 0, 1, 2)));
  • In this modification, the searched range is expanded using offset.

Reference:

user_71086530
Related