Google-sheets – How to remove protection on a range using scripts in Google spreadsheets

google sheetsgoogle-apps-script

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?

Best Answer

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();
  }
}
Related Topic