Excel.WorksheetProtection class

Represents the protection of a sheet object.

[ API set: ExcelApi 1.2 ]

Extends
OfficeExtension.ClientObject

Properties

options

Sheet protection options. Read-only.

[ API set: ExcelApi 1.2 ]

protected

Indicates if the worksheet is protected. Read-only.

[ API set: ExcelApi 1.2 ]

Methods

load(option)

Queues up a command to load the specified properties of the object. You must call "context.sync()" before reading the properties.

protect(options, password)

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

[ API set: ExcelApi 1.2 for options; 1.7 for password ]

toJSON()
unprotect(password)

Unprotects a worksheet.

[ API set: ExcelApi 1.7 for password ]

Property Details

options

Sheet protection options. Read-only.

[ API set: ExcelApi 1.2 ]

readonly options: Excel.WorksheetProtectionOptions;
Property Value

protected

Indicates if the worksheet is protected. Read-only.

[ API set: ExcelApi 1.2 ]

readonly protected: boolean;
Property Value
boolean

Method Details

load(option)

Queues up a command to load the specified properties of the object. You must call "context.sync()" before reading the properties.

load(option?: string | string[]): Excel.WorksheetProtection;
Parameters
option
string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns
Remarks

In addition to this signature, this method has the following signatures:

load(option?: { select?: string; expand?: string; }): Excel.WorksheetProtection - Where option.select is a comma-delimited string that specifies the properties to load, and options.expand is a comma-delimited string that specifies the navigation properties to load.

load(option?: { select?: string; expand?: string; top?: number; skip?: number }): Excel.WorksheetProtection - Only available on collection types. It is similar to the preceding signature. Option.top specifies the maximum number of collection items that can be included in the result. Option.skip specifies the number of items that are to be skipped and not included in the result. If option.top is specified, the result set will start after skipping the specified number of items.

protect(options, password)

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

[ API set: ExcelApi 1.2 for options; 1.7 for password ]

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

Optional. Sheet protection options.

password
string

Optional. Sheet protection password.

Returns
void

Examples

Excel.run(function(ctx) {
  // get a reference to Sheet1
  var sheet = ctx.workbook.worksheets.getItem("Sheet1");

  // Protect inserting or deleting rows in Sheet1
  sheet.protection.protect({
    allowInsertRows: false,
    allowDeleteRows: false
  });

  return ctx.sync();
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
await Excel.run(async (context) => {
    let activeSheet = context.workbook.worksheets.getActiveWorksheet();
    activeSheet.load("protection/protected");

    await context.sync();

    if (!activeSheet.protection.protected) {
        activeSheet.protection.protect();
    }
});
let password = await passwordHandler();
passwordHelper(password);
await Excel.run(async (context) => {
    let activeSheet = context.workbook.worksheets.getActiveWorksheet();
    activeSheet.load("protection/protected");

    await context.sync();

    if (!activeSheet.protection.protected) {
        activeSheet.protection.protect(null, password);
    }
});

toJSON()

toJSON(): Excel.Interfaces.WorksheetProtectionData;
Returns

unprotect(password)

Unprotects a worksheet.

[ API set: ExcelApi 1.7 for password ]

unprotect(password?: string): void;
Parameters
password
string

sheet protection password.

Returns
void

Examples

Excel.run(function(ctx) {
  // get a reference to Sheet1
  var sheet = ctx.workbook.worksheets.getItem("Sheet1");

  // Remove all protects applied to Sheet1
  sheet.protection.unprotect();

  return ctx.sync();
}).catch(function(error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
        console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
});
// Unprotecting a worksheet with unprotect() will remove all 
// WorksheetProtectionOptions options applied to a worksheet.
// To remove only a subset of WorksheetProtectionOptions use the 
// protect() method and set the options you wish to remove to true.
Excel.run(function(ctx) {
  var sheet = ctx.workbook.worksheets.getItem("Sheet1");
  sheet.protection.protect({
    allowInsertRows: false, // Protect row insertion
    allowDeleteRows: true // Unprotect row deletion
  });
});
await Excel.run(async (context) => {
    let activeSheet = context.workbook.worksheets.getActiveWorksheet();
    activeSheet.protection.unprotect();
});
let password = await passwordHandler();
passwordHelper(password);
await Excel.run(async (context) => {
    let activeSheet = context.workbook.worksheets.getActiveWorksheet();
    activeSheet.protection.unprotect(password);
});