Protect spreadsheet then unprotect specific cells and ranges with script

Question

I have a spreadsheet that I owner and I have 10 users with editor permission as there are quite a lot of cells to unlock or lock, it might be best to lock everything first and unlock the ones that they can safely use without accidentally editing a formula incorrectly

now I'm just guessing what the best solution would be I would like to avoid that users is deleted

if it matters then no one is in a group

these would be the ones that the editors can edit

['B3:U27', 'W3:AP27', 'E29:E31', 'I29:I31', 'M29:M31', 'Q29:Q31', 'U29:U31', 'Z29:Z31', 'AD29:AD31', 'AH29:AH31', 'AL29:AL31', 'AP29:AP31', 'B29', 'F29', 'J29', 'N29', 'R29', 'W29', 'AA29', 'AE29', 'AI29', 'AM29', 'C29', 'G29', 'K29', 'O29', 'S29', 'X29', 'AB29', 'AF29', 'AJ29', 'AN29', 'D29', 'H29', 'L29', 'P29', 'T29', 'Y29', 'AC29', 'AG29', 'AK29', 'AO29', 'B31', 'F31', 'J31', 'N31', 'R31', 'W31', 'AA31', 'AE31', 'AI31', 'AM31', 'C31', 'G31', 'K31', 'O31', 'S31', 'X31', 'AB31', 'AF31', 'AJ31', 'AN31', 'D31', 'H31', 'L31', 'P31', 'T31', 'Y31', 'AC31', 'AG31', 'AK31', 'AO31', 'B33:C33', 'F33:G33', 'J33:K33', 'N33:O33', 'R33:S33', 'W33:X33', 'AA33:AB33', 'AE33:AF33', 'AI33:AJ33', 'AM33:AN33' ,'D33:E33', 'H33:I33', 'L33:M33', 'P33:Q33', 'T33:U33', 'Y33:Z33', 'AC33:AD33', 'AG33:AH33', 'AK33:AL33', 'AO33:AP33'];

everything else can be locked

1. will 1 or 2 extra sheets be created each day and will this apply to all sheets from now on?

2. does it matter that already have a few sheets with manually protection (without a script)?

3. users will be notified of this or is it all just happening in the background?

UPDATE:

I found the right script to protect the page and unlock the range and it works perfectly

link

function testProtect() {
var sheet = SpreadsheetApp.getActiveSheet();
var protection = sheet.protect().setDescription('Sample protected sheet');
var unprotected = sheet.getRange('B3:I27');
protection.setUnprotectedRanges([unprotected]);

var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);

}
    }

but there were two things I couldn't figure out how can I apply it to all sheets and add more cells and ranges? because if I add another range it gives an error

var unprotected = sheet.getRange('B3:I27','F29:I29');

Exception: B3:I27 cannot be converted to int type (line 4 in the "Code" file)

thanks in advance for any help!

Answer 1

If I understand your post correctly, here's your goal:

1. Create a script to lock your sheet and only allow specific ranges to be editable for the users with edit access.
2. Apply that script to all of your sheets on your spreadsheet file.

Recommended Solution:

You can refer to this sample script below where it locks your sheet and only unlock specific ranges you setup.

Sample script

[UPDATED]

function main(){ //Main function to run
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var disregard = ["Sheet3","Sheet4","Sheet5"]; //ADD SHEET NAMES HERE THAT YOU WANT TO BE DISREGARDED

  for(var x=0; x<sheets.length; x++){
    if(disregard.some(data => sheets[x].getName().includes(data))){ 
      //E.g. Disregard any sheet names added on the "disregard" array
    }else{
      unlockCertainRanges(sheets[x]);
    }
  }
}

function unlockCertainRanges(currentSheet){ //Function to unlock certain ranges on your spreadshseet
  var sheet = currentSheet;
  // Remove all range protections in the spreadsheet
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    protection.remove();
  }

  var protection = sheet.protect();
  //restrict editors to owner
  protection.getRange().getA1Notation();
  var eds = protection.getEditors();
  protection.removeEditors(eds);

  //set unprotected ranges
  var ranges = protection.getUnprotectedRanges();
  var data = ["A1:A5","B6:B10","C11:C15"]; // ADD YOUR RANGES HERE
  data.forEach(res => { //LOOPS INTO EVERY ARRAY CONTAINING SPECIFIC RANGES
    ranges.push(sheet.getRange(res));
    protection.setUnprotectedRanges(ranges); //REMOVES THE PROTECTION ON THE RANGE
  });
}

Note: Borrowed a snippet of script to unlock specific ranges from How to protect a sheet then unprotect specific cells as reference.

Result:

Sample Sheet

All cells are locked except the ranges "A1:A5","B6:B10" & "C11:C15" (contains the "Unlock" word for visibility)

Other cells are locked

Unlocked range cells are editable