Excel. Worksheet class

Properties

autoFilter	Represents the `AutoFilter` object of the worksheet.
charts	Returns a collection of charts that are part of the worksheet.
comments	Returns a collection of all the Comments objects on the worksheet.
context	The request context associated with the object. This connects the add-in's process to the Office host application's process.
customProperties	Gets a collection of worksheet-level custom properties.
enableCalculation	Determines if Excel should recalculate the worksheet when necessary. True if Excel recalculates the worksheet when necessary. False if Excel doesn't recalculate the sheet.
freezePanes	Gets an object that can be used to manipulate frozen panes on the worksheet.
horizontalPageBreaks	Gets the horizontal page break collection for the worksheet. This collection only contains manual page breaks.
id	Returns a value that uniquely identifies the worksheet in a given workbook. The value of the identifier remains the same even when the worksheet is renamed or moved.
name	The display name of the worksheet.
namedSheetViews	Returns a collection of sheet views that are present in the worksheet.
names	Collection of names scoped to the current worksheet.
pageLayout	Gets the `PageLayout` object of the worksheet.
pivotTables	Collection of PivotTables that are part of the worksheet.
position	The zero-based position of the worksheet within the workbook.
protection	Returns the sheet protection object for a worksheet.
shapes	Returns the collection of all the Shape objects on the worksheet.
showGridlines	Specifies if gridlines are visible to the user.
showHeadings	Specifies if headings are visible to the user.
slicers	Returns a collection of slicers that are part of the worksheet.
standardHeight	Returns the standard (default) height of all the rows in the worksheet, in points.
standardWidth	Specifies the standard (default) width of all the columns in the worksheet. One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.
tabColor	The tab color of the worksheet. When retrieving the tab color, if the worksheet is invisible, the value will be `null`. If the worksheet is visible but the tab color is set to auto, an empty string will be returned. Otherwise, the property will be set to a color, in the form #RRGGBB (e.g., "FFA500"). When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.
tabId	Returns a value representing this worksheet that can be read by Open Office XML. This is an integer value, which is different from `worksheet.id` (which returns a globally unique identifier) and `worksheet.name` (which returns a value such as "Sheet1").
tables	Collection of tables that are part of the worksheet.
tasks	Returns a collection of tasks that are present in the worksheet.
verticalPageBreaks	Gets the vertical page break collection for the worksheet. This collection only contains manual page breaks.
visibility	The visibility of the worksheet.

Methods

activate()	Activate the worksheet in the Excel UI.
calculate(markAllDirty)	Calculates all cells on a worksheet.
copy(positionType, relativeTo)	Copies a worksheet and places it at the specified position.
copy(positionTypeString, relativeTo)	Copies a worksheet and places it at the specified position.
delete()	Deletes the worksheet from the workbook. Note that if the worksheet's visibility is set to "VeryHidden", the delete operation will fail with an `InvalidOperation` exception. You should first change its visibility to hidden or visible before deleting it.
findAll(text, criteria)	Finds all occurrences of the given string based on the criteria specified and returns them as a `RangeAreas` object, comprising one or more rectangular ranges.
findAllOrNullObject(text, criteria)	Finds all occurrences of the given string based on the criteria specified and returns them as a `RangeAreas` object, comprising one or more rectangular ranges.
getCell(row, column)	Gets the `Range` object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid.
getNext(visibleOnly)	Gets the worksheet that follows this one. If there are no worksheets following this one, this method will throw an error.
getNextOrNullObject(visibleOnly)	Gets the worksheet that follows this one. If there are no worksheets following this one, then this method returns an object with its `isNullObject` property set to `true`. For further information, see *OrNullObject methods and properties.
getPrevious(visibleOnly)	Gets the worksheet that precedes this one. If there are no previous worksheets, this method will throw an error.
getPreviousOrNullObject(visibleOnly)	Gets the worksheet that precedes this one. If there are no previous worksheets, then this method returns an object with its `isNullObject` property set to `true`. For further information, see *OrNullObject methods and properties.
getRange(address)	Gets the `Range` object, representing a single rectangular block of cells, specified by the address or name.
getRangeByIndexes(startRow, startColumn, rowCount, columnCount)	Gets the `Range` object beginning at a particular row index and column index, and spanning a certain number of rows and columns.
getRanges(address)	Gets the `RangeAreas` object, representing one or more blocks of rectangular ranges, specified by the address or name.
getUsedRange(valuesOnly)	The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, this function will return the top left cell (i.e. it will not throw an error).
getUsedRangeOrNullObject(valuesOnly)	The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, then this method returns an object with its `isNullObject` property set to `true`. For further information, see *OrNullObject methods and properties.
load(options)	Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading the properties.
load(propertyNames)	Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading the properties.
load(propertyNamesAndPaths)	Queues up a command to load the specified properties of the object. You must call `context.sync()` before reading the properties.
replaceAll(text, replacement, criteria)	Finds and replaces the given string based on the criteria specified within the current worksheet.
set(properties, options)	Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.
set(properties)	Sets multiple properties on the object at the same time, based on an existing loaded object.
showOutlineLevels(rowLevels, columnLevels)	Shows row or column groups by their outline levels. Outlines groups and summarizes a list of data in the worksheet. The `rowLevels` and `columnLevels` parameters specify how many levels of the outline will be displayed. The acceptable argument range is between 0 and 8. A value of 0 does not change the current display. A value greater than the current number of levels displays all the levels.
toJSON()	Overrides the JavaScript `toJSON()` method in order to provide more useful output when an API object is passed to `JSON.stringify()`. (`JSON.stringify`, in turn, calls the `toJSON` method of the object that is passed to it.) Whereas the original Excel.Worksheet object is an API object, the `toJSON` method returns a plain JavaScript object (typed as `Excel.Interfaces.WorksheetData`) that contains shallow copies of any loaded child properties from the original object.

Events

onActivated	Occurs when the worksheet is activated.
onCalculated	Occurs when the worksheet is calculated.
onChanged	Occurs when data changes in a specific worksheet.
onColumnSorted	Occurs when one or more columns have been sorted. This happens as the result of a left to right sort operation.
onDeactivated	Occurs when the worksheet is deactivated.
onFiltered	Occurs when a filter is applied on a specific worksheet.
onFormatChanged	Occurs when format changed on a specific worksheet.
onFormulaChanged	Occurs when one or more formulas are changed in this worksheet. This event is for when the formula itself changes, not the data value resulting from the formula's calculation.
onNameChanged	Occurs when the worksheet name is changed.
onProtectionChanged	Occurs when the worksheet protection state is changed.
onRowHiddenChanged	Occurs when the hidden state of one or more rows has changed on a specific worksheet.
onRowSorted	Occurs when one or more rows have been sorted. This happens as the result of a top-to-bottom sort operation.
onSelectionChanged	Occurs when the selection changes on a specific worksheet.
onSingleClicked	Occurs when a left-clicked/tapped action happens in the worksheet. This event will not be fired when clicking in the following cases: The user drags the mouse for multi-selection. The user selects a cell in the mode when cell arguments are selected for formula references.
onVisibilityChanged	Occurs when the worksheet visibility is changed.

Property Details

autoFilter

Represents the AutoFilter object of the worksheet.

readonly autoFilter: Excel.AutoFilter;

Property Value

Excel.AutoFilter

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-auto-filter.yaml

// This function adds a percentage AutoFilter to the active worksheet 
// and applies the filter to a column of the used range.
await Excel.run(async (context) => {
    // Retrieve the active worksheet and the used range on that worksheet.
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const farmData = sheet.getUsedRange();

    // Add a filter that will only show the rows with the top 50% of values in column 3.
    sheet.autoFilter.apply(farmData, 3, {
        criterion1: "50",
        filterOn: Excel.FilterOn.topPercent
    });

    await context.sync();
});

charts

Returns a collection of charts that are part of the worksheet.

readonly charts: Excel.ChartCollection;

Property Value

Excel.ChartCollection

Remarks

comments

Returns a collection of all the Comments objects on the worksheet.

readonly comments: Excel.CommentCollection;

Property Value

Excel.CommentCollection

Remarks

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

Excel.RequestContext

customProperties

Gets a collection of worksheet-level custom properties.

readonly customProperties: Excel.WorksheetCustomPropertyCollection;

Property Value

Excel.WorksheetCustomPropertyCollection

Remarks

[ API set: ExcelApi 1.12 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/26-document/custom-properties.yaml

await Excel.run(async (context) => {
  // Load the keys and values of all custom properties in the current worksheet.
  const customWorksheetProperties = context.workbook.worksheets.getActiveWorksheet().customProperties;
  customWorksheetProperties.load(["key", "value"]);
  await context.sync();

  // Log each custom property to the console.
  // Note that your document may have more properties than those you have set using this snippet.
  customWorksheetProperties.items.forEach((property) => {
    console.log(`${property.key}:${property.value}`);
  });
});

enableCalculation

Determines if Excel should recalculate the worksheet when necessary. True if Excel recalculates the worksheet when necessary. False if Excel doesn't recalculate the sheet.

enableCalculation: boolean;

Property Value

boolean

Remarks

freezePanes

Gets an object that can be used to manipulate frozen panes on the worksheet.

readonly freezePanes: Excel.WorksheetFreezePanes;

Property Value

Excel.WorksheetFreezePanes

Remarks

horizontalPageBreaks

Gets the horizontal page break collection for the worksheet. This collection only contains manual page breaks.

readonly horizontalPageBreaks: Excel.PageBreakCollection;

Property Value

Excel.PageBreakCollection

Remarks

id

Returns a value that uniquely identifies the worksheet in a given workbook. The value of the identifier remains the same even when the worksheet is renamed or moved.

readonly id: string;

Property Value

string

Remarks

name

The display name of the worksheet.

name: string;

Property Value

string

Remarks

namedSheetViews

Returns a collection of sheet views that are present in the worksheet.

readonly namedSheetViews: Excel.NamedSheetViewCollection;

Property Value

Excel.NamedSheetViewCollection

Remarks

[ API set: ExcelApiOnline 1.1 ]

names

Collection of names scoped to the current worksheet.

readonly names: Excel.NamedItemCollection;

Property Value

Excel.NamedItemCollection

Remarks

[ API set: ExcelApi 1.4 ]

pageLayout

Gets the PageLayout object of the worksheet.

readonly pageLayout: Excel.PageLayout;

Property Value

Excel.PageLayout

Remarks

pivotTables

Collection of PivotTables that are part of the worksheet.

readonly pivotTables: Excel.PivotTableCollection;

Property Value

Excel.PivotTableCollection

Remarks

[ API set: ExcelApi 1.3 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-get-pivottables.yaml

await Excel.run(async (context) => {
  // Get the names of all the PivotTables in the current worksheet.
  const pivotTables = context.workbook.worksheets.getActiveWorksheet().pivotTables;
  pivotTables.load("name");
  await context.sync();

  // Display the names in the console.
  console.log("PivotTables in the current worksheet:")
  pivotTables.items.forEach((pivotTable) => {
    console.log(`\t${pivotTable.name}`);
  });
});

position

The zero-based position of the worksheet within the workbook.

position: number;

Property Value

number

Remarks

Examples

// Set worksheet position.
await Excel.run(async (context) => { 
    const wSheetName = 'Sheet1';
    const worksheet = context.workbook.worksheets.getItem(wSheetName);
    worksheet.position = 2;
    await context.sync(); 
});

protection

Returns the sheet protection object for a worksheet.

readonly protection: Excel.WorksheetProtection;

Property Value

Excel.WorksheetProtection

Remarks

[ API set: ExcelApi 1.2 ]

Examples

// 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.
await Excel.run(async (context) => {
  const sheet = context.workbook.worksheets.getItem("Sheet1");
  sheet.protection.protect({
    allowInsertRows: false, // Protect row insertion
    allowDeleteRows: true // Unprotect row deletion
  });
});

shapes

Returns the collection of all the Shape objects on the worksheet.

readonly shapes: Excel.ShapeCollection;

Property Value

Excel.ShapeCollection

Remarks

showGridlines

Specifies if gridlines are visible to the user.

showGridlines: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.8 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/gridlines.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.showGridlines = true;

    await context.sync();
});

showHeadings

Specifies if headings are visible to the user.

showHeadings: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.8 ]

slicers

Returns a collection of slicers that are part of the worksheet.

readonly slicers: Excel.SlicerCollection;

Property Value

Excel.SlicerCollection

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-slicer.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Pivot");
    const slicer = sheet.slicers.add(
        "Farm Sales", /* The slicer data source. For PivotTables, this can be the PivotTable object reference or name. */
        "Type" /* The field in the data source to filter by. For PivotTables, this can be a PivotField object reference or ID. */
    );
    slicer.name = "Fruit Slicer";
    await context.sync();
});

standardHeight

Returns the standard (default) height of all the rows in the worksheet, in points.

readonly standardHeight: number;

Property Value

number

Remarks

standardWidth

Specifies the standard (default) width of all the columns in the worksheet. One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

standardWidth: number;

Property Value

number

Remarks

tabColor

The tab color of the worksheet. When retrieving the tab color, if the worksheet is invisible, the value will be null. If the worksheet is visible but the tab color is set to auto, an empty string will be returned. Otherwise, the property will be set to a color, in the form #RRGGBB (e.g., "FFA500"). When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.

tabColor: string;

Property Value

string

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/tab-color.yaml

await Excel.run(async (context) => {
    const activeSheet = context.workbook.worksheets.getActiveWorksheet();
    activeSheet.tabColor = "#FF0000";

    await context.sync();
});

tabId

Returns a value representing this worksheet that can be read by Open Office XML. This is an integer value, which is different from worksheet.id (which returns a globally unique identifier) and worksheet.name (which returns a value such as "Sheet1").

readonly tabId: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.14 ]

tables

Collection of tables that are part of the worksheet.

readonly tables: Excel.TableCollection;

Property Value

Excel.TableCollection

Remarks

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

tasks

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Returns a collection of tasks that are present in the worksheet.

readonly tasks: Excel.DocumentTaskCollection;

Property Value

Excel.DocumentTaskCollection

Remarks

verticalPageBreaks

Gets the vertical page break collection for the worksheet. This collection only contains manual page breaks.

readonly verticalPageBreaks: Excel.PageBreakCollection;

Property Value

Excel.PageBreakCollection

Remarks

[ API set: ExcelApi 1.1 for reading visibility; 1.2 for setting it. ]

visibility

The visibility of the worksheet.

visibility: Excel.SheetVisibility | "Visible" | "Hidden" | "VeryHidden";

Property Value

Excel.SheetVisibility | "Visible" | "Hidden" | "VeryHidden"

Remarks

Method Details

activate()

Activate the worksheet in the Excel UI.

activate(): void;

Returns

void

Remarks

Examples

await Excel.run(async (context) => { 
    const wSheetName = 'Sheet1';
    const worksheet = context.workbook.worksheets.getItem(wSheetName);
    worksheet.activate();
    await context.sync(); 
});

calculate(markAllDirty)

Calculates all cells on a worksheet.

calculate(markAllDirty: boolean): void;

Parameters

markAllDirty: boolean

True, to mark all as dirty.

Returns

void

Remarks

[ API set: ExcelApi 1.6 ]

copy(positionType, relativeTo)

Copies a worksheet and places it at the specified position.

copy(positionType?: Excel.WorksheetPositionType, relativeTo?: Excel.Worksheet): Excel.Worksheet;

Parameters

positionType: Excel.WorksheetPositionType

The location in the workbook to place the newly created worksheet. The default value is "None", which inserts the worksheet at the beginning of the worksheet.

relativeTo: Excel.Worksheet

The existing worksheet which determines the newly created worksheet's position. This is only needed if positionType is "Before" or "After".

Returns

Excel.Worksheet

The newly created worksheet.

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-copy.yaml

await Excel.run(async (context) => {

    let myWorkbook = context.workbook;
    let sampleSheet = myWorkbook.worksheets.getActiveWorksheet();
    let copiedSheet = sampleSheet.copy("End")

    sampleSheet.load("name");
    copiedSheet.load("name");

    await context.sync();

    console.log("'" + sampleSheet.name + "' was copied to '" + copiedSheet.name + "'")
});

copy(positionTypeString, relativeTo)

Copies a worksheet and places it at the specified position.

copy(positionTypeString?: "None" | "Before" | "After" | "Beginning" | "End", relativeTo?: Excel.Worksheet): Excel.Worksheet;

Parameters

positionTypeString: "None" | "Before" | "After" | "Beginning" | "End"

The location in the workbook to place the newly created worksheet. The default value is "None", which inserts the worksheet at the beginning of the worksheet.

relativeTo: Excel.Worksheet

The existing worksheet which determines the newly created worksheet's position. This is only needed if positionType is "Before" or "After".

Returns

Excel.Worksheet

The newly created worksheet.

Remarks

delete()

Deletes the worksheet from the workbook. Note that if the worksheet's visibility is set to "VeryHidden", the delete operation will fail with an InvalidOperation exception. You should first change its visibility to hidden or visible before deleting it.

delete(): void;

Returns

void

Remarks

Examples

await Excel.run(async (context) => { 
    const wSheetName = 'Sheet1';
    const worksheet = context.workbook.worksheets.getItem(wSheetName);
    worksheet.delete();
    await context.sync(); 
});

findAll(text, criteria)

Finds all occurrences of the given string based on the criteria specified and returns them as a RangeAreas object, comprising one or more rectangular ranges.

findAll(text: string, criteria: Excel.WorksheetSearchCriteria): Excel.RangeAreas;

Parameters

text: string

The string to find.

criteria: Excel.WorksheetSearchCriteria

Additional search criteria, including whether the search needs to match the entire cell or be case-sensitive.

Returns

Excel.RangeAreas

A RangeAreas object, comprising one or more rectangular ranges, that matches the search criteria. If no cells meet this criteria, an ItemNotFound error will be thrown.

Remarks

findAllOrNullObject(text, criteria)

Finds all occurrences of the given string based on the criteria specified and returns them as a RangeAreas object, comprising one or more rectangular ranges.

findAllOrNullObject(text: string, criteria: Excel.WorksheetSearchCriteria): Excel.RangeAreas;

Parameters

text: string

The string to find.

criteria: Excel.WorksheetSearchCriteria

Additional search criteria, including whether the search needs to match the entire cell or be case-sensitive.

Returns

Excel.RangeAreas

A RangeAreas object, comprising one or more rectangular ranges, that matches the search criteria. If there are no matches, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/worksheet-find-all.yaml

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const foundRanges = sheet.findAllOrNullObject("Complete", {
        completeMatch: true,
        matchCase: false
    });

    await context.sync();

    if (foundRanges.isNullObject) {
        console.log("No complete projects");
    } else {
        foundRanges.format.fill.color = "green"
    }
});

getCell(row, column)

Gets the Range object containing the single cell based on row and column numbers. The cell can be outside the bounds of its parent range, so long as it stays within the worksheet grid.

getCell(row: number, column: number): Excel.Range;

Parameters

row: number

The row number of the cell to be retrieved. Zero-indexed.

column: number

The column number of the cell to be retrieved. Zero-indexed.

Returns

Excel.Range

Remarks

Examples

await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const worksheet = context.workbook.worksheets.getItem(sheetName);
    const cell = worksheet.getCell(0,0);
    cell.load('address');
    await context.sync();

    console.log(cell.address);
});

getNext(visibleOnly)

Gets the worksheet that follows this one. If there are no worksheets following this one, this method will throw an error.

getNext(visibleOnly?: boolean): Excel.Worksheet;

Parameters

visibleOnly: boolean

Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Excel.Worksheet

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/reference-worksheets-by-relative-position.yaml

await Excel.run(async (context) => {
    const sheets = context.workbook.worksheets;

    // We don't want to include the default worksheet that was created
    // when the workbook was created, so our "firstSheet" will be the one
    // after the literal first. Note chaining of navigation methods.
    const firstSheet = sheets.getFirst().getNext();
    const lastSheet = sheets.getLast();
    const firstTaxRateRange = firstSheet.getRange("B2");
    const lastTaxRateRange = lastSheet.getRange("B2");

    firstSheet.load("name");
    lastSheet.load("name");
    firstTaxRateRange.load("text");
    lastTaxRateRange.load("text");

    await context.sync();

    let firstYear = firstSheet.name.substr(5, 4);
    let lastYear = lastSheet.name.substr(5, 4);
    console.log(`Tax Rate change from ${firstYear} to ${lastYear}`, `Tax rate for ${firstYear}: ${firstTaxRateRange.text[0][0]}\nTax rate for ${lastYear}: ${lastTaxRateRange.text[0][0]}`)

    await context.sync();
});

getNextOrNullObject(visibleOnly)

Gets the worksheet that follows this one. If there are no worksheets following this one, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getNextOrNullObject(visibleOnly?: boolean): Excel.Worksheet;

Parameters

visibleOnly: boolean

Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Excel.Worksheet

Remarks

getPrevious(visibleOnly)

Gets the worksheet that precedes this one. If there are no previous worksheets, this method will throw an error.

getPrevious(visibleOnly?: boolean): Excel.Worksheet;

Parameters

visibleOnly: boolean

Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Excel.Worksheet

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/54-worksheet/reference-worksheets-by-relative-position.yaml

await Excel.run(async (context) => {
    const sheets = context.workbook.worksheets;
    const currentSheet = sheets.getActiveWorksheet();
    const previousYearSheet = currentSheet.getPrevious();
    const currentTaxDueRange = currentSheet.getRange("C2");
    const previousTaxDueRange = previousYearSheet.getRange("C2");

    currentSheet.load("name");
    previousYearSheet.load("name");
    currentTaxDueRange.load("text");
    previousTaxDueRange.load("text");

    await context.sync();

    let currentYear = currentSheet.name.substr(5, 4);
    let previousYear = previousYearSheet.name.substr(5, 4);
    console.log("Two Year Tax Due Comparison", `Tax due for ${currentYear} was ${currentTaxDueRange.text[0][0]}\nTax due for ${previousYear} was ${previousTaxDueRange.text[0][0]}`)

    await context.sync();
});

getPreviousOrNullObject(visibleOnly)

Gets the worksheet that precedes this one. If there are no previous worksheets, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getPreviousOrNullObject(visibleOnly?: boolean): Excel.Worksheet;

Parameters

visibleOnly: boolean

Optional. If true, considers only visible worksheets, skipping over any hidden ones.

Returns

Excel.Worksheet

Remarks

getRange(address)

Gets the Range object, representing a single rectangular block of cells, specified by the address or name.

getRange(address?: string): Excel.Range;

Parameters

address: string

Optional. The string representing the address or name of the range. For example, "A1:B2". If not specified, the entire worksheet range is returned.

Returns

Excel.Range

Remarks

Examples

// Use the range address to get the range object.
await Excel.run(async (context) => { 
    const sheetName = "Sheet1";
    const rangeAddress = "A1:F8";
    const worksheet = context.workbook.worksheets.getItem(sheetName);
    const range = worksheet.getRange(rangeAddress);
    range.load('cellCount');
    await context.sync();
    
    console.log(range.cellCount);
});

getRangeByIndexes(startRow, startColumn, rowCount, columnCount)

Gets the Range object beginning at a particular row index and column index, and spanning a certain number of rows and columns.

getRangeByIndexes(startRow: number, startColumn: number, rowCount: number, columnCount: number): Excel.Range;

Parameters

startRow: number

Start row (zero-indexed).

startColumn: number

Start column (zero-indexed).

rowCount: number

Number of rows to include in the range.

columnCount: number

Number of columns to include in the range.

Returns

Excel.Range

Remarks

getRanges(address)

Gets the RangeAreas object, representing one or more blocks of rectangular ranges, specified by the address or name.

getRanges(address?: string): Excel.RangeAreas;

Parameters

address: string

Optional. A string containing the comma-separated or semicolon-separated addresses or names of the individual ranges. For example, "A1:B2, A5:B5" or "A1:B2; A5:B5". If not specified, a RangeAreas object for the entire worksheet is returned.

Returns

Excel.RangeAreas

Remarks

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/range-areas.yaml

await Excel.run(async (context) => {

    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const specifiedRanges = sheet.getRanges("D3:D5, G3:G5");
    specifiedRanges.format.fill.color = "pink";

    await context.sync();
})

getUsedRange(valuesOnly)

The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, this function will return the top left cell (i.e. it will not throw an error).

getUsedRange(valuesOnly?: boolean): Excel.Range;

Parameters

valuesOnly: boolean

Optional. If true, considers only cells with values as used cells (ignoring formatting). [Api set: ExcelApi 1.2]

Returns

Excel.Range

Remarks

Examples

await Excel.run(async (context) => { 
    const wSheetName = 'Sheet1';
    const worksheet = context.workbook.worksheets.getItem(wSheetName);
    const usedRange = worksheet.getUsedRange();
    usedRange.load('address');
    await context.sync();
    
    console.log(usedRange.address);
});

getUsedRangeOrNullObject(valuesOnly)

The used range is the smallest range that encompasses any cells that have a value or formatting assigned to them. If the entire worksheet is blank, then this method returns an object with its isNullObject property set to true. For further information, see *OrNullObject methods and properties.

getUsedRangeOrNullObject(valuesOnly?: boolean): Excel.Range;

Parameters

valuesOnly: boolean

Optional. Considers only cells with values as used cells.

Returns

Excel.Range

Remarks

[ API set: ExcelApi 1.4 ]

load(options)

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

load(options?: Excel.Interfaces.WorksheetLoadOptions): Excel.Worksheet;

Parameters

options: Excel.Interfaces.WorksheetLoadOptions

Provides options for which properties of the object to load.

Returns

Excel.Worksheet

load(propertyNames)

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

load(propertyNames?: string | string[]): Excel.Worksheet;

Parameters

propertyNames: string | string[]

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

Returns

Excel.Worksheet

Examples

// Get worksheet properties based on sheet name.
await Excel.run(async (context) => { 
    const wSheetName = 'Sheet1';
    const worksheet = context.workbook.worksheets.getItem(wSheetName);
    worksheet.load('position')
    await context.sync();
    
    console.log(worksheet.position);
});

load(propertyNamesAndPaths)

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

load(propertyNamesAndPaths?: {
            select?: string;
            expand?: string;
        }): Excel.Worksheet;

Parameters

propertyNamesAndPaths: { select?: string; expand?: string; }

propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

Excel.Worksheet

replaceAll(text, replacement, criteria)

Finds and replaces the given string based on the criteria specified within the current worksheet.

replaceAll(text: string, replacement: string, criteria: Excel.ReplaceCriteria): OfficeExtension.ClientResult<number>;

Parameters

text: string

String to find.

replacement: string

The string that replaces the original string.

criteria: Excel.ReplaceCriteria

Additional replacement criteria.

Returns

OfficeExtension.ClientResult<number>

The number of replacements performed.

Remarks

set(properties, options)

Sets multiple properties of an object at the same time. You can pass either a plain object with the appropriate properties, or another API object of the same type.

set(properties: Interfaces.WorksheetUpdateData, options?: OfficeExtension.UpdateOptions): void;

Parameters

properties: Excel.Interfaces.WorksheetUpdateData

A JavaScript object with properties that are structured isomorphically to the properties of the object on which the method is called.

options: OfficeExtension.UpdateOptions

Provides an option to suppress errors if the properties object tries to set any read-only properties.

Returns

void

Examples

// Set the color and name of the current worksheet.
await Excel.run(async (context) => {
  const activeSheet = context.workbook.worksheets.getActiveWorksheet();
  activeSheet.set({
    tabColor: "yellow",
    name: "MySheet"
  });

  await context.sync();
});

set(properties)

Sets multiple properties on the object at the same time, based on an existing loaded object.

set(properties: Excel.Worksheet): void;

Parameters

properties: Excel.Worksheet

Returns

void

showOutlineLevels(rowLevels, columnLevels)

Shows row or column groups by their outline levels. Outlines groups and summarizes a list of data in the worksheet. The rowLevels and columnLevels parameters specify how many levels of the outline will be displayed. The acceptable argument range is between 0 and 8. A value of 0 does not change the current display. A value greater than the current number of levels displays all the levels.

showOutlineLevels(rowLevels: number, columnLevels: number): void;

Parameters

rowLevels: number

The number of row levels of an outline to display.

columnLevels: number

The number of column levels of an outline to display.

Returns

void

Remarks

OfficeExtension.EventHandlers<Excel.WorksheetActivatedEventArgs>

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/42-range/outline.yaml

Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // This shows the top 3 outline levels; collapsing any additional sublevels.
    sheet.showOutlineLevels(3, 3);
    await context.sync();
});

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Worksheet object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.WorksheetData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.WorksheetData;

Returns

Excel.Interfaces.WorksheetData

Event Details

onActivated

Occurs when the worksheet is activated.

readonly onActivated: OfficeExtension.EventHandlers<Excel.WorksheetActivatedEventArgs>;

Event Type

Remarks

OfficeExtension.EventHandlers<Excel.WorksheetCalculatedEventArgs>

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onActivated.add(function (event) {
        return Excel.run(async (context) => {
            console.log("The activated worksheet ID is: " + event.worksheetId);
            await context.sync();
        });
    });
    await context.sync();
});

onCalculated

Occurs when the worksheet is calculated.

readonly onCalculated: OfficeExtension.EventHandlers<Excel.WorksheetCalculatedEventArgs>;

Event Type

Remarks

[ API set: ExcelApi 1.8 ]

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onCalculated.add(function (event) {
        return Excel.run(async (context) => {
            console.log("The worksheet has recalculated.");
            await context.sync();
        });
    });
    await context.sync();
});

onChanged

Occurs when data changes in a specific worksheet.

readonly onChanged: OfficeExtension.EventHandlers<Excel.WorksheetChangedEventArgs>;

Event Type

OfficeExtension.EventHandlers<Excel.WorksheetChangedEventArgs>

Remarks

OfficeExtension.EventHandlers<Excel.WorksheetColumnSortedEventArgs>

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/events-worksheet.yaml

await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onChanged.add(onChange);
    await context.sync();

    console.log("Added a worksheet-level data-changed event handler.");
});

onColumnSorted

Occurs when one or more columns have been sorted. This happens as the result of a left to right sort operation.

readonly onColumnSorted: OfficeExtension.EventHandlers<Excel.WorksheetColumnSortedEventArgs>;

Event Type

Remarks

OfficeExtension.EventHandlers<Excel.WorksheetDeactivatedEventArgs>

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/event-column-and-row-sort.yaml

await Excel.run(async (context) => {
    console.log("Adding column handler");
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // This will fire whenever a column has been moved as the result of a sort action.
    sheet.onColumnSorted.add((event) => {
        return Excel.run((context) => {
            console.log("Column sorted: " + event.address);
            const sheet = context.workbook.worksheets.getActiveWorksheet();

            // Clear formatting for section, then highlight the sorted area.
            sheet.getRange("A1:E5").format.fill.clear();
            if (event.address !== "") {
                sheet.getRanges(event.address).format.fill.color = "yellow";
            }

            return context.sync();
        });
    });
});

onDeactivated

Occurs when the worksheet is deactivated.

readonly onDeactivated: OfficeExtension.EventHandlers<Excel.WorksheetDeactivatedEventArgs>;

Event Type

Remarks

OfficeExtension.EventHandlers<Excel.WorksheetFilteredEventArgs>

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onDeactivated.add(function (event) {
        return Excel.run(async (context) => {
            console.log("The deactivated worksheet is: " + event.worksheetId);
            await context.sync();
        });
    });
    await context.sync();
});

onFiltered

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Occurs when a filter is applied on a specific worksheet.

readonly onFiltered: OfficeExtension.EventHandlers<Excel.WorksheetFilteredEventArgs>;

Event Type

Remarks

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

onFormatChanged

Occurs when format changed on a specific worksheet.

readonly onFormatChanged: OfficeExtension.EventHandlers<Excel.WorksheetFormatChangedEventArgs>;

Event Type

OfficeExtension.EventHandlers<Excel.WorksheetFormatChangedEventArgs>

Remarks

OfficeExtension.EventHandlers<Excel.WorksheetFormulaChangedEventArgs>

onFormulaChanged

Occurs when one or more formulas are changed in this worksheet. This event is for when the formula itself changes, not the data value resulting from the formula's calculation.

readonly onFormulaChanged: OfficeExtension.EventHandlers<Excel.WorksheetFormulaChangedEventArgs>;

Event Type

Remarks

[ API set: ExcelApi 1.13 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/events-formula-changed.yaml

await Excel.run(async (context) => {
  // Retrieve the worksheet named "Sample".
  let sheet = context.workbook.worksheets.getItem("Sample");
  
  // Register the formula changed event handler for this worksheet.
  sheet.onFormulaChanged.add(formulaChangeHandler);
  await context.sync();
  
  console.log("Registered a formula changed event handler for this worksheet.");
});

...

async function formulaChangeHandler(event: Excel.WorksheetFormulaChangedEventArgs) {
  await Excel.run(async (context) => {
    // Retrieve details about the formula change event.
    const cellAddress = event.formulaDetails[0].cellAddress;
    const previousFormula = event.formulaDetails[0].previousFormula;
    const source = event.source;
    
    // Print out the change event details.
    console.log(
      `The formula in cell ${cellAddress} changed. 
      The previous formula was: ${previousFormula}. 
      The source of the change was: ${source}.`
    );
  });
}

onNameChanged

Occurs when the worksheet name is changed.

readonly onNameChanged: OfficeExtension.EventHandlers<Excel.WorksheetNameChangedEventArgs>;

Event Type

OfficeExtension.EventHandlers<Excel.WorksheetNameChangedEventArgs>

Remarks

[ API set: ExcelApi 1.17 ]

onProtectionChanged

Occurs when the worksheet protection state is changed.

readonly onProtectionChanged: OfficeExtension.EventHandlers<Excel.WorksheetProtectionChangedEventArgs>;

Event Type

OfficeExtension.EventHandlers<Excel.WorksheetProtectionChangedEventArgs>

Remarks

[ API set: ExcelApi 1.14 ]

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/events-worksheet-protection.yaml

// This function registers an event handler for the onProtectionChanged event of a worksheet.
await Excel.run(async (context) => {
    // Set "Sample" as the active worksheet.
    context.workbook.worksheets.getItemOrNullObject("Sample").delete();
    const sheet = context.workbook.worksheets.add("Sample");
    sheet.activate();

    // Register the onProtectionChanged event handler.
    sheet.onProtectionChanged.add(checkProtection);
    await context.sync();
    console.log("Added a worksheet protection change event handler.");
});

...

async function checkProtection(event: Excel.WorksheetProtectionChangedEventArgs) {
    // This function is an event handler that returns the protection status of a worksheet
    // and information about the changed worksheet.
    await Excel.run(async (context) => {
        const protectionStatus = event.isProtected;
        const worksheetId = event.worksheetId;
        const source = event.source;
        console.log("Protection status changed. Protection status is now: " + protectionStatus + ".");
        console.log("    ID of changed worksheet: " + worksheetId + ".");
        console.log("    Source of change event: " + source + ".");
    });
}

onRowHiddenChanged

Occurs when the hidden state of one or more rows has changed on a specific worksheet.

readonly onRowHiddenChanged: OfficeExtension.EventHandlers<Excel.WorksheetRowHiddenChangedEventArgs>;

Event Type

OfficeExtension.EventHandlers<Excel.WorksheetRowHiddenChangedEventArgs>

Remarks

[ API set: ExcelApi 1.11 ]

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.onRowHiddenChanged.add(function (event) {
        return Excel.run(async (context) => {
            console.log(`Row ${event.address} is now ${event.changeType}`);
            await context.sync();
        });
    });
    await context.sync();
});

onRowSorted

Occurs when one or more rows have been sorted. This happens as the result of a top-to-bottom sort operation.

readonly onRowSorted: OfficeExtension.EventHandlers<Excel.WorksheetRowSortedEventArgs>;

Event Type

OfficeExtension.EventHandlers<Excel.WorksheetRowSortedEventArgs>

Remarks

OfficeExtension.EventHandlers<Excel.WorksheetSelectionChangedEventArgs>

Examples

// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/30-events/event-column-and-row-sort.yaml

await Excel.run(async (context) => {
    console.log("Adding row handler");
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // This will fire whenever a row has been moved as the result of a sort action.
    sheet.onRowSorted.add((event) => {
        return Excel.run((context) => {
            console.log("Row sorted: " + event.address);
            const sheet = context.workbook.worksheets.getActiveWorksheet();

            // Clear formatting for section, then highlight the sorted area.
            sheet.getRange("A1:E5").format.fill.clear();
            if (event.address !== "") {
                sheet.getRanges(event.address).format.fill.color = "yellow";
            }

            return context.sync();
        });
    });
});

onSelectionChanged

Occurs when the selection changes on a specific worksheet.

readonly onSelectionChanged: OfficeExtension.EventHandlers<Excel.WorksheetSelectionChangedEventArgs>;

Event Type

Remarks

OfficeExtension.EventHandlers<Excel.WorksheetSingleClickedEventArgs>

Examples

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onSelectionChanged.add(function (event) {
        return Excel.run(async (context) => {
            console.log("The selected range has changed to: " + event.address);
            await context.sync();
        });
    });
    await context.sync();
});

onSingleClicked

Occurs when a left-clicked/tapped action happens in the worksheet. This event will not be fired when clicking in the following cases:

The user drags the mouse for multi-selection.
The user selects a cell in the mode when cell arguments are selected for formula references.

readonly onSingleClicked: OfficeExtension.EventHandlers<Excel.WorksheetSingleClickedEventArgs>;

Event Type

Remarks