Manage protected ranges and sheets

This article explains the method for getting protected ranges and sheets from a specific spreadsheet.

Method to use

Spreadsheet.getProtections(type);

Use getProtections(), which is provided by the Spreadsheet class.

As the argument, pass the ProtectionType enum, which represents the type of protection. To get range protections, pass SpreadsheetApp.ProtectionType.RANGE. To get sheet protections, pass SpreadsheetApp.ProtectionType.SHEET.

The return value is an array of Protection objects, each representing a protected range or sheet.

Because the script accesses the spreadsheet, you may need to authorize it the first time you run it.

Sample code

/**
 * ID of the target spreadsheet
 * The xxx part of https://docs.google.com/spreadsheets/d/xxx/edit
 */
const SPREAD_SHEET_ID = '_______________';

/**
 * Removes the protection from protected ranges and sheets
 */
const removeProtections = () => {
  /** Target spreadsheet */
  const ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);

  /** Protected ranges */
  const rangeProtections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  /** Protected sheets */
  const sheetProtections = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);

  // Remove range protections
  for (const protection of rangeProtections) {
    if (protection.canEdit()) {
      protection.remove();
    }
  }

  // Remove sheet protections
  for (const protection of sheetProtections) {
    if (protection.canEdit()) {
      protection.remove();
    }
  }
};

When you run it, the protection is removed from all protected ranges and sheets in the specified spreadsheet.

Note that if the user running the script doesn’t have permission to edit a protected range or sheet, its protection can’t be removed. The sample code uses canEdit() to check for edit permission and removes only the protections the user has permission for.