How can I get this to protect a sheet on scripting basis (Google Apps Script)?

1
votes

I got this piece of code running with no error; The resulting spreadsheet does show a locker next to the sheet name; Protected Sheets and Ranges show only specific range is unprotected, but the other user opening the file can edit protected ranges:

      var editors = newSpreadsheet.getEditors();
      for (var i = 0; i < editors.length; i++) {
        newSpreadsheet.removeEditor(editors[i]);
      };

      var sheetToProtect = newSpreadsheet.getSheetByName('CheckList');
      var rngMonitorUnprotect = sheetToProtect.getRange("F11:F14");
      var protection = sheetToProtect.protect();
      protection.setUnprotectedRanges([rngMonitorUnprotect]);

What am I missing here?

1
google-apps-scriptgoogle-sheetsprotection

1 Answers

2
votes

Explanation / Issue:

As per the official documentation, this is the correct way to apply a protection to the sheet.

  • The issue is that you didn't remove the list of editors from the protection object. What you did instead was to remove them from the spreadsheet file itself.

  • Essentially, when you add a protection to a sheet, all the current editors automatically have the right to edit the sheet (or sheet range) regardless of the protection. So your script needs to remove them from that right, this is why we execute this:

    protection.removeEditors(protection.getEditors());

Protect only the range F11:F14 of the sheet:

function myFunction() {
  // Protect range F11:F14, then remove all other users from the list of editors.
  var sheetToProtect = SpreadsheetApp.getActive().getSheetByName('CheckList'); 
  var range = sheetToProtect.getRange('F11:F14'); 
  var protection = range.protect();

  // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
  // permission comes from a group, the script throws an exception upon removing the group.
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Protect the full range of the sheet except for F11:F14:

function myFunction() {
  var sheetToProtect = SpreadsheetApp.getActive().getSheetByName('CheckList'); 
  var protection = sheetToProtect.protect();
  var rngMonitorUnprotect = sheetToProtect.getRange("F11:F14");  
  protection.setUnprotectedRanges([rngMonitorUnprotect]);    
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}