ExcelScript.Worksheet interface

Activate the worksheet in the Excel UI.

activate(): void;

Returns

Creates a new chart.

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

Parameters

Returns

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

Returns

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

addGeometricShape(geometricShapeType: GeometricShapeType): Shape;

Parameters

Returns

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

Returns

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

addHorizontalPageBreak(pageBreakRange: Range | string): PageBreak;

Parameters

Returns

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

Returns

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

Returns

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

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

Parameters

Returns

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

Returns

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

Returns

Adds a new slicer to the workbook.

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

Parameters

Returns

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

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

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

Returns

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

addVerticalPageBreak(pageBreakRange: Range | string): PageBreak;

Parameters

Returns

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

Returns

Calculates all cells on a worksheet.

calculate(markAllDirty: boolean): void;

Parameters

Returns

Copies a worksheet and places it at the specified position.

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

Parameters

Returns

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

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

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

Returns

Represents the AutoFilter object of the worksheet.

getAutoFilter(): AutoFilter;

Returns

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

Returns

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

Returns

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

getCharts(): Chart[];

Returns

Gets a comment from the collection based on its ID.

getComment(commentId: string): Comment;

Parameters

Returns

Gets the comment from the specified cell.

getCommentByCell(cellAddress: Range | string): Comment;

Parameters

Returns

Gets the comment to which the given reply is connected.

getCommentByReplyId(replyId: string): Comment;

Parameters

Returns

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

getComments(): Comment[];

Returns

Gets a collection of worksheet-level custom properties.

getCustomProperties(): WorksheetCustomProperty[];

Returns

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

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

getFreezePanes(): WorksheetFreezePanes;

Returns

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

getHorizontalPageBreaks(): PageBreak[];

Returns

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

The display name of the worksheet.

getName(): string;

Returns

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

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

Returns

Collection of names scoped to the current worksheet.

getNames(): NamedItem[];

Returns

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

Returns

Gets the PageLayout object of the worksheet.

getPageLayout(): PageLayout;

Returns

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

getPivotTable(name: string): PivotTable | undefined;

Parameters

Returns

Collection of PivotTables that are part of the worksheet.

getPivotTables(): PivotTable[];

Returns

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

getPosition(): number;

Returns

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

Returns

Returns sheet protection object for a worksheet.

getProtection(): WorksheetProtection;

Returns

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

getRange(address?: string): Range;

Parameters

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

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

Returns

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

getRanges(address?: string): RangeAreas;

Parameters

Returns

Gets a shape using its Name or ID.

getShape(key: string): Shape;

Parameters

Returns

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

getShapes(): Shape[];

Returns

Specifies if gridlines are visible to the user.

getShowGridlines(): boolean;

Returns

Specifies if headings are visible to the user.

getShowHeadings(): boolean;

Returns

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

Returns

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

getSlicers(): Slicer[];

Returns

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

getStandardHeight(): number;

Returns

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

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

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

Returns

Collection of tables that are part of the worksheet.

getTables(): Table[];

Returns

getUsedRange(valuesOnly?: boolean): Range;

Parameters

Returns

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

getVerticalPageBreaks(): PageBreak[];

Returns

The Visibility of the worksheet.

getVisibility(): SheetVisibility;

Returns

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

Returns

Refreshes all the pivot tables in the collection.

refreshAllPivotTables(): void;

Returns

Resets all manual page breaks in the collection.

removeAllHorizontalPageBreaks(): void;

Returns

Resets all manual page breaks in the collection.

removeAllVerticalPageBreaks(): void;

Returns

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

replaceAll(
            text: string,
            replacement: string,
            criteria: ReplaceCriteria
        ): number;

Parameters

Returns

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

Returns

The display name of the worksheet.

setName(name: string): void;

Parameters

Returns

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

setPosition(position: number): void;

Parameters

Returns

Specifies if gridlines are visible to the user.

setShowGridlines(showGridlines: boolean): void;

Parameters

Returns

Specifies if headings are visible to the user.

setShowHeadings(showHeadings: boolean): void;

Parameters

Returns

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

Returns

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

Returns

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

The Visibility of the worksheet.

setVisibility(visibility: SheetVisibility): void;

Parameters

Returns

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

Returns