Does setUnprotectedRanges() work for editors, or only for viewers?

Editors are not blocked by protections by default — they get a warning dialog but can dismiss it. To actually restrict editors, call protection.removeEditors(editors) or remove yourself with removeEditor(Session.getActiveUser().getEmail()). The unprotected ranges stay open for everyone regardless of that setting.

I re-ran the script and now I have duplicate protections in the panel. Why?

sheet.protect() always creates a new protection object. If you do not remove the existing sheet-level protections first (the getProtections loop in the script), every run stacks another one on top. The fix is included in the script: fetch existing protections with ProtectionType.SHEET and call remove() on each before creating the new one.

My unprotected ranges locked after I inserted rows. How do I fix that?

This only happens if you used individual range protections instead of the single-sheet approach. Range protections do not adjust when rows are inserted. Switch to one sheet protection with setUnprotectedRanges() and the exception ranges will track row insertions correctly.

Can I target a sheet by index instead of by name?

Yes: getSheets()[0] returns the first sheet (zero-indexed). By-name is safer in shared files where collaborators might reorder tabs; by-index is fine for scripts you control end-to-end.

// Sheets · Apps Script

Protect a sheet except specific cells in Google Sheets.

Use Apps Script to lock an entire sheet while leaving specific input cells editable — the right way, using setUnprotectedRanges() instead of stacking individual range protections.

“I want to lock my whole sheet so nobody can accidentally edit formulas or headers, but leave a few input cells open for data entry.”

The script

copy · paste · trigger

protectSheetExceptInputs.gs

Apps Script

// Protect the whole sheet, then punch holes for editable ranges.
// Run once; re-run to reset protections if ranges change.
function protectSheetExceptInputs() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');

  // Remove any existing sheet-level protections first.
  var existing = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
  for (var i = 0; i < existing.length; i++) {
    existing[i].remove();
  }

  var protection = sheet.protect().setDescription('Sheet lock — inputs open');

  // These ranges stay editable for anyone with edit access.
  var unprotected = [
    sheet.getRange('C4:C20'),
    sheet.getRange('F4:F20'),
    sheet.getRange('B2')
  ];
  protection.setUnprotectedRanges(unprotected);

  // Optional: remove yourself from the editors list so even you
  // get the warning dialog outside the unprotected ranges.
  // protection.removeEditor(Session.getActiveUser().getEmail());
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why one sheet protection beats many range protections

The UI makes it tempting to select each editable range and click Protect range. That works until someone inserts a row above row 4. The protection stays anchored to the original row numbers; your input cells shift down and are now locked, while the old protected range floats in empty space. You end up chasing a moving target every time the sheet grows.

The correct model is one protection on the entire sheet, then a list of exceptions passed to setUnprotectedRanges(). Those exceptions are Range objects, so they follow the data when rows are inserted, the same way a named range would. One protection to update, not a dozen.

What the script actually does, step by step

It opens the sheet by name and clears any previous sheet-level protections first. Skipping that step causes duplicates: every time you re-run the function you stack another protection on top of the last, and the exceptions from run N-1 survive silently underneath the new ones.

sheet.protect() creates the sheet-level lock and returns a Protection object. setDescription() is optional but useful — it shows up in the Data > Protect sheets and ranges panel so a future maintainer knows the protection is intentional.

The three ranges passed to setUnprotectedRanges() remain editable for anyone who has edit access to the spreadsheet. The protection only blocks people who have view or comment access, or who are editors but hit a range outside those exceptions. The commented-out removeEditor() call tightens that further: it makes the warning dialog appear even for editors who touch the locked area, which is useful when the sheet is shared with colleagues who have full edit access but should not be casually overwriting formulas.

Hardcoding ranges vs. reading them from a config cell

For a sheet where the input columns never change, hardcoding 'C4:C20' is fine. The first time I hit a sheet where the input area was decided by a dropdown, I moved the range addresses into a named range called INPUT_CELLS and read it back with getRangeByName(). That lets a non-technical owner update the config without touching the script.

One gotcha: setUnprotectedRanges() accepts an array of Range objects, not strings. If you are reading addresses from cells, you still need sheet.getRange(address) to convert each string before building the array. Passing raw strings throws a silent type error and leaves the full sheet locked with no exceptions.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Does setUnprotectedRanges() work for editors, or only for viewers?: Editors are not blocked by protections by default — they get a warning dialog but can dismiss it. To actually restrict editors, call protection.removeEditors(editors) or remove yourself with removeEditor(Session.getActiveUser().getEmail()). The unprotected ranges stay open for everyone regardless of that setting.
I re-ran the script and now I have duplicate protections in the panel. Why?: sheet.protect() always creates a new protection object. If you do not remove the existing sheet-level protections first (the getProtections loop in the script), every run stacks another one on top. The fix is included in the script: fetch existing protections with ProtectionType.SHEET and call remove() on each before creating the new one.
My unprotected ranges locked after I inserted rows. How do I fix that?: This only happens if you used individual range protections instead of the single-sheet approach. Range protections do not adjust when rows are inserted. Switch to one sheet protection with setUnprotectedRanges() and the exception ranges will track row insertions correctly.
Can I target a sheet by index instead of by name?: Yes: getSheets()[0] returns the first sheet (zero-indexed). By-name is safer in shared files where collaborators might reorder tabs; by-index is fine for scripts you control end-to-end.

// one good script a week