Protect Ranges and Sheets

Apps Script lets you lock specific ranges or entire sheets so that only authorised users can edit them. This is useful for protecting formula cells, header rows, or sensitive data while still allowing team members to update the fields they own.

Protecting the Header Row

function protectHeaderRow() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var headerRange = sheet.getRange("1:1");
  var protection = headerRange.protect()
    .setDescription("Header row – do not edit");

  // Remove all editors except the owner
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }

  Logger.log("Header row protected.");
}

Protecting the Entire Sheet Except the Status Column

This lets sales reps only update column G (Status), while all other columns are locked:

function protectSheetExceptStatus() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  // Protect the whole sheet first
  var protection = sheet.protect().setDescription("Sales Tracker – restricted");

  // Allow editing only in column G (Status)
  var unprotectedRange = sheet.getRange("G2:G1000");
  protection.setUnprotectedRanges([unprotectedRange]);

  // Remove all non-owner editors so only the owner can change the protection
  protection.removeEditors(protection.getEditors());

  Logger.log("Sheet protected; only Status column is editable.");
}

Protecting the Amount Column from Sales Reps

function protectAmountColumn() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var amountRange = sheet.getRange("D2:D1000");
  var protection = amountRange.protect()
    .setDescription("Amount – editable by managers only");

  // Allow only managers to edit
  protection.removeEditors(protection.getEditors());
  protection.addEditors(["[email protected]"]);

  Logger.log("Amount column protected for managers only.");
}

Granting Edit Access to Specific Users

function shareEditAccess() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var protection = sheet.protect().setDescription("Sales Tracker");

  protection.addEditors([
    "[email protected]",
    "[email protected]",
    "[email protected]"
  ]);

  Logger.log("Edit access granted to sales reps.");
}

Listing All Protected Ranges

function listProtectedRanges() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  protections.forEach(function(p) {
    Logger.log(
      "Range: " + p.getRange().getA1Notation()
      + " | Description: " + p.getDescription()
      + " | Editors: " + p.getEditors().join(", ")
    );
  });
}

Removing All Protections

function removeAllProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Remove sheet-level protections
  ss.getSheets().forEach(function(sheet) {
    sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET).forEach(function(p) {
      p.remove();
    });
    sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function(p) {
      p.remove();
    });
  });

  Logger.log("All protections removed.");
}

Warning-Only Mode

If you want to warn users before editing (but not block them):

function setWarningOnlyProtection() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var protection = sheet.protect().setWarningOnly(true);
  Logger.log("Warning-only protection enabled.");
}

setWarningOnly(true) shows a confirmation dialog when anyone tries to edit a protected area, but still allows the edit to proceed.