google sheets - Using a script to unprotect a range based on the value of another cell

Question

I'm trying to build a schedule for employees to add appointments to a rep calendar. The goal is once a day has 5 appointments, it gets blacked out, and locked down. I had this working with data validation, but then we decided to add employee names to a dropdown housed in each cell. Since each cell can only have one data validation rule (as far as I can tell) I'm having to use a script to protect/unprotect the cells. I've almost got everything working, but my function seems to only protect the given range. When I added the else clause to unprotect the range given <5 appointments, it runs without failure, but does't actually change anything. (I suspect it protects, then immediately unprotects the range.) Currently, the function only works on Monday (B5:B27).

The code I'm using is:

function onEdit() {
  var ss = SpreadsheetApp.getActive();
  var maxAppointments = ss.getRange('G3')

  if (countMon >= maxAppointments) {
    var countMon = ss.getRange('B3').getValue();
    var mon = ss.getRange('B5:B27');
    var protectMon = mon.protect().setDescription('Protect Monday').setRangeName('monday');
    protectMon.removeEditors(protectMon.getEditors());
    protectMon.addEditors(['[email protected]', '[email protected]', '[email protected]']);
    if (protectMon.canDomainEdit()) {
      protectMon.setDomainEdit(false);
    }
  }
   else {
   var monProtections = ss.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < monProtections.length; i++) {
      if ( monProtections[i].getRangeName() == 'monday') {
         monProtections[i].remove();
      }
    }
   }
};

I also created a simplified example sheet with personal info scrubbed.

question from:https://stackoverflow.com/questions/65892841/using-a-script-to-unprotect-a-range-based-on-the-value-of-another-cell

Answer 1

You can use this sample code: (this should work for columns Monday-Friday)

function onEdit(e) {
  var ss = e.source; // get spreadsheet
  var sheet = ss.getActiveSheet();
  var cell = e.range; // get edited range
  
  if(sheet.getName() != "Rep1"){
    return;
  }
  
  var maxAppointments = sheet.getRange('G3').getValue();
  var row = cell.getRow();
  var col = cell.getColumn();
  
  //Check if edited cell is within B5:F27
  if(row>=5 && row<=27 && col>=2 && col<=6){
    
    //Get current date user count
    var count = sheet.getRange(3,col).getValue();
    if(count>=maxAppointments){
      //Select Row 5 to Row 27
      var range = sheet.getRange(5,col,23);

      // Protect range
      var protection = range.protect();
      var name = 'Column'+col;
      protection.setDescription(name); 

      Logger.log(protection.getEditors());
      protection.removeEditors(protection.getEditors());
      var me = Session.getEffectiveUser();
      protection.addEditor(me);
      Logger.log(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }else{
      var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
      Logger.log("Length"+protections.length);
      for (var i = 0; i < protections.length; i++) {
      
        var protection = protections[i];
        Logger.log(protection.getDescription());
        if (protection.getDescription() == 'Column'+col) {
          Logger.log("remove");
          protection.remove();
        }
      }
    }
  }
}

I added some guarding in your current onEdit(), such as modified cell should be in sheet "Rep1", modified cells should be within B5:F27 (done by checking row and column index)

What it does?

1. Check if modified cell is in Sheet Rep1 and within B5:F27 by getting its sheet name using Sheet.getName(), Row and Column Index using Range.getRow()/Range.getColumn()

2. If current user count on a specific day is >= the max allowed user count, protect the specific column from row5 to row 27 using Sheet.getRange(row, column, numRows) and Range.protect(). Set the description to "Column"+column index

3. If current user count a specific day is < the max allowed user count, remove the protection if there is any. Protection to be removed will be based on the protection description set in step 2

Sample Output:

-> This is the view from a non-editor user after protection. Notice that column B and D are not editable (Data validation drop-down list was hidded)

Note:

In the sample code, I just set my primary user as the editor after removing other editors. You can just apply your configuration in your original code where there are multiple editors.

I also did not modify your conditional formatting which sets the cell background to black, maybe you could revisit that since it always change the cell background of columns B-D even though not all columns were locked. See the sample output where columns B and D are locked but not column C