How add more unprotected ranges to sheet with some already unprotected ranges?

Question

I have a sheet that is protected with some unprotected ranges.

In the first phase as admin I unprotect some ranges with a SCRIPT 1. Unfortunately it takes around 5 min. to execute the script.

User can add a row to the sheet and the script should unprotect some cells in the added row. The problem is that I don't know how to unprotect range when there is already some ranges that are unprotected. At this point when user adds a row script runs a SCRIPT 1 that goes through whole sheet and it takes unfortunately too much time...

SCRIPT 1 - going through whole document (around 5min.)

function protect(sheet) {

var protection = sheet.protect().setDescription('Protected sheet');
var arrayRanges = new Array;

for(var i=1;i<sheet.getLastRow();i++){

if(sheet.getRange(i,50).getValue() == "s"){

  range = sheet.getRange(i,3,1,5);
  arrayRanges.push(range);
} else if (sheet.getRange(i,50).getValue() == "d"){
  range = sheet.getRange(i,2,1,6);
  arrayRanges.push(range);
} else if (sheet.getRange(i,50).getValue() == "r"){
  range = sheet.getRange(i,12,1,26);
  arrayRanges.push(range);
}
}
protection.setUnprotectedRanges(arrayRanges);

protection.addEditor("[email protected]");
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
}

I tried to add new unprotected range with SCRIPT 2, but it works the way that erases results of the SCRIPT 1 and leaves me only with the results of the SCRIPT 2.

SCRIPT 2 - adding new unprotected range

function protect(sheet) {

var protection = sheet.protect().setDescription('Protected sheet');
var arrayRanges = new Array;

var range = sheet.getRange(1,3,1,5);
arrayRanges.push(range);

protection.setUnprotectedRanges(arrayRanges);

protection.addEditor("[email protected]");
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
}

I run the scripts through WebApps.

Why is line "protection.addEditor("[email protected]");" is before line "protection.removeEditors(protection.getEditors());" ? It doesn't make any sense.. — Kishan

Kishan Kishan · Accepted Answer · 2019-08-14T16:33:18

You don't have to use ".getRange" and ".getValue" in a loop, if it can be avoided by using ".getValues"

Try the following script code:

//SCRIPT 1
function protect(sheet) {
  //var sheet = SpreadsheetApp.getActiveSheet();
  var protection = sheet.protect().setDescription('Protected sheet');

  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var r = sheet.getRange(1,1,lastRow,lastCol);
  var v = r.getValues();

  var arrayRanges = new Array();

  for(var i=0;i<v.length;i++){
    if( v[i][49] === "s" ) {
      //range = sheet.getRange(i,3,1,5);
      arrayRanges.push( 'C'+(i+1)+':G'+(i+1) );
    }
    else if ( v[i][49] === "d") {
      //range = sheet.getRange(i,2,1,6);
      arrayRanges.push( 'B'+(i+1)+':G'+(i+1) );
    }
    else if ( v[i][49] === "r") {
      //range = sheet.getRange(i,12,1,26);
      arrayRanges.push( 'L'+(i+1)+':AK'+(i+1) );
    }
  }
  var ranges = sheet.getRangeList(arrayRanges).getRanges();
  protection.setUnprotectedRanges(ranges);

  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
  protection.addEditor('[email protected]');
};

//SCRIPT 2 - adding new unprotected range
function addUnprotectedRanges(sheet) {  
  //var sheet = SpreadsheetApp.getActiveSheet();
  var protection = sheet.protect();
  var unprotected = protection.getUnprotectedRanges();
  var range = sheet.getRange(1,3,1,5);
  unprotected.push(range);
  protection.setUnprotectedRanges(unprotected);

  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
  protection.addEditor('[email protected]');
};

How add more unprotected ranges to sheet with some already unprotected ranges?

1 Answers