google-sheetsgoogle-apps-scripttriggersspreadsheet-protection

How to apply protected ranges to all users in google sheets?


I have a code that that is doing what I want but I have one problem with it. Basically below code is creating a protected range when the cell in AC Column is not empty and removing it when empty. Code works on edit.

It is working well but the problem is that even if the row is protected everyone can still edit the row and I have no idea how to switch it off. Does anyone have an idea what can be wrong with my code?

function CellProtection2(e) {
var sheet = e.source.getActiveSheet();
  var range = e.range;
  var row = range.getRow();
  var column = range.getColumn();

  // Check if the edited cell is in column AC
  if (column == 29) { // Assuming AC is the 29th column
    var cellValue = range.getValue();
    
    // Get the range from column B to W for the edited row
    var rowRange = sheet.getRange(row, 2, 1, 23); // B2:W2 for the first row
    
    if (cellValue !== "") {
      // If AC column is not empty, protect the range from B to W in the row
      var protection = rowRange.protect().setDescription('Protected because AC column has a value');
      
      // Remove all editors
      var editors = protection.getEditors();
      for (var i = 0; i < editors.length; i++) {
        protection.removeEditor(editors[i]);
      }

      // Disable domain editing
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
      
    } else {
      // If AC column is empty, remove protection from the range
      var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
      for (var i = 0; i < protections.length; i++) {
        var protectionRange = protections[i].getRange();
        if (protectionRange && protectionRange.getA1Notation() == rowRange.getA1Notation()) {
          protections[i].remove();
        }
      }
    }
  }
}

Solution

  • In Google Sheets, it's not possible to apply protection that blocks all users, as the spreadsheet owner will always be able to edit any protected sheet or range.

    When using a script to create, update or delete protections, to block all users except the spreadsheet owner, ensure that the script is run by the spreadsheet owner or an installable trigger created by the spreadsheet owner.

    If the Google Apps Script project is contained by the spreadsheet to protect, then the easier way is to create the trigger "manually" in contrast by using code by using the Google Apps Script web app. For details, please check out https://developers.google.com/apps-script/guides/triggers/installable.

    Please ensure that the function to be called by the installable trigger is not called twice by the same event, i.e., this might occur when using a simple trigger an installable trigger for the same event, like on edit.

    If you create the installable trigger using code, please ensure only one for each function.