3

I would like to write scripts that can protect and unprotect a range in a Google spreadsheet. The first function works:

function AddProtectionToColumn() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getRange('A:A');
   var protectSs = range.protect().setDescription('Protect column A');  
   var me = Session.getEffectiveUser();
   protectSs.addEditor(me);
   protectSs.removeEditors(protectSs.getEditors());
   if (protectSs.canDomainEdit()) {
     protectSs.setDomainEdit(false); 
  }
}

but the second function is not working:

function RemoveProtectionToColumn() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getRange('A:A');
   range.protect().remove();
   var me = Session.getEffectiveUser();
   protectSs.addEditor(me);
   protectSs.removeEditors(protectSs.getEditors());
   if (protectSs.canDomainEdit()) {
     protectSs.setDomainEdit(false); 
  }
}

Can someone help?

3

The line

range.protect().remove(); 

does not have the desired effect because the method protect actually creates a new protection, which you then remove with remove. What you need is to get the existing protection with range A:A.

Unfortunately, there is no getProtection() method of a Range object. One has to call getProtections on the sheet (or spreadsheet), and then search through the returned array of protections until you find the one you need. You can search either by range (calling getRange on each Protection object) or, which is more robust, by description.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]; // assuming you want the first sheet
var protections = sheet.getProtections();
for (var i = 0; i < protections.length; i++) {
  if (protections[i].getDescription() == 'Protect column A') {
    protections[i].remove();
  }
}  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.