ExcelScript.Worksheet interface

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

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.

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)

Create 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.

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.

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 function will return an object with its isNullObject property set to true.

getCharts()

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

getComment(commentId)

Gets a comment from the collection based on its ID.

getCommentByCell(cellAddress)

Gets the comment from the specified cell.

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.

getNamedItem(name)

Gets a NamedItem object using its name. If the object does not exist, then this function will return an object with its isNullObject property set to true.

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 will return an object with its isNullObject property set to true.

getPageLayout()

Gets the PageLayout object of the worksheet.

getPivotTable(name)

Gets a PivotTable by name. If the PivotTable does not exist, will return a null object.

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, this method will return a null objet.

getProtection()

Returns 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.

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 function will return an object with its isNullObject property set to true.

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 "#123456" When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.

getTable(key)

Gets a table by Name or ID. If the table doesn't exist, then this function will return an object with its isNullObject property set to true.

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 function will return an object with its isNullObject property set to true.

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.

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 "#123456" 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 group and summarize 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

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

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

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

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 ID or shape objects.

Returns

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

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

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

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

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

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

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/id of a PivotTable/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 id/name of TableColumn.

slicerDestination

string | ExcelScript.Worksheet

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

Returns

addTable(address, hasHeaders)

Create 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

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 header shifting the data down by one row.

Returns

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

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

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

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

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

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

getAutoFilter()

Represents the AutoFilter object of the worksheet.

getAutoFilter(): AutoFilter;

Returns

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

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 function will return an object with its isNullObject property set to true.

getChart(name: string): Chart | undefined;

Parameters

name

string

Name of the chart to be retrieved.

Returns

ExcelScript.Chart | undefined

getCharts()

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

getCharts(): Chart[];

Returns

getComment(commentId)

Gets a comment from the collection based on its ID.

getComment(commentId: string): Comment;

Parameters

commentId

string

The identifier for the comment.

Returns

getCommentByCell(cellAddress)

Gets the comment from the specified cell.

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

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

getComments()

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

getComments(): Comment[];

Returns

getCustomProperties()

Gets a collection of worksheet-level custom properties.

getCustomProperties(): WorksheetCustomProperty[];

Returns

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

getHorizontalPageBreaks()

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

getHorizontalPageBreaks(): PageBreak[];

Returns

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.

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 function will return an object with its isNullObject property set to true.

getNamedItem(name: string): NamedItem | undefined;

Parameters

name

string

Nameditem name.

Returns

ExcelScript.NamedItem | undefined

getNames()

Collection of names scoped to the current worksheet.

getNames(): NamedItem[];

Returns

getNext(visibleOnly)

Gets the worksheet that follows this one. If there are no worksheets following this one, then this method will return an object with its isNullObject property set to true.

getNext(visibleOnly?: boolean): Worksheet;

Parameters

visibleOnly

boolean

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

Returns

getPageLayout()

Gets the PageLayout object of the worksheet.

getPageLayout(): PageLayout;

Returns

getPivotTable(name)

Gets a PivotTable by name. If the PivotTable does not exist, will return a null object.

getPivotTable(name: string): PivotTable | undefined;

Parameters

name

string

Name of the PivotTable to be retrieved.

Returns

getPivotTables()

Collection of PivotTables that are part of the worksheet.

getPivotTables(): PivotTable[];

Returns

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, this method will return a null objet.

getPrevious(visibleOnly?: boolean): Worksheet;

Parameters

visibleOnly

boolean

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

Returns

getProtection()

Returns sheet protection object for a worksheet.

getProtection(): WorksheetProtection;

Returns

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

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

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 addresses or names of the individual ranges. For example, "A1:B2, A5:B5". If not specified, an RangeArea object for the entire worksheet is returned.

Returns

getShape(key)

Gets a shape using its Name or ID.

getShape(key: string): Shape;

Parameters

key

string

Name or ID of the shape to be retrieved.

Returns

getShapes()

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

getShapes(): Shape[];

Returns

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 function will return an object with its isNullObject property set to true.

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

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 "#123456" When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.

getTabColor(): string;

Returns

string

getTable(key)

Gets a table by Name or ID. If the table doesn't exist, then this function will return an object with its isNullObject property set to true.

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

getUsedRange(valuesOnly)

getUsedRange(valuesOnly?: boolean): Range;

Parameters

valuesOnly

boolean

Optional. Considers only cells with values as used cells.

Returns

getVerticalPageBreaks()

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

getVerticalPageBreaks(): PageBreak[];

Returns

getVisibility()

The Visibility of the worksheet.

getVisibility(): SheetVisibility;

Returns

getWorksheetCustomProperty(key)

Gets a custom property object by its key, which is case-insensitive. If the custom property doesn't exist, then this function will return an object with its isNullObject property set to true.

getWorksheetCustomProperty(
            key: string
        ): WorksheetCustomProperty | undefined;

Parameters

key

string

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

Returns

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

String to replace the original with.

criteria
ExcelScript.ReplaceCriteria

Additional Replace 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.

setName(name: string): void;

Parameters

name

string

Returns

void

setPosition(position)

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

setPosition(position: number): void;

Parameters

position

number

Returns

void

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)

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.

setStandardWidth(standardWidth: number): void;

Parameters

standardWidth

number

Returns

void

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 "#123456" When setting the color, use an empty-string to set an "auto" color, or a real color otherwise.

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

Returns

void

showOutlineLevels(rowLevels, columnLevels)

Shows row or column groups by their outline levels. Outlines group and summarize 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