CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Leti
Leti

Reputation: 121

How to protect data with conditions?

I have a code :

function Lock(){
  var sh = SpreadsheetApp.openById(ssID).getSheetByName('SheetName');
  var protection = sh.protect().setDescription('DATA');
  var me = Session.getEffectiveUser();
  protection.addEditor(me).setUnprotectedRanges(sh.getRange("F2:F").createTextFinder("^(?!OK!).*$").matchEntireCell(true).useRegularExpression(true).findAll().map(r => r.offset(0, 0, 1, 2)));
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()){
  protection.setDomainEdit(false);
  }
}

This code working great. It will protect Row if column F:F is correct value is OK!. And now i want it to protect if the value is OK! or Failed!

.createTextFinder("^(?!OK!).*$")

Where should I modify it? i tried || but can not. Thanks!

Upvotes: 1

Views: 65

Answers (1)

Tanaike
Tanaike

Reputation: 201683

In that case, how about the following modification?

From:

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F2:F").createTextFinder("^(?!OK!).*$").matchEntireCell(true).useRegularExpression(true).findAll().map(r => r.offset(0, 0, 1, 2)));

To:

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F2:F").createTextFinder("^(?!(OK!|Failed!)).*$").matchEntireCell(true).useRegularExpression(true).findAll().map(r => r.offset(0, 0, 1, 2)));

and

protection.addEditor(me).setUnprotectedRanges(sh.getRange("F2:F").createTextFinder("^(?!OK!)(?!Failed!).*$").matchEntireCell(true).useRegularExpression(true).findAll().map(r => r.offset(0, 0, 1, 2)));
  • In this modification, ^(?!OK!).*$ is modified to ^(?!(OK!|Failed!)).*$ and ^(?!OK!)(?!Failed!).*$.

Upvotes: 1

Related Questions