ExcelScript.Worksheet interface

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.

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 method returns undefined.

getNamedSheetView(key)

Gets a sheet view using its name.

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.

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.

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Examples

/**
 * This script searches through a worksheet and finds cells containing "No". 
 * Those cells are filled 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

getAutoFilter()

Represents the AutoFilter object of the worksheet.

getAutoFilter(): AutoFilter;

Returns

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

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

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

getNamedSheetView(key: string): NamedSheetView;

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

getNamedSheetViews()

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

getNamedSheetViews(): NamedSheetView[];

Returns

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 returns undefined.

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, then this method returns undefined.

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, 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

getProtection()

Returns the sheet protection object for a worksheet.

getProtection(): WorksheetProtection;

Returns

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

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

getShape(key)

Gets a shape using its name or ID.

getShape(key: string): Shape;

Parameters

key

string

The 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 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

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

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

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 method returns undefined.

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

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.

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)

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 #RRGGBB (e.g., "FFA500"). 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

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