ExcelScript.Worksheet interface

Reference

Package:: ExcelScript

An Excel worksheet is a grid of cells. It can contain data, tables, charts, etc.

Remarks

Examples

/**
 * This script creates a new worksheet named "Plum" and sets its tab color to purple.
 */
function main(workbook: ExcelScript.Workbook) {
  const newSheet = workbook.addWorksheet("Plum")
  newSheet.setTabColor("purple");
}

Methods

activate()	Activate the worksheet in the Excel UI.
addChart(type, sourceData, seriesBy)	Creates a new chart.
addComment(cellAddress, content, contentType)	Creates a new comment with the given content on the given cell. An `InvalidArgument` error is thrown if the provided range is larger than one cell.
addGeometricShape(geometricShapeType)	Adds a geometric shape to the worksheet. Returns a `Shape` object that represents the new shape.
addGroup(values)	Groups a subset of shapes in this collection's worksheet. Returns a `Shape` object that represents the new group of shapes.
addHorizontalPageBreak(pageBreakRange)	Adds a page break before the top-left cell of the range specified.
addImage(base64ImageString)	Creates an image from a base64-encoded string and adds it to the worksheet. Returns the `Shape` object that represents the new image.
addLine(startLeft, startTop, endLeft, endTop, connectorType)	Adds a line to worksheet. Returns a `Shape` object that represents the new line.
addNamedItem(name, reference, comment)	Adds a new name to the collection of the given scope.
addNamedItemFormulaLocal(name, formula, comment)	Adds a new name to the collection of the given scope using the user's locale for the formula.
addNamedSheetView(name)	Creates a new sheet view with the given name.
addPivotTable(name, source, destination)	Add a PivotTable based on the specified source data and insert it at the top-left cell of the destination range.
addSlicer(slicerSource, sourceField, slicerDestination)	Adds a new slicer to the workbook.
addTable(address, hasHeaders)	Creates a new table. The range object or source address determines the worksheet under which the table will be added. If the table cannot be added (e.g., because the address is invalid, or the table would overlap with another table), an error will be thrown.
addTextBox(text)	Adds a text box to the worksheet with the provided text as the content. Returns a `Shape` object that represents the new text box.
addVerticalPageBreak(pageBreakRange)	Adds a page break before the top-left cell of the range specified.
addWorksheetCustomProperty(key, value)	Adds a new custom property that maps to the provided key. This overwrites existing custom properties with that key.
calculate(markAllDirty)	Calculates all cells on a worksheet.
copy(positionType, 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.
enterTemporaryNamedSheetView()	Creates and activates a new temporary sheet view. Temporary views are removed when closing the application, exiting the temporary view with the exit method, or switching to another sheet view. The temporary sheet view can also be accessed with the empty string (""), if the temporary view exists.
exitActiveNamedSheetView()	Exits the currently active sheet view.
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.
getActiveNamedSheetView()	Gets the worksheet's currently active sheet view.
getAutoFilter()	Represents the `AutoFilter` object of the worksheet.
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.
getChart(name)	Gets a chart using its name. If there are multiple charts with the same name, the first one will be returned. If the chart doesn't exist, then this method returns `undefined`.
getCharts()	Returns a collection of charts that are part of the worksheet.
getComment(commentId)	Gets a comment from the collection based on its ID. If the comment object does not exist, then this method returns `undefined`.
getCommentByCell(cellAddress)	Gets the comment from the specified cell. If there is no comment in the cell, an error is thrown.
getCommentByReplyId(replyId)	Gets the comment to which the given reply is connected.
getComments()	Returns a collection of all the Comments objects on the worksheet.
getCustomProperties()	Gets a collection of worksheet-level custom properties.
getEnableCalculation()	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.
getFreezePanes()	Gets an object that can be used to manipulate frozen panes on the worksheet.
getHorizontalPageBreaks()	Gets the horizontal page break collection for the worksheet. This collection only contains manual page breaks.
getId()	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.
getName()	The display name of the worksheet. The name must be fewer than 32 characters.
getNamedItem(name)	Gets a `NamedItem` object using its name. If the object does not exist, then this method returns `undefined`.
getNamedSheetView(key)	Gets a sheet view using its name. If the sheet view object does not exist, then this method returns `undefined`.
getNamedSheetViews()	Returns a collection of sheet views that are present in the worksheet.
getNames()	Collection of names scoped to the current worksheet.
getNext(visibleOnly)	Gets the worksheet that follows this one. If there are no worksheets following this one, then this method returns `undefined`.
getPageLayout()	Gets the `PageLayout` object of the worksheet.
getPivotTable(name)	Gets a PivotTable by name. If the PivotTable does not exist, then this method returns `undefined`.
getPivotTables()	Collection of PivotTables that are part of the worksheet.
getPosition()	The zero-based position of the worksheet within the workbook.
getPrevious(visibleOnly)	Gets the worksheet that precedes this one. If there are no previous worksheets, then this method returns `undefined`.
getProtection()	Returns the sheet protection object for a worksheet.
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.
getShape(key)	Gets a shape using its name or ID. If the shape object does not exist, then this method returns `undefined`.
getShapes()	Returns the collection of all the Shape objects on the worksheet.
getShowGridlines()	Specifies if gridlines are visible to the user.
getShowHeadings()	Specifies if headings are visible to the user.
getSlicer(key)	Gets a slicer using its name or ID. If the slicer doesn't exist, then this method returns `undefined`.
getSlicers()	Returns a collection of slicers that are part of the worksheet.
getStandardHeight()	Returns the standard (default) height of all the rows in the worksheet, in points.
getStandardWidth()	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.
getTabColor()	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.
getTabId()	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").
getTable(key)	Gets a table by name or ID. If the table doesn't exist, then this method returns `undefined`.
getTables()	Collection of tables that are part of the worksheet.
getUsedRange(valuesOnly)
getVerticalPageBreaks()	Gets the vertical page break collection for the worksheet. This collection only contains manual page breaks.
getVisibility()	The visibility of the worksheet.
getWorksheetCustomProperty(key)	Gets a custom property object by its key, which is case-insensitive. If the custom property doesn't exist, then this method returns `undefined`.
refreshAllPivotTables()	Refreshes all the pivot tables in the collection.
removeAllHorizontalPageBreaks()	Resets all manual page breaks in the collection.
removeAllVerticalPageBreaks()	Resets all manual page breaks in the collection.
replaceAll(text, replacement, criteria)	Finds and replaces the given string based on the criteria specified within the current worksheet.
setEnableCalculation(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.
setName(name)	The display name of the worksheet. The name must be fewer than 32 characters.
setPosition(position)	The zero-based position of the worksheet within the workbook.
setShowGridlines(showGridlines)	Specifies if gridlines are visible to the user.
setShowHeadings(showHeadings)	Specifies if headings are visible to the user.
setStandardWidth(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.
setTabColor(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.
setVisibility(visibility)	The visibility of the worksheet.
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.

Method Details

activate()

Activate the worksheet in the Excel UI.

activate(): void;

Returns

void

Examples

/**
 * This script switches the active view to a worksheet named "Data", if it exists.
 */
function main(workbook: ExcelScript.Workbook) {
  // Check if the "Data" worksheet exists.
  let dataWorksheet = workbook.getWorksheet("Data");
  if (dataWorksheet) {
    // Switch to the "Data" worksheet.
    dataWorksheet.activate();
  } else {
    console.log(`No worksheet named "Data" in this workbook.`);
  }
}

addChart(type, sourceData, seriesBy)

Creates a new chart.

addChart(
            type: ChartType,
            sourceData: Range,
            seriesBy?: ChartSeriesBy
        ): Chart;

Parameters

type: ExcelScript.ChartType

Represents the type of a chart. See ExcelScript.ChartType for details.

sourceData: ExcelScript.Range

The Range object corresponding to the source data.

seriesBy: ExcelScript.ChartSeriesBy

Optional. Specifies the way columns or rows are used as data series on the chart. See ExcelScript.ChartSeriesBy for details.

Returns

ExcelScript.Chart

Examples

/**
 * This sample creates a column-clustered chart based on the current worksheet's data.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the data range.
  let range = selectedSheet.getUsedRange();

  // Insert a chart using the data on the current worksheet.
  let chart = selectedSheet.addChart(ExcelScript.ChartType.columnClustered, range);

  // Name the chart for easy access in other scripts.
  chart.setName("ColumnChart");
}

addComment(cellAddress, content, contentType)

Creates a new comment with the given content on the given cell. An InvalidArgument error is thrown if the provided range is larger than one cell.

addComment(
            cellAddress: Range | string,
            content: CommentRichContent | string,
            contentType?: ContentType
        ): Comment;

Parameters

cellAddress: ExcelScript.Range | string

The cell to which the comment is added. This can be a Range object or a string. If it's a string, it must contain the full address, including the sheet name. An InvalidArgument error is thrown if the provided range is larger than one cell.

content: ExcelScript.CommentRichContent | string

The comment's content. This can be either a string or CommentRichContent object. Strings are used for plain text. CommentRichContent objects allow for other comment features, such as mentions.

contentType: ExcelScript.ContentType

Optional. The type of content contained within the comment. The default value is enum ContentType.Plain.

Returns

ExcelScript.Comment

addGeometricShape(geometricShapeType)

Adds a geometric shape to the worksheet. Returns a Shape object that represents the new shape.

addGeometricShape(geometricShapeType: GeometricShapeType): Shape;

Parameters

geometricShapeType: ExcelScript.GeometricShapeType

Represents the type of the geometric shape. See ExcelScript.GeometricShapeType for details.

Returns

ExcelScript.Shape

Examples

/**
 * This script creates a hexagon shape on the current worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  const currentSheet = workbook.getActiveWorksheet();
  const hexagon: ExcelScript.Shape = 
    currentSheet.addGeometricShape(ExcelScript.GeometricShapeType.hexagon);
  
  // Set the hexagon size to 40x40 pixels.
  hexagon.setHeight(40);
  hexagon.setWidth(40);

  // Position the hexagon at [100,100] pixels.
  hexagon.setLeft(100);
  hexagon.setTop(100);
}

addGroup(values)

Groups a subset of shapes in this collection's worksheet. Returns a Shape object that represents the new group of shapes.

addGroup(values: Array<string | Shape>): Shape;

Parameters

values: Array<string | ExcelScript.Shape>

An array of shape IDs or shape objects.

Returns

ExcelScript.Shape

addHorizontalPageBreak(pageBreakRange)

Adds a page break before the top-left cell of the range specified.

addHorizontalPageBreak(pageBreakRange: Range | string): PageBreak;

Parameters

pageBreakRange: ExcelScript.Range | string

The range immediately after the page break to be added.

Returns

ExcelScript.PageBreak

addImage(base64ImageString)

Creates an image from a base64-encoded string and adds it to the worksheet. Returns the Shape object that represents the new image.

addImage(base64ImageString: string): Shape;

Parameters

base64ImageString: string

A base64-encoded string representing an image in either JPEG or PNG format.

Returns

ExcelScript.Shape

Examples

/**
 * This sample copies an image from a URL. 
 * This could be used to copy photos that a colleague stored in a shared folder to a related workbook.
 */
async function main(workbook: ExcelScript.Workbook) {
  // Fetch the image from a URL.
  const link = "https://raw.githubusercontent.com/OfficeDev/office-scripts-docs/master/docs/images/git-octocat.png";
  const response = await fetch(link);

  // Store the response as an ArrayBuffer, since it is a raw image file.
  const data = await response.arrayBuffer();

  // Convert the image data into a base64-encoded string.
  const image = convertToBase64(data);

  // Add the image to the current worksheet.
  workbook.getActiveWorksheet().addImage(image);
}

/**
 * Converts an ArrayBuffer containing a .png image into a base64-encoded string.
 */
function convertToBase64(input: ArrayBuffer) {
  const uInt8Array = new Uint8Array(input);
  const count = uInt8Array.length;

  // Allocate the necessary space up front.
  const charCodeArray = new Array<string>(count) 
  
  // Convert every entry in the array to a character.
  for (let i = count; i >= 0; i--) { 
    charCodeArray[i] = String.fromCharCode(uInt8Array[i]);
  }

  // Convert the characters to base64.
  const base64 = btoa(charCodeArray.join(''));
  return base64;
}

addLine(startLeft, startTop, endLeft, endTop, connectorType)

Adds a line to worksheet. Returns a Shape object that represents the new line.

addLine(
            startLeft: number,
            startTop: number,
            endLeft: number,
            endTop: number,
            connectorType?: ConnectorType
        ): Shape;

Parameters

startLeft: number

The distance, in points, from the start of the line to the left side of the worksheet.

startTop: number

The distance, in points, from the start of the line to the top of the worksheet.

endLeft: number

The distance, in points, from the end of the line to the left of the worksheet.

endTop: number

The distance, in points, from the end of the line to the top of the worksheet.

connectorType: ExcelScript.ConnectorType

Represents the connector type. See ExcelScript.ConnectorType for details.

Returns

ExcelScript.Shape

addNamedItem(name, reference, comment)

Adds a new name to the collection of the given scope.

addNamedItem(
            name: string,
            reference: Range | string,
            comment?: string
        ): NamedItem;

Parameters

name: string

The name of the named item.

reference: ExcelScript.Range | string

The formula or the range that the name will refer to.

comment: string

Optional. The comment associated with the named item.

Returns

ExcelScript.NamedItem

addNamedItemFormulaLocal(name, formula, comment)

Adds a new name to the collection of the given scope using the user's locale for the formula.

addNamedItemFormulaLocal(
            name: string,
            formula: string,
            comment?: string
        ): NamedItem;

Parameters

name: string

The name of the named item.

formula: string

The formula in the user's locale that the name will refer to.

comment: string

Optional. The comment associated with the named item.

Returns

ExcelScript.NamedItem

addNamedSheetView(name)

Creates a new sheet view with the given name.

addNamedSheetView(name: string): NamedSheetView;

Parameters

name: string

The name of the sheet view to be created. Throws an error when the provided name already exists, is empty, or is a name reserved by the worksheet.

Returns

ExcelScript.NamedSheetView

addPivotTable(name, source, destination)

Add a PivotTable based on the specified source data and insert it at the top-left cell of the destination range.

addPivotTable(
            name: string,
            source: Range | string | Table,
            destination: Range | string
        ): PivotTable;

Parameters

name: string

The name of the new PivotTable.

source: ExcelScript.Range | string | ExcelScript.Table

The source data for the new PivotTable, this can either be a range (or string address including the worksheet name) or a table.

destination: ExcelScript.Range | string

The cell in the upper-left corner of the PivotTable report's destination range (the range on the worksheet where the resulting report will be placed).

Returns

ExcelScript.PivotTable

Examples

/**
 * This script creates a PivotTable from an existing table and adds it to a new worksheet.
 * This script assumes there is a table in the current worksheet with columns named "Type" and "Sales".
 */
function main(workbook: ExcelScript.Workbook) {
  // Create a PivotTable based on a table in the current worksheet.
  let sheet = workbook.getActiveWorksheet();
  let table = sheet.getTables()[0];

  // Add the PivotTable to a new worksheet.
  let newSheet = workbook.addWorksheet("Pivot");
  let pivotTable = newSheet.addPivotTable("My Pivot", table, "A1");

  // Add fields to the PivotTable to show "Sales" per "Type".
  pivotTable.addRowHierarchy(pivotTable.getHierarchy("Type"));
  pivotTable.addDataHierarchy(pivotTable.getHierarchy("Sales"));

  // Switch to the new worksheet.
  newSheet.activate();
}

addSlicer(slicerSource, sourceField, slicerDestination)

Adds a new slicer to the workbook.

addSlicer(
            slicerSource: string | PivotTable | Table,
            sourceField: string | PivotField | number | TableColumn,
            slicerDestination?: string | Worksheet
        ): Slicer;

Parameters

slicerSource: string | ExcelScript.PivotTable | ExcelScript.Table

The data source that the new slicer will be based on. It can be a PivotTable object, a Table object, or a string. When a PivotTable object is passed, the data source is the source of the PivotTable object. When a Table object is passed, the data source is the Table object. When a string is passed, it is interpreted as the name or ID of a PivotTable or table.

sourceField: string | ExcelScript.PivotField | number | ExcelScript.TableColumn

The field in the data source to filter by. It can be a PivotField object, a TableColumn object, the ID of a PivotField or the name or ID of a TableColumn.

slicerDestination: string | ExcelScript.Worksheet

Optional. The worksheet in which the new slicer will be created. It can be a Worksheet object or the name or ID of a worksheet. This parameter can be omitted if the slicer collection is retrieved from a worksheet.

Returns

ExcelScript.Slicer

Examples

/**
 * This script adds a slicer for an existing PivotTable on the current worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first PivotTable from the current worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const pivot = currentSheet.getPivotTables()[0];

  // Create the slicer. 
  // Note that this assumes "Type" is already added as a hierarchy to the PivotTable.
  const slicer = currentSheet.addSlicer(
    pivot, /* The table or PivotTale to be sliced. */
    pivot.getHierarchy("Type").getFields()[0] /* What source field to use as the slicer options. */
  );

  // Select the items to display.
  slicer.selectItems(["Lemon", "Lime"]);

  // Set the left margin of the slicer.
  slicer.setLeft(400);
}

addTable(address, hasHeaders)

Creates a new table. The range object or source address determines the worksheet under which the table will be added. If the table cannot be added (e.g., because the address is invalid, or the table would overlap with another table), an error will be thrown.

addTable(address: Range | string, hasHeaders: boolean): Table;

Parameters

address: ExcelScript.Range | string

A Range object, or a string address or name of the range representing the data source. If the address does not contain a sheet name, the currently-active sheet is used.

hasHeaders: boolean

A boolean value that indicates whether the data being imported has column labels. If the source does not contain headers (i.e., when this property set to false), Excel will automatically generate a header and shift the data down by one row.

Returns

ExcelScript.Table

Examples

/**
 * This sample creates a table from the current worksheet's used range, then sorts it based on the first column.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Create a table with the used cells.
  let usedRange = selectedSheet.getUsedRange();
  let newTable = selectedSheet.addTable(usedRange, true);

  // Sort the table using the first column.
  newTable.getSort().apply([{ key: 0, ascending: true }]);
}

addTextBox(text)

Adds a text box to the worksheet with the provided text as the content. Returns a Shape object that represents the new text box.

addTextBox(text?: string): Shape;

Parameters

text: string

Represents the text that will be shown in the created text box.

Returns

ExcelScript.Shape

addVerticalPageBreak(pageBreakRange)

Adds a page break before the top-left cell of the range specified.

addVerticalPageBreak(pageBreakRange: Range | string): PageBreak;

Parameters

pageBreakRange: ExcelScript.Range | string

The range immediately after the page break to be added.

Returns

ExcelScript.PageBreak

addWorksheetCustomProperty(key, value)

Adds a new custom property that maps to the provided key. This overwrites existing custom properties with that key.

addWorksheetCustomProperty(
            key: string,
            value: string
        ): WorksheetCustomProperty;

Parameters

key: string

The key that identifies the custom property object. It is case-insensitive.The key is limited to 255 characters (larger values will cause an InvalidArgument error to be thrown.)

value: string

The value of this custom property.

Returns

ExcelScript.WorksheetCustomProperty

calculate(markAllDirty)

Calculates all cells on a worksheet.

calculate(markAllDirty: boolean): void;

Parameters

markAllDirty: boolean

True, to mark all as dirty.

Returns

void

copy(positionType, relativeTo)

Copies a worksheet and places it at the specified position.

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

Parameters

positionType: ExcelScript.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: ExcelScript.Worksheet

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

Returns

ExcelScript.Worksheet

Examples

/**
 * This script duplicates a worksheet named "Template". 
 * The new worksheet is added after the template.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the worksheet named "Template".
  let template = workbook.getWorksheet("Template");

  // Copy the worksheet.
  let newSheet = template.copy(
    ExcelScript.WorksheetPositionType.after,
    template
  );

  // Name the worksheet using the current date.
  let date = new Date(Date.now());
  newSheet.setName(`${date.toDateString()}`);
}

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

Examples

/**
 * The following scripts removes the first worksheet in the workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first worksheet.
  let sheet = workbook.getWorksheets()[0];

  // Remove that worksheet from the workbook.
  sheet.delete();
}

enterTemporaryNamedSheetView()

Creates and activates a new temporary sheet view. Temporary views are removed when closing the application, exiting the temporary view with the exit method, or switching to another sheet view. The temporary sheet view can also be accessed with the empty string (""), if the temporary view exists.

enterTemporaryNamedSheetView(): NamedSheetView;

Returns

ExcelScript.NamedSheetView

exitActiveNamedSheetView()

Exits the currently active sheet view.

exitActiveNamedSheetView(): void;

Returns

void

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: WorksheetSearchCriteria): RangeAreas;

Parameters

text: string

The string to find.

criteria: ExcelScript.WorksheetSearchCriteria

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

Returns

ExcelScript.RangeAreas

Examples

/**
 * This script searches through a worksheet and finds cells containing "No". 
 * Those cells are filled with the color red.
 * Use Range.find instead of Worksheet.findAll when you want to limit the search to a specific range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current, active worksheet.
  let worksheet = workbook.getActiveWorksheet();
  let noCells = worksheet.findAll("No", { completeMatch: true });

  // Set the fill color to red.
  noCells.getFormat().getFill().setColor("red");
}

getActiveNamedSheetView()

Gets the worksheet's currently active sheet view.

getActiveNamedSheetView(): NamedSheetView;

Returns

ExcelScript.NamedSheetView

getAutoFilter()

Represents the AutoFilter object of the worksheet.

getAutoFilter(): AutoFilter;

Returns

ExcelScript.AutoFilter

Examples

/**
 * This script creates an autoFilter on the worksheet that filters out rows based on column values. 
 * The autoFilter filters to only include rows that have a value in column D in the top 10 percentile 
 * (of column D values).
 */
function main(workbook: ExcelScript.Workbook) {
  const currentSheet = workbook.getActiveWorksheet();
  const dataRange = currentSheet.getUsedRange();

  // Add a filter that will only show the rows with the top 10% of values in column D
  // (index 3, assuming the used range spans from at least A:D).
  currentSheet.getAutoFilter().apply(dataRange, 3, {
    criterion1: "10",
    filterOn: ExcelScript.FilterOn.topPercent
  });
}

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): 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

ExcelScript.Range

getChart(name)

Gets a chart using its name. If there are multiple charts with the same name, the first one will be returned. If the chart doesn't exist, then this method returns undefined.

getChart(name: string): Chart | undefined;

Parameters

name: string

Name of the chart to be retrieved.

Returns

ExcelScript.Chart | undefined

Examples

/**
 * This sample moves an existing chart to a specific place on the worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();
  
  // Get an existing chart named "ColumnChart".
  let chart = selectedSheet.getChart("ColumnChart");

  // Place the chart over the range "F1:L13".
  chart.setPosition("F1", "L13");
}

getCharts()

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

getCharts(): Chart[];

Returns

ExcelScript.Chart[]

getComment(commentId)

Gets a comment from the collection based on its ID. If the comment object does not exist, then this method returns undefined.

getComment(commentId: string): Comment | undefined;

Parameters

commentId: string

The identifier for the comment.

Returns

ExcelScript.Comment | undefined

getCommentByCell(cellAddress)

Gets the comment from the specified cell. If there is no comment in the cell, an error is thrown.

getCommentByCell(cellAddress: Range | string): Comment;

Parameters

cellAddress: ExcelScript.Range | string

The cell which the comment is on. This can be a Range object or a string. If it's a string, it must contain the full address, including the sheet name. An InvalidArgument error is thrown if the provided range is larger than one cell.

Returns

ExcelScript.Comment

getCommentByReplyId(replyId)

Gets the comment to which the given reply is connected.

getCommentByReplyId(replyId: string): Comment;

Parameters

replyId: string

The identifier of comment reply.

Returns

ExcelScript.Comment

getComments()

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

getComments(): Comment[];

Returns

ExcelScript.Comment[]

getCustomProperties()

Gets a collection of worksheet-level custom properties.

getCustomProperties(): WorksheetCustomProperty[];

Returns

ExcelScript.WorksheetCustomProperty[]

getEnableCalculation()

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.

getEnableCalculation(): boolean;

Returns

boolean

getFreezePanes()

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

getFreezePanes(): WorksheetFreezePanes;

Returns

ExcelScript.WorksheetFreezePanes

getHorizontalPageBreaks()

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

getHorizontalPageBreaks(): PageBreak[];

Returns

ExcelScript.PageBreak[]

getId()

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.

getId(): string;

Returns

string

getName()

The display name of the worksheet. The name must be fewer than 32 characters.

getName(): string;

Returns

string

Examples

/**
 * This sample gets all the worksheet names in the workbook.
 * It then logs those names to the console.
 */
function main(workbook: ExcelScript.Workbook) {
    // Create an array to hold the worksheet names.
    let worksheetNames = [];

    // Iterate over the worksheet collection in the workbook.
    for (let worksheet of workbook.getWorksheets()) {
        worksheetNames.push(worksheet.getName());
    }

    // Log the array of worksheet names.
    console.log(worksheetNames);
}

getNamedItem(name)

Gets a NamedItem object using its name. If the object does not exist, then this method returns undefined.

getNamedItem(name: string): NamedItem | undefined;

Parameters

name: string

Nameditem name.

Returns

ExcelScript.NamedItem | undefined

getNamedSheetView(key)

Gets a sheet view using its name. If the sheet view object does not exist, then this method returns undefined.

getNamedSheetView(key: string): NamedSheetView | undefined;

Parameters

key: string

The case-sensitive name of the sheet view. Use the empty string ("") to get the temporary sheet view, if the temporary view exists.

Returns

ExcelScript.NamedSheetView | undefined

getNamedSheetViews()

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

getNamedSheetViews(): NamedSheetView[];

Returns

ExcelScript.NamedSheetView[]

getNames()

Collection of names scoped to the current worksheet.

getNames(): NamedItem[];

Returns

ExcelScript.NamedItem[]

getNext(visibleOnly)

Gets the worksheet that follows this one. If there are no worksheets following this one, then this method returns undefined.

getNext(visibleOnly?: boolean): Worksheet;

Parameters

visibleOnly: boolean

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

Returns

ExcelScript.Worksheet

getPageLayout()

Gets the PageLayout object of the worksheet.

getPageLayout(): PageLayout;

Returns

ExcelScript.PageLayout

Examples

/**
 * This script sets the printing orientation for the entire workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Go to each worksheet so the print settings are consistent.
  workbook.getWorksheets().forEach((sheet) => {
    const pageLayout = sheet.getPageLayout();

    // Print every page with a landscape orientation.
    pageLayout.setOrientation(ExcelScript.PageOrientation.landscape);
  });
}

getPivotTable(name)

Gets a PivotTable by name. If the PivotTable does not exist, then this method returns undefined.

getPivotTable(name: string): PivotTable | undefined;

Parameters

name: string

Name of the PivotTable to be retrieved.

Returns

ExcelScript.PivotTable | undefined

getPivotTables()

Collection of PivotTables that are part of the worksheet.

getPivotTables(): PivotTable[];

Returns

ExcelScript.PivotTable[]

getPosition()

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

getPosition(): number;

Returns

number

getPrevious(visibleOnly)

Gets the worksheet that precedes this one. If there are no previous worksheets, then this method returns undefined.

getPrevious(visibleOnly?: boolean): Worksheet;

Parameters

visibleOnly: boolean

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

Returns

ExcelScript.Worksheet

getProtection()

Returns the sheet protection object for a worksheet.

getProtection(): WorksheetProtection;

Returns

ExcelScript.WorksheetProtection

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);
}

getRange(address)

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

getRange(address?: string): 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

ExcelScript.Range

Examples

/**
 * This sample reads the value of A1 and prints it to the console.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the value of cell A1.
  let range = selectedSheet.getRange("A1");
  
  // Print the value of A1.
  console.log(range.getValue());
}

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
        ): 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

ExcelScript.Range

getRanges(address)

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

getRanges(address?: string): 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

ExcelScript.RangeAreas

getShape(key)

Gets a shape using its name or ID. If the shape object does not exist, then this method returns undefined.

getShape(key: string): Shape | undefined;

Parameters

key: string

The name or ID of the shape to be retrieved.

Returns

ExcelScript.Shape | undefined

getShapes()

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

getShapes(): Shape[];

Returns

ExcelScript.Shape[]

getShowGridlines()

Specifies if gridlines are visible to the user.

getShowGridlines(): boolean;

Returns

boolean

getShowHeadings()

Specifies if headings are visible to the user.

getShowHeadings(): boolean;

Returns

boolean

getSlicer(key)

Gets a slicer using its name or ID. If the slicer doesn't exist, then this method returns undefined.

getSlicer(key: string): Slicer | undefined;

Parameters

key: string

Name or ID of the slicer to be retrieved.

Returns

ExcelScript.Slicer | undefined

getSlicers()

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

getSlicers(): Slicer[];

Returns

ExcelScript.Slicer[]

getStandardHeight()

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

getStandardHeight(): number;

Returns

number

getStandardWidth()

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.

getStandardWidth(): number;

Returns

number

getTabColor()

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.

getTabColor(): string;

Returns

string

getTabId()

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").

getTabId(): number;

Returns

number

getTable(key)

Gets a table by name or ID. If the table doesn't exist, then this method returns undefined.

getTable(key: string): Table | undefined;

Parameters

key: string

Name or ID of the table to be retrieved.

Returns

ExcelScript.Table | undefined

getTables()

Collection of tables that are part of the worksheet.

getTables(): Table[];

Returns

ExcelScript.Table[]

getUsedRange(valuesOnly)

getUsedRange(valuesOnly?: boolean): Range;

Parameters

valuesOnly: boolean

Optional. Considers only cells with values as used cells.

Returns

ExcelScript.Range

getVerticalPageBreaks()

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

getVerticalPageBreaks(): PageBreak[];

Returns

ExcelScript.PageBreak[]

getVisibility()

The visibility of the worksheet.

getVisibility(): SheetVisibility;

Returns

ExcelScript.SheetVisibility

getWorksheetCustomProperty(key)

Gets a custom property object by its key, which is case-insensitive. If the custom property doesn't exist, then this method returns undefined.

getWorksheetCustomProperty(
            key: string
        ): WorksheetCustomProperty | undefined;

Parameters

key: string

The key that identifies the custom property object. It is case-insensitive.

Returns

ExcelScript.WorksheetCustomProperty | undefined

refreshAllPivotTables()

Refreshes all the pivot tables in the collection.

refreshAllPivotTables(): void;

Returns

void

removeAllHorizontalPageBreaks()

Resets all manual page breaks in the collection.

removeAllHorizontalPageBreaks(): void;

Returns

void

removeAllVerticalPageBreaks()

Resets all manual page breaks in the collection.

removeAllVerticalPageBreaks(): void;

Returns

void

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: ReplaceCriteria
        ): number;

Parameters

text: string

String to find.

replacement: string

The string that replaces the original string.

criteria: ExcelScript.ReplaceCriteria

Additional replacement criteria.

Returns

number

setEnableCalculation(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.

setEnableCalculation(enableCalculation: boolean): void;

Parameters

enableCalculation: boolean

Returns

void

setName(name)

The display name of the worksheet. The name must be fewer than 32 characters.

setName(name: string): void;

Parameters

name: string

Returns

void

Examples

/**
 * This sample renames a worksheet from "Sheet1" to "SALES".
 */
function main(workbook: ExcelScript.Workbook) {
  // Get a worksheet named "Sheet1".
  const sheet = workbook.getWorksheet('Sheet1'); 

  // Set its name to SALES.
  sheet.setName('SALES');
}

setPosition(position)

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

setPosition(position: number): void;

Parameters

position: number

Returns

void

Examples

/**
 * This sample sets the worksheet named "SALES" as the first sheet in the workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get a worksheet named "SALES".
  const sheet = workbook.getWorksheet('SALES'); 
  // Position the worksheet at the beginning of the workbook.
  sheet.setPosition(0);
}

setShowGridlines(showGridlines)

Specifies if gridlines are visible to the user.

setShowGridlines(showGridlines: boolean): void;

Parameters

showGridlines: boolean

Returns

void

setShowHeadings(showHeadings)

Specifies if headings are visible to the user.

setShowHeadings(showHeadings: boolean): void;

Parameters

showHeadings: boolean

Returns

void

setStandardWidth(standardWidth)

setStandardWidth(standardWidth: number): void;

Parameters

standardWidth: number

Returns

void

setTabColor(tabColor)

setTabColor(tabColor: string): void;

Parameters

tabColor: string

Returns

void

Examples

/**
 * This script sets the tab color of every worksheet in the workbook to red.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get all the worksheets in the workbook. 
  let sheets = workbook.getWorksheets();
  
  // Set the tab color of each worksheet to a random color.
  for (let sheet of sheets) {    
    // Set the color of the current worksheet's tab to red.
    sheet.setTabColor("red");
  }
}

setVisibility(visibility)

The visibility of the worksheet.

setVisibility(visibility: SheetVisibility): void;

Parameters

visibility: ExcelScript.SheetVisibility

Returns

void

Examples

/**
 * This script unhides all the worksheets in the workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Iterate over each worksheet.
  workbook.getWorksheets().forEach((worksheet) => {
    // Set the worksheet visibility to visible.
    worksheet.setVisibility(ExcelScript.SheetVisibility.visible);
  });
}

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

Share via

ExcelScript.Worksheet interface

Remarks

Examples

Methods

Method Details

activate()

Returns

Examples

addChart(type, sourceData, seriesBy)

Parameters

Returns

Examples

addComment(cellAddress, content, contentType)

Parameters

Returns

addGeometricShape(geometricShapeType)

Parameters

Returns

Examples

addGroup(values)

Parameters

Returns

addHorizontalPageBreak(pageBreakRange)

Parameters

Returns

addImage(base64ImageString)

Parameters

Returns

Examples

addLine(startLeft, startTop, endLeft, endTop, connectorType)

Parameters

Returns

addNamedItem(name, reference, comment)

Parameters

Returns

addNamedItemFormulaLocal(name, formula, comment)

Parameters

Returns

addNamedSheetView(name)

Parameters

Returns

addPivotTable(name, source, destination)

Parameters

Returns

Examples

addSlicer(slicerSource, sourceField, slicerDestination)

Parameters

Returns

Examples

addTable(address, hasHeaders)

Parameters

Returns

Examples

addTextBox(text)

Parameters

Returns

addVerticalPageBreak(pageBreakRange)

Parameters

Returns

addWorksheetCustomProperty(key, value)

Parameters

Returns

calculate(markAllDirty)

Parameters

Returns

copy(positionType, relativeTo)

Parameters

Returns

Examples

delete()

Returns

Examples

enterTemporaryNamedSheetView()

Returns

exitActiveNamedSheetView()

Returns

findAll(text, criteria)

Parameters

Returns