// Sheets · Apps Script

Protect a range from editing in Google Sheets.

How to lock a specific range in Google Sheets using Apps Script — including the step most tutorials skip: explicitly removing existing editors so the protection actually holds.

“I want to lock a specific range in my Google Sheet so collaborators can't accidentally overwrite formulas or reference data, and I want to do it programmatically so I don't have to repeat the UI clicks every time the sheet is re-created.”

The script

copy · paste · trigger

protectRange.gs

Apps Script

// Lock B2:D10 so only the script owner can edit it.
// Without removeEditors(), the protection is created but ignored.
function protectSummaryRange() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary');
  var range = sheet.getRange('B2:D10');

  var protection = range.protect();
  protection.setDescription('Summary inputs — do not edit');

  // Remove everyone currently listed as an editor.
  var editors = protection.getEditors();
  if (editors.length > 0) {
    protection.removeEditors(editors);
  }

  // Re-add only the owner (the account running this script).
  var owner = SpreadsheetApp.getActiveSpreadsheet().getOwner();
  protection.addEditor(owner);
}

Generate yours

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

Walkthrough

What protect() actually does (and doesn't do)

Calling range.protect() creates a Protection object and registers it with the spreadsheet. That part works fine. What catches people is that the newly created protection inherits the spreadsheet's existing editor list — so every collaborator who already had edit access to the file still has edit access to the protected range. The range is technically protected, but the list of permitted editors is still wide open.

The fix is to call protection.getEditors() immediately after creating the protection, then pass that array directly to protection.removeEditors(). That call wipes the inherited list. You then add back only the accounts you actually want to allow. In most automation scenarios, that's just the owner: SpreadsheetApp.getActiveSpreadsheet().getOwner().

The first time I hit this, I spent twenty minutes in the Sheets UI confirming the protection was listed but couldn't figure out why a shared account was still overwriting cells. The protection was real; the editor list was the problem.

Running it once versus running it on every open

If you attach protectSummaryRange() to a button or run it manually once, you're done — protections persist with the spreadsheet and survive sharing, copying to Drive, and re-opening. You do not need to re-run on each open.

The exception is templates: when you use SpreadsheetApp.create() or Utilities.copyFile() to spin up a fresh sheet from a template, protections from the source do not carry over cleanly to the new file's permission model. In that workflow, call protectSummaryRange() (or an equivalent) immediately after creating the copy, before returning the new file's URL to the user.

One edge case worth naming: if the script is running as a service account or an OAuth-delegated identity rather than the spreadsheet owner, getOwner() returns the file owner's email, not the service account. addEditor() will still accept it, but whether that account can actually edit depends on whether they're in the file's share list at the file level. Protection-level permissions are additive on top of file-level permissions, not a replacement for them.

Tightening it further: warning-only mode and unprotecting by description

If locking the range entirely is too aggressive — say, you want collaborators to be able to edit but see a warning — call protection.setWarningOnly(true) instead of managing the editor list. The warning dialog fires on every edit attempt, but the user can click through it. Useful for cells that are wrong to edit but not catastrophic.

When you need to remove or update a protection later from script, use sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE) to get an array of all range protections on that sheet, then filter by protection.getDescription() to find yours. That description string set at creation time is your handle — without it, you're iterating over anonymous protections and guessing. I keep descriptions consistent with the function name, like 'Summary inputs — do not edit', so I can match them reliably.

Deleting a stale protection is protection.remove(). No confirmation, no undo from script. If you're regenerating protections on a sheet that's been modified, the safe pattern is: getProtections(), remove() all of them, then re-run the protection setup fresh.

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

I ran the script and the protection shows up in the UI, but editors can still change the cells. What's wrong?: The inherited editor list. When you call range.protect(), the protection starts with all current spreadsheet editors already allowed. Call protection.getEditors() and pass the result to protection.removeEditors() before adding back only the accounts you want. Without that step, the protection exists on paper but every existing collaborator bypasses it.
Can I protect a range so that nobody — not even the owner — can edit it?: No. The spreadsheet owner always retains the ability to modify or delete protections through the UI, and removeEditors() will not accept the owner's email as a removal target — Apps Script silently ignores that entry. You can remove all other editors, but the owner is always permitted.
Does protecting a range via Apps Script behave the same as protecting it through the Sheets UI?: Yes, they write to the same underlying data model. A protection set by script appears in Data > Protect sheets and ranges in the UI, and vice versa. The only difference is that script-created protections can be identified and managed by description string, which the UI does not expose as a filter.
I copied the spreadsheet to a new file and the protections disappeared. How do I re-apply them?: Protections on a copied file are stripped or reset because the new file has a different set of editors and a different owner resolution. The standard fix is to call your protection setup function immediately after DriveApp.copyFile() or SpreadsheetApp.copy() returns the new file object, before handing the URL to anyone.