ExcelScript.WorksheetProtection interface

Package:

ExcelScript

Represents the protection of a worksheet object.

Remarks

Examples

/**
 * This script pauses the protection of a worksheet by using the provided password.
 * This password could come from a Power Automate flow.
 */
function main(workbook: ExcelScript.Workbook, password: string) {
  // Get the worksheet named "Sales".
  const sheet = workbook.getWorksheet("Sales");
  const protection: ExcelScript.WorksheetProtection = sheet.getProtection();
  
  // Check if the provided password works.
  if (protection.checkPassword(password)) {
    protection.pauseProtection(password);

    // Edit the worksheet...

    protection.resumeProtection();
  } else {
    console.log("Incorrect password");
  }
}

Methods

addAllowEditRange(title, rangeAddress, options)	Adds an AllowEditRange object to the worksheet. Worksheet protection must be disabled or paused for this method to work properly. If worksheet protection is enabled and not paused, then this method throws an AccessDenied error and the add operation fails.
checkPassword(password)	Specifies if the password can be used to unlock worksheet protection. This method doesn't change the worksheet protection state. If a password is entered but no password is required to unlock worksheet protection, this method will return false.
getAllowEditRange(key)	Gets the AllowEditRange object by its title.
getAllowEditRanges()	Specifies the AllowEditRangeCollection object found in this worksheet. This is a collection of AllowEditRange objects, which work with worksheet protection properties. When worksheet protection is enabled, an AllowEditRange object can be used to allow editing of a specific range, while maintaining protection on the rest of the worksheet.
getCanPauseProtection()	Specifies if protection can be paused for this worksheet.
getIsPasswordProtected()	Specifies if the sheet is password protected.
getIsPaused()	Specifies if worksheet protection is paused.
getOptions()	Specifies the protection options for the worksheet.
getProtected()	Specifies if the worksheet is protected.
getSavedOptions()	Specifies the protection options saved in the worksheet. This will return the same WorksheetProtectionOptions object regardless of the worksheet protection state.
pauseProtection(password)	Pauses worksheet protection for the given worksheet object for the user in the current session. This method does nothing if worksheet protection isn't enabled or is already paused. If the password is incorrect, then this method throws an InvalidArgument error and fails to pause protection. This method does not change the protection state if worksheet protection is not enabled or already paused.
pauseProtectionForAllAllowEditRanges(password)	Pauses worksheet protection for all AllowEditRange objects found in this worksheet that have the given password for the user in the current session. This method does nothing if worksheet protection isn't enabled or is paused. If worksheet protection cannot be paused, this method throws an UnsupportedOperation error and fails to pause protection for the range. If the password does not match any AllowEditRange objects in the collection, then this method throws a BadPassword error and fails to pause protection for any range in the collection.
protect(options, password)	Protects a worksheet. Fails if the worksheet has already been protected.
resumeProtection()	Resumes worksheet protection for the given worksheet object for the user in a given session. Worksheet protection must be paused for this method to work. If worksheet protection is not paused, then this method will not change the protection state of the worksheet.
setPassword(password)	Changes the password associated with the WorksheetProtection object. Setting the password as an empty string ("") or as null will remove password protection from the WorksheetProtection object. Worksheet protection must be enabled and paused for this method to work properly. If worksheet protection is disabled, this method throws an InvalidOperation error and fails to change the password. If worksheet protection is enabled and not paused, this method throws an AccessDenied error and fails to change the password.
unprotect(password)	Unprotects a worksheet.
updateOptions(options)	Change the worksheet protection options associated with the WorksheetProtection object. Worksheet protection must be disabled or paused for this method to work properly. If worksheet protection is enabled and not paused, this method throws an AccessDenied error and fails to change the worksheet protection options.

Method Details

addAllowEditRange(title, rangeAddress, options)

Adds an AllowEditRange object to the worksheet. Worksheet protection must be disabled or paused for this method to work properly. If worksheet protection is enabled and not paused, then this method throws an AccessDenied error and the add operation fails.

addAllowEditRange(
            title: string,
            rangeAddress: string,
            options?: AllowEditRangeOptions
        ): void;

Parameters

title

string

The title string of the AllowEditRange object to be added.

rangeAddress

string

The range address of the AllowEditRange object to be added.

options

ExcelScript.AllowEditRangeOptions

Additional options to be added to the AllowEditRange object, such as the password.

Returns

void

Examples

/**
 * This script adds a password-protected, editable range
 * to an otherwise protected worksheet.
 */
function main(workbook: ExcelScript.Workbook, password: string) {
    // Get the protection object for the "Data" worksheet.
    const dataSheet = workbook.getWorksheet("Data");
    const sheetProtection = dataSheet.getProtection();

    // Set the password needed to edit the range to be the user provided string.
    const editRangeProperties : ExcelScript.AllowEditRangeOptions = {
        password: password
    };

    // Set range "D2:D6" to be editable if the password is provided.
    sheetProtection.addAllowEditRange("Notes Section", "D2:D6", editRangeProperties);
    
    // Protect the worksheet.
    sheetProtection.protect();
}

checkPassword(password)

Specifies if the password can be used to unlock worksheet protection. This method doesn't change the worksheet protection state. If a password is entered but no password is required to unlock worksheet protection, this method will return false.

checkPassword(password?: string): boolean;

Parameters

password

string

The password to check against the protected worksheet.

Returns

boolean

getAllowEditRange(key)

Gets the AllowEditRange object by its title.

getAllowEditRange(key: string): AllowEditRange | undefined;

Parameters

key

string

The title of the AllowEditRange.

Returns

ExcelScript.AllowEditRange | undefined

getAllowEditRanges()

Specifies the AllowEditRangeCollection object found in this worksheet. This is a collection of AllowEditRange objects, which work with worksheet protection properties. When worksheet protection is enabled, an AllowEditRange object can be used to allow editing of a specific range, while maintaining protection on the rest of the worksheet.

getAllowEditRanges(): AllowEditRange[];

Returns

ExcelScript.AllowEditRange[]

getCanPauseProtection()

Specifies if protection can be paused for this worksheet.

getCanPauseProtection(): boolean;

Returns

boolean

getIsPasswordProtected()

Specifies if the sheet is password protected.

getIsPasswordProtected(): boolean;

Returns

boolean

getIsPaused()

Specifies if worksheet protection is paused.

getIsPaused(): boolean;

Returns

boolean

getOptions()

Specifies the protection options for the worksheet.

getOptions(): WorksheetProtectionOptions;

Returns

ExcelScript.WorksheetProtectionOptions

getProtected()

Specifies if the worksheet is protected.

getProtected(): boolean;

Returns

boolean

getSavedOptions()

Specifies the protection options saved in the worksheet. This will return the same WorksheetProtectionOptions object regardless of the worksheet protection state.

getSavedOptions(): WorksheetProtectionOptions;

Returns

ExcelScript.WorksheetProtectionOptions

pauseProtection(password)

Pauses worksheet protection for the given worksheet object for the user in the current session. This method does nothing if worksheet protection isn't enabled or is already paused. If the password is incorrect, then this method throws an InvalidArgument error and fails to pause protection. This method does not change the protection state if worksheet protection is not enabled or already paused.

pauseProtection(password?: string): void;

Parameters

password

string

The password associated with the protected worksheet.

Returns

void

pauseProtectionForAllAllowEditRanges(password)

Pauses worksheet protection for all AllowEditRange objects found in this worksheet that have the given password for the user in the current session. This method does nothing if worksheet protection isn't enabled or is paused. If worksheet protection cannot be paused, this method throws an UnsupportedOperation error and fails to pause protection for the range. If the password does not match any AllowEditRange objects in the collection, then this method throws a BadPassword error and fails to pause protection for any range in the collection.

pauseProtectionForAllAllowEditRanges(password: string): void;

Parameters

password

string

The password to pause protection on the AllowEditRange objects.

Returns

void

protect(options, password)

Protects a worksheet. Fails if the worksheet has already been protected.

protect(options?: WorksheetProtectionOptions, password?: string): void;

Parameters

options

ExcelScript.WorksheetProtectionOptions

Optional. Sheet protection options.

password

string

Optional. Sheet protection password.

Returns

void

Examples

/**
 * This script protects cells from being selected on the current worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the protection settings for the current worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const sheetProtection = currentSheet.getProtection();

  // Create a new WorksheetProtectionOptions object with the selectionMode property set to `none`.
  let protectionOptions : ExcelScript.WorksheetProtectionOptions = {
    selectionMode: ExcelScript.ProtectionSelectionMode.none
  }

  // Apply the given protection options.
  sheetProtection.protect(protectionOptions);
}

resumeProtection()

Resumes worksheet protection for the given worksheet object for the user in a given session. Worksheet protection must be paused for this method to work. If worksheet protection is not paused, then this method will not change the protection state of the worksheet.

resumeProtection(): void;

Returns

void

setPassword(password)

Changes the password associated with the WorksheetProtection object. Setting the password as an empty string ("") or as null will remove password protection from the WorksheetProtection object. Worksheet protection must be enabled and paused for this method to work properly. If worksheet protection is disabled, this method throws an InvalidOperation error and fails to change the password. If worksheet protection is enabled and not paused, this method throws an AccessDenied error and fails to change the password.

setPassword(password?: string): void;

Parameters

password

string

The password associated with the WorksheetProtection object.

Returns

void

unprotect(password)

Unprotects a worksheet.

unprotect(password?: string): void;

Parameters

password

string

Sheet protection password.

Returns

void

updateOptions(options)

Change the worksheet protection options associated with the WorksheetProtection object. Worksheet protection must be disabled or paused for this method to work properly. If worksheet protection is enabled and not paused, this method throws an AccessDenied error and fails to change the worksheet protection options.

updateOptions(options: WorksheetProtectionOptions): void;

Parameters

options

ExcelScript.WorksheetProtectionOptions

The options interface associated with the WorksheetProtection object.

Returns

void