Skip to main content

Controll protected sheets and ranges

We will explain the function that get protected sheets and ranges in specific spreadsheet.

Function to use​

Spreadsheet.getProtections(type);

Use getProtections method provided in Spreadsheet class.

Specify string for argument, usually use the contstant provided by Spreadsheet class.

Authentication may be required at the time of first execution because you will access the spreadsheet.

Sample code​

/**
 * Target Spreadsheet ID
 * https://docs.google.com/spreadsheets/d/〇〇〇/edit part of 〇〇〇
 */
const SPREAD_SHEET_ID = '_______________';

/**
 * Unprotect protected range.
 */
const removeProtections = () => {
  /** Target Spreadsheet */
  const ss = SpreadsheetApp.openById(SPREAD_SHEET_ID);

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

  // Unprotect
  for (const protection of rangeProtections) {
    if (protection.canEdit()) {
      protection.remove();
    }
  }
};

When you execute it, you will unprotect all protected spreadsheets.

A script that uses this method requires approval in advance.

Also, if the execution user does not have authority, it cannot be released.