0

I want to restrict approximately 80% of cells within range(s) from being edited by anyone but a specific group of people I call 'editors'. The other 20% of cells I want to be editable by a set of users called 'estimators'. Both groups have their email addresses listed in a single-dimensional array. The cells are not grouped in any way and can be thought of as being random. The range(s) could include several thousand cells within a single Google Sheet.

I have tested a couple options using a smaller block of cells as a test bed. Neither seems to be a good solution.

I have tried iterating across the range and applying restrictions to each cell as needed. While this seems to work, it takes forever (> 10 mins for ~100 cells), I end up with a bazillion permission rules (~100 but terrible to sort through in the side bar), and I start getting errors like this one while the script is running:

Exception: Service Spreadsheets failed while accessing document with id 1p4l8-XOkxoEz7fPdp18Ei-....

I have also tried to restrict access to the entire range (100% of the cells) and then going to the 20% of cells and allowing users to edit those cells. My hope was this would result in fewer rules and make the process faster. This does not work as the previous restriction rule (which is still in effect) seems to override the second rule.

Here is my permissions routine that I got from https://developers.google.com/apps-script/reference/spreadsheet/protection. This routine is used to only restrict cells. This was my first attempt:

// Restricts the cell so it can only be edited by users whose email addresses are listed in the
// variable 'editors'.
// 'sheet' is the sheet the range applies to.
// 'cell' is the range of cells to be protected in A1 notation.
// 'editors' is a one-dimensional array of email address strings.
function protectCell(sheet, cell, editors) {
  var protection = sheet.getRange(cell).protect().setDescription('Formulas and references are protected.');
  protection.addEditors(editors);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

This is my second attempt where I restrict the entire block then 'unrestrict' specific cells. I have not completely vetted this code block but you can get the idea. This does not work when tested manually as the first rule appears to override the second:

// This function protects the passed range from editing so only email addresses
// contained in the 'editors' variable will be able to edit them. It excludes all
// cells contained in the 'excludedCells' arg.
// range is expected to be in A1 Notation
// excludedCells is a one-dimensional array of ranges that are each in A1 Notation.
function protectBlock(sheet, range, excludedCells, editors, estimators) {
  // First we'll restrict the entire range from everyone except 'editors'
  var protection = sheet.getRange(range).protect().setDescription('Formulas and references are protected.');
  protection.addEditors(editors);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) protection.setDomainEdit(false);

  // Now let's open the editable cells up to 'estimators'
  for (index = 0; index < excludedCells.length; index++) {
    var protection = sheet.getRange(excludedCells[index]).protect().setDescription('Editable cell by estimators.');
    protection.addEditors(estimators);
  }
}

My Questions:
What is the best way to restrict access to many cells that cannot be easily grouped so that

  1. it is scriptable,
  2. it executes quickly, and
  3. does not lend itself to errors?

Bonus question:
When there are conflicting permissions rules, how does Google Apps determine which rule wins?

Your Answer

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

Browse other questions tagged or ask your own question.