How to protect a sheet then unprotect specific cel

2019-07-24 17:51发布

问题:

I have created a script that runs on opening of a GoogleSheet. The script unprotects the entire sheet then protects all but the row of the current date. The script does exactly what I want, but it occurred to me that it is too cumbersome and time consuming.

Instead of protecting a whole bunch of specific ranges, is there a way to protect the entire sheet and then unprotect only a few ranges. This would seem to be much more efficient regarding script run time.

My only problem is that when I try to unprotect specific ranges, the code throws an error.

Cannot find method setUnprotectedRanges(Range)

code snippet here:

var sheet = SpreadsheetApp.getActiveSheet();
    // 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 range1 = sheet.getRange("A2:L2");
    protection.setUnprotectedRanges(range1);
    var range1 = sheet.getRange("A4:C4");
    protection.setUnprotectedRanges(range1); 

Any suggestion?

Gary

回答1:

How about this modification?

Modification point:

  • When it adds the unprotected range, please add them as an array.

Modified script:

Please modify the part of //set unprotected ranges as follows.

//set unprotected ranges
var range1 = sheet.getRange("A2:L2");
var range2 = sheet.getRange("A4:C4");
protection.setUnprotectedRanges([range1, range2]);

If you want to add new ranges to the existing unprotected ranges, please use the following script.

var ranges = protection.getUnprotectedRanges();
var range1 = sheet.getRange("A2:L2");
var range2 = sheet.getRange("A4:C4");
ranges.push(range1);
ranges.push(range2);
protection.setUnprotectedRanges(ranges);

Reference:

  • setUnprotectedRanges(ranges)

If this was not what you want, please tell me. I would like to modify it.