ExcelScript.Workbook interface

Reference

Package:: ExcelScript

Workbook is the top level object which contains related workbook objects such as worksheets, tables, and ranges.

Remarks

Examples

/**
 * This script adds a new worksheet to the workbook, then switches to it.
 */
function main(workbook: ExcelScript.Workbook) {
  // Add a new worksheet with the default name.
  let worksheet = workbook.addWorksheet();

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

Methods

addBinding(range, bindingType, id)	Add a new binding to a particular Range.
addBindingFromNamedItem(name, bindingType, id)	Add a new binding based on a named item in the workbook. If the named item references to multiple areas, the `InvalidReference` error will be returned.
addBindingFromSelection(bindingType, id)	Add a new binding based on the current selection. If the selection has multiple areas, the `InvalidReference` error will be returned.
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.
addCustomXmlPart(xml)	Adds a new custom XML part to the workbook.
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.
addPivotTableStyle(name, makeUniqueName)	Creates a blank `PivotTableStyle` with the specified name.
addPredefinedCellStyle(name)	Adds a new style to the collection.
addSlicer(slicerSource, sourceField, slicerDestination)	Adds a new slicer to the workbook.
addSlicerStyle(name, makeUniqueName)	Creates a blank slicer style with the specified name.
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.
addTableStyle(name, makeUniqueName)	Creates a blank `TableStyle` with the specified name.
addTimelineStyle(name, makeUniqueName)	Creates a blank `TimelineStyle` with the specified name.
addWorksheet(name)	Adds a new worksheet to the workbook. The worksheet will be added at the end of existing worksheets. If you wish to activate the newly added worksheet, call `.activate()` on it.
breakAllLinksToLinkedWorkbooks()	Breaks all the links to the linked workbooks. Once the links are broken, any formulas referencing workbook links are removed entirely and replaced with the most recently retrieved values.
getActiveCell()	Gets the currently active cell from the workbook.
getActiveChart()	Gets the currently active chart in the workbook. If there is no active chart, then this method returns `undefined`.
getActiveSlicer()	Gets the currently active slicer in the workbook. If there is no active slicer, then this method returns `undefined`.
getActiveWorksheet()	Gets the currently active worksheet in the workbook.
getApplication()	Represents the Excel application instance that contains this workbook.
getAutoSave()	Specifies if the workbook is in AutoSave mode.
getBinding(id)	Gets a binding object by ID. If the binding object does not exist, then this method returns `undefined`.
getBindings()	Represents a collection of bindings that are part of the workbook.
getCalculationEngineVersion()	Returns a number about the version of Excel Calculation Engine.
getChartDataPointTrack()	True if all charts in the workbook are tracking the actual data points to which they are attached. False if the charts track the index of the data points.
getComment(commentId)	Gets a comment from the collection based on its ID. If the comment object does not exist, then this method returns `undefined`.
getCommentByCell(cellAddress)	Gets the comment from the specified cell. If there is no comment in the cell, an error is thrown.
getCommentByReplyId(replyId)	Gets the comment to which the given reply is connected.
getComments()	Represents a collection of comments associated with the workbook.
getCustomXmlPart(id)	Gets a custom XML part based on its ID. If the `CustomXmlPart` does not exist, then this method returns `undefined`.
getCustomXmlPartByNamespace(namespaceUri)	Gets a new collection of custom XML parts whose namespaces match the given namespace.
getCustomXmlParts()	Represents the collection of custom XML parts contained by this workbook.
getCustomXmlPartsByNamespace(namespaceUri)	Gets a new collection of custom XML parts whose namespaces match the given namespace.
getDefaultPivotTableStyle()	Gets the default PivotTable style for the parent object's scope.
getDefaultSlicerStyle()	Gets the default `SlicerStyle` for the parent object's scope.
getDefaultTableStyle()	Gets the default table style for the parent object's scope.
getDefaultTimelineStyle()	Gets the default timeline style for the parent object's scope.
getFirstWorksheet(visibleOnly)	Gets the first worksheet in the collection.
getIsDirty()	Specifies if changes have been made since the workbook was last saved. You can set this property to `true` if you want to close a modified workbook without either saving it or being prompted to save it.
getLastWorksheet(visibleOnly)	Gets the last worksheet in the collection.
getLinkedWorkbookByUrl(key)	Gets information about a linked workbook by its URL. If the workbook does not exist, then this method returns `undefined`.
getLinkedWorkbookRefreshMode()	Represents the update mode of the workbook links. The mode is same for all of the workbook links present in the workbook.
getLinkedWorkbooks()	Returns a collection of linked workbooks. In formulas, the workbook links can be used to reference data (cell values and names) outside of the current workbook.
getName()	Gets the workbook name.
getNamedItem(name)	Gets a `NamedItem` object using its name. If the object does not exist, then this method returns `undefined`.
getNames()	Represents a collection of workbook-scoped named items (named ranges and constants).
getPivotTable(name)	Gets a PivotTable by name. If the PivotTable does not exist, then this method returns `undefined`.
getPivotTables()	Represents a collection of PivotTables associated with the workbook.
getPivotTableStyle(name)	Gets a `PivotTableStyle` by name. If the `PivotTableStyle` does not exist, then this method returns `undefined`.
getPivotTableStyles()	Represents a collection of PivotTableStyles associated with the workbook.
getPredefinedCellStyle(name)	Gets a style by name. If the style object does not exist, then this method returns `undefined`.
getPredefinedCellStyles()	Represents a collection of styles associated with the workbook.
getPreviouslySaved()	Specifies if the workbook has ever been saved locally or online.
getProperties()	Gets the workbook properties.
getProtection()	Returns the protection object for a workbook.
getQueries()	Returns a collection of Power Query queries that are part of the workbook.
getQuery(key)	Gets a query from the collection based on its name.
getReadOnly()	Returns `true` if the workbook is open in read-only mode.
getSelectedRange()	Gets the currently selected single range from the workbook. If there are multiple ranges selected, this method will throw an error.
getSelectedRanges()	Gets the currently selected one or more ranges from the workbook. Unlike `getSelectedRange()`, this method returns a `RangeAreas` object that represents all the selected ranges.
getSlicer(key)	Gets a slicer using its name or ID. If the slicer doesn't exist, then this method returns `undefined`.
getSlicers()	Represents a collection of slicers associated with the workbook.
getSlicerStyle(name)	Gets a `SlicerStyle` by name. If the slicer style doesn't exist, then this method returns `undefined`.
getSlicerStyles()	Represents a collection of SlicerStyles associated with the workbook.
getTable(key)	Gets a table by name or ID. If the table doesn't exist, then this method returns `undefined`.
getTables()	Represents a collection of tables associated with the workbook.
getTableStyle(name)	Gets a `TableStyle` by name. If the table style does not exist, then this method returns `undefined`.
getTableStyles()	Represents a collection of TableStyles associated with the workbook.
getTimelineStyle(name)	Gets a `TimelineStyle` by name. If the timeline style doesn't exist, then this method returns `undefined`.
getTimelineStyles()	Represents a collection of TimelineStyles associated with the workbook.
getUsePrecisionAsDisplayed()	True if calculations in this workbook will be done using only the precision of the numbers as they're displayed. Data will permanently lose accuracy when switching this property from `false` to `true`.
getWorksheet(key)	Gets a worksheet object using its name or ID. If the worksheet does not exist, then this method returns `undefined`.
getWorksheets()	Represents a collection of worksheets associated with the workbook.
refreshAllDataConnections()	Makes a request to refresh all the data connections.
refreshAllLinksToLinkedWorkbooks()	Makes a request to refresh all the workbook links.
refreshAllPivotTables()	Refreshes all the pivot tables in the collection.
setChartDataPointTrack(chartDataPointTrack)	True if all charts in the workbook are tracking the actual data points to which they are attached. False if the charts track the index of the data points.
setDefaultPivotTableStyle(newDefaultStyle)	Sets the default PivotTable style for use in the parent object's scope.
setDefaultSlicerStyle(newDefaultStyle)	Sets the default slicer style for use in the parent object's scope.
setDefaultTableStyle(newDefaultStyle)	Sets the default table style for use in the parent object's scope.
setDefaultTimelineStyle(newDefaultStyle)	Sets the default timeline style for use in the parent object's scope.
setIsDirty(isDirty)	Specifies if changes have been made since the workbook was last saved. You can set this property to `true` if you want to close a modified workbook without either saving it or being prompted to save it.
setLinkedWorkbookRefreshMode(linkedWorkbookRefreshMode)	Represents the update mode of the workbook links. The mode is same for all of the workbook links present in the workbook.
setUsePrecisionAsDisplayed(usePrecisionAsDisplayed)	True if calculations in this workbook will be done using only the precision of the numbers as they're displayed. Data will permanently lose accuracy when switching this property from `false` to `true`.

Method Details

addBinding(range, bindingType, id)

Add a new binding to a particular Range.

addBinding(
            range: Range | string,
            bindingType: BindingType,
            id: string
        ): Binding;

Parameters

range: ExcelScript.Range | string

Range to bind the binding to. May be a Range object or a string. If string, must contain the full address, including the sheet name

bindingType: ExcelScript.BindingType

Type of binding. See ExcelScript.BindingType.

id: string

Name of the binding.

Returns

ExcelScript.Binding

addBindingFromNamedItem(name, bindingType, id)

Add a new binding based on a named item in the workbook. If the named item references to multiple areas, the InvalidReference error will be returned.

addBindingFromNamedItem(
            name: string,
            bindingType: BindingType,
            id: string
        ): Binding;

Parameters

name: string

Name from which to create binding.

bindingType: ExcelScript.BindingType

Type of binding. See ExcelScript.BindingType.

id: string

Name of the binding.

Returns

ExcelScript.Binding

addBindingFromSelection(bindingType, id)

Add a new binding based on the current selection. If the selection has multiple areas, the InvalidReference error will be returned.

addBindingFromSelection(bindingType: BindingType, id: string): Binding;

Parameters

bindingType: ExcelScript.BindingType

Type of binding. See ExcelScript.BindingType.

id: string

Name of the binding.

Returns

ExcelScript.Binding

addComment(cellAddress, content, contentType)

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

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

Parameters

cellAddress: ExcelScript.Range | string

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

content: ExcelScript.CommentRichContent | string

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

contentType: ExcelScript.ContentType

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

Returns

ExcelScript.Comment

addCustomXmlPart(xml)

Adds a new custom XML part to the workbook.

addCustomXmlPart(xml: string): CustomXmlPart;

Parameters

xml: string

XML content. Must be a valid XML fragment.

Returns

ExcelScript.CustomXmlPart

addNamedItem(name, reference, comment)

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

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

Parameters

name: string

The name of the named item.

reference: ExcelScript.Range | string

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

comment: string

Optional. The comment associated with the named item.

Returns

ExcelScript.NamedItem

Examples

/**
 * This script creates a named formula and uses it in another part of the workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Create a named item for a formula.
  // This formula is the sum of the cells F2:F21 on Sheet1.
  const namedItem: ExcelScript.NamedItem = workbook.addNamedItem(
    "GrandTotal", 
    "=SUM(Sheet1!$F$2:$F$21)", 
    "The sum of table sums."
  );

  // Add this named formula to a new sheet in the workbook.
  const otherSheet = workbook.addWorksheet();
  otherSheet.getRange("A1").setFormula(namedItem.getFormula());

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

addNamedItemFormulaLocal(name, formula, comment)

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

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

Parameters

name: string

The name of the named item.

formula: string

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

comment: string

Optional. The comment associated with the named item.

Returns

ExcelScript.NamedItem

addPivotTable(name, source, destination)

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

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

Parameters

name: string

The name of the new PivotTable.

source: ExcelScript.Range | string | ExcelScript.Table

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

destination: ExcelScript.Range | string

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

Returns

ExcelScript.PivotTable

Examples

/**
 * This script creates a PivotTable from an existing table and adds it to an existing worksheet.
 * This script assumes there is a table in the current worksheet with columns named "Type" and "Sales".
 * It also assumes there is a worksheet named "PivotSheet".
 */
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];
  let pivotTable = workbook.addPivotTable("My Pivot", table, "PivotSheet!A1");

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

addPivotTableStyle(name, makeUniqueName)

Creates a blank PivotTableStyle with the specified name.

addPivotTableStyle(
            name: string,
            makeUniqueName?: boolean
        ): PivotTableStyle;

Parameters

name: string

The unique name for the new PivotTable style. Will throw an InvalidArgument error if the name is already in use.

makeUniqueName: boolean

Optional. Defaults to false. If true, will append numbers to the name in order to make it unique, if needed.

Returns

ExcelScript.PivotTableStyle

addPredefinedCellStyle(name)

Adds a new style to the collection.

addPredefinedCellStyle(name: string): void;

Parameters

name: string

Name of the style to be added.

Returns

void

addSlicer(slicerSource, sourceField, slicerDestination)

Adds a new slicer to the workbook.

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

Parameters

slicerSource: string | ExcelScript.PivotTable | ExcelScript.Table

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

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

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

slicerDestination: string | ExcelScript.Worksheet

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

Returns

ExcelScript.Slicer

Examples

/**
 * This script adds a slicer for an existing PivotTable.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable named "Farm Pivot".
  const farmPivot = workbook.getPivotTable("Farm Pivot");

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

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

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

addSlicerStyle(name, makeUniqueName)

Creates a blank slicer style with the specified name.

addSlicerStyle(name: string, makeUniqueName?: boolean): SlicerStyle;

Parameters

name: string

The unique name for the new slicer style. Will throw an InvalidArgument exception if the name is already in use.

makeUniqueName: boolean

Optional. Defaults to false. If true, will append numbers to the name in order to make it unique, if needed.

Returns

ExcelScript.SlicerStyle

addTable(address, hasHeaders)

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

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

Parameters

address: ExcelScript.Range | string

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

hasHeaders: boolean

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

Returns

ExcelScript.Table

Examples

/**
 * This sample converts the information in the first worksheet
 * into a table with headers.
*/
function main(workbook: ExcelScript.Workbook) {
  // This assumes there is one contiguous range in the first worksheet.
  const dataRange = workbook.getFirstWorksheet().getUsedRange();
  
  // Add a table at the workbook level.
  workbook.addTable(dataRange.getAddress(), true);
}

addTableStyle(name, makeUniqueName)

Creates a blank TableStyle with the specified name.

addTableStyle(name: string, makeUniqueName?: boolean): TableStyle;

Parameters

name: string

The unique name for the new table style. Will throw an InvalidArgument error if the name is already in use.

makeUniqueName: boolean

Optional. Defaults to false. If true, will append numbers to the name in order to make it unique, if needed.

Returns

ExcelScript.TableStyle

addTimelineStyle(name, makeUniqueName)

Creates a blank TimelineStyle with the specified name.

addTimelineStyle(name: string, makeUniqueName?: boolean): TimelineStyle;

Parameters

name: string

The unique name for the new timeline style. Will throw an InvalidArgument error if the name is already in use.

makeUniqueName: boolean

Optional. Defaults to false. If true, will append numbers to the name in order to make it unique, if needed.

Returns

ExcelScript.TimelineStyle

addWorksheet(name)

Adds a new worksheet to the workbook. The worksheet will be added at the end of existing worksheets. If you wish to activate the newly added worksheet, call .activate() on it.

addWorksheet(name?: string): Worksheet;

Parameters

name: string

Optional. The name of the worksheet to be added. If specified, the name should be unique. If not specified, Excel determines the name of the new worksheet.

Returns

ExcelScript.Worksheet

Examples

/**
 * This script adds a new worksheet named "Data" to the workbook.
 * If a worksheet with that name already exists, the script logs a note.
 */
function main(workbook: ExcelScript.Workbook) {
  // Check if the "Data" worksheet already exists.
  if (workbook.getWorksheet("Data")) {
    console.log("The Data worksheet is already in the workbook.");
  } else {
    // Add a new worksheet.
    let worksheet = workbook.addWorksheet("Data");
  }
}

breakAllLinksToLinkedWorkbooks()

Breaks all the links to the linked workbooks. Once the links are broken, any formulas referencing workbook links are removed entirely and replaced with the most recently retrieved values.

breakAllLinksToLinkedWorkbooks(): void;

Returns

void

getActiveCell()

Gets the currently active cell from the workbook.

getActiveCell(): Range;

Returns

ExcelScript.Range

Examples

/**
 * This script logs the value of the current active cell. 
 * If multiple cells are selected, the top-leftmost cell will be logged.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the current active cell in the workbook.
  let cell = workbook.getActiveCell();
  console.log(`The current cell's value is ${cell.getValue()}`);
}

getActiveChart()

Gets the currently active chart in the workbook. If there is no active chart, then this method returns undefined.

getActiveChart(): Chart;

Returns

ExcelScript.Chart

getActiveSlicer()

Gets the currently active slicer in the workbook. If there is no active slicer, then this method returns undefined.

getActiveSlicer(): Slicer;

Returns

ExcelScript.Slicer

getActiveWorksheet()

Gets the currently active worksheet in the workbook.

getActiveWorksheet(): Worksheet;

Returns

ExcelScript.Worksheet

getApplication()

Represents the Excel application instance that contains this workbook.

getApplication(): Application;

Returns

ExcelScript.Application

getAutoSave()

Specifies if the workbook is in AutoSave mode.

getAutoSave(): boolean;

Returns

boolean

getBinding(id)

Gets a binding object by ID. If the binding object does not exist, then this method returns undefined.

getBinding(id: string): Binding | undefined;

Parameters

id: string

ID of the binding object to be retrieved.

Returns

ExcelScript.Binding | undefined

getBindings()

Represents a collection of bindings that are part of the workbook.

getBindings(): Binding[];

Returns

ExcelScript.Binding[]

getCalculationEngineVersion()

Returns a number about the version of Excel Calculation Engine.

getCalculationEngineVersion(): number;

Returns

number

getChartDataPointTrack()

True if all charts in the workbook are tracking the actual data points to which they are attached. False if the charts track the index of the data points.

getChartDataPointTrack(): boolean;

Returns

boolean

getComment(commentId)

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

getComment(commentId: string): Comment | undefined;

Parameters

commentId: string

The identifier for the comment.

Returns

ExcelScript.Comment | undefined

getCommentByCell(cellAddress)

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

getCommentByCell(cellAddress: Range | string): Comment;

Parameters

cellAddress: ExcelScript.Range | string

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

Returns

ExcelScript.Comment

getCommentByReplyId(replyId)

Gets the comment to which the given reply is connected.

getCommentByReplyId(replyId: string): Comment;

Parameters

replyId: string

The identifier of comment reply.

Returns

ExcelScript.Comment

getComments()

Represents a collection of comments associated with the workbook.

getComments(): Comment[];

Returns

ExcelScript.Comment[]

getCustomXmlPart(id)

Gets a custom XML part based on its ID. If the CustomXmlPart does not exist, then this method returns undefined.

getCustomXmlPart(id: string): CustomXmlPart | undefined;

Parameters

id: string

ID of the object to be retrieved.

Returns

ExcelScript.CustomXmlPart | undefined

getCustomXmlPartByNamespace(namespaceUri)

Warning

This API is now deprecated.

Use getCustomXmlPartsByNamespace instead.

Gets a new collection of custom XML parts whose namespaces match the given namespace.

getCustomXmlPartByNamespace(namespaceUri: string): CustomXmlPart[];

Parameters

namespaceUri: string

This must be a fully qualified schema URI; for example, "http://schemas.contoso.com/review/1.0".

Returns

ExcelScript.CustomXmlPart[]

getCustomXmlParts()

Represents the collection of custom XML parts contained by this workbook.

getCustomXmlParts(): CustomXmlPart[];

Returns

ExcelScript.CustomXmlPart[]

getCustomXmlPartsByNamespace(namespaceUri)

Gets a new collection of custom XML parts whose namespaces match the given namespace.

getCustomXmlPartsByNamespace(namespaceUri: string): CustomXmlPart[];

Parameters

namespaceUri: string

This must be a fully qualified schema URI; for example, "http://schemas.contoso.com/review/1.0".

Returns

ExcelScript.CustomXmlPart[]

getDefaultPivotTableStyle()

Gets the default PivotTable style for the parent object's scope.

getDefaultPivotTableStyle(): PivotTableStyle;

Returns

ExcelScript.PivotTableStyle

getDefaultSlicerStyle()

Gets the default SlicerStyle for the parent object's scope.

getDefaultSlicerStyle(): SlicerStyle;

Returns

ExcelScript.SlicerStyle

getDefaultTableStyle()

Gets the default table style for the parent object's scope.

getDefaultTableStyle(): TableStyle;

Returns

ExcelScript.TableStyle

getDefaultTimelineStyle()

Gets the default timeline style for the parent object's scope.

getDefaultTimelineStyle(): TimelineStyle;

Returns

ExcelScript.TimelineStyle

getFirstWorksheet(visibleOnly)

Gets the first worksheet in the collection.

getFirstWorksheet(visibleOnly?: boolean): Worksheet;

Parameters

visibleOnly: boolean

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

Returns

ExcelScript.Worksheet

getIsDirty()

Specifies if changes have been made since the workbook was last saved. You can set this property to true if you want to close a modified workbook without either saving it or being prompted to save it.

getIsDirty(): boolean;

Returns

boolean

getLastWorksheet(visibleOnly)

Gets the last worksheet in the collection.

getLastWorksheet(visibleOnly?: boolean): Worksheet;

Parameters

visibleOnly: boolean

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

Returns

ExcelScript.Worksheet

getLinkedWorkbookByUrl(key)

Gets information about a linked workbook by its URL. If the workbook does not exist, then this method returns undefined.

getLinkedWorkbookByUrl(key: string): LinkedWorkbook | undefined;

Parameters

key: string

The URL of the linked workbook.

Returns

ExcelScript.LinkedWorkbook | undefined

getLinkedWorkbookRefreshMode()

Represents the update mode of the workbook links. The mode is same for all of the workbook links present in the workbook.

getLinkedWorkbookRefreshMode(): WorkbookLinksRefreshMode;

Returns

ExcelScript.WorkbookLinksRefreshMode

Examples

/**
 * This script refreshes all the links to external workbooks, 
 * if the linked workbook refresh mode is set to manual.
 * To learn about linked workbooks, see https://support.microsoft.com/office/create-an-external-reference-link-to-a-cell-range-in-another-workbook-c98d1803-dd75-4668-ac6a-d7cca2a9b95f.
 */
function main(workbook: ExcelScript.Workbook) {
  // Check the refresh mode.
  if (workbook.getLinkedWorkbookRefreshMode() === ExcelScript.WorkbookLinksRefreshMode.manual) {
    console.log("Refreshing workbook links");

    // Trigger a refresh of linked workbook content.
    workbook.refreshAllLinksToLinkedWorkbooks();
  }
}

getLinkedWorkbooks()

Returns a collection of linked workbooks. In formulas, the workbook links can be used to reference data (cell values and names) outside of the current workbook.

getLinkedWorkbooks(): LinkedWorkbook[];

Returns

ExcelScript.LinkedWorkbook[]

getName()

Gets the workbook name.

getName(): string;

Returns

string

Examples

/**
 * This script logs the name of the workbook without the ".xlsx" extension.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the workbook's name.
  let name = workbook.getName();

  // Remove the file extension.
  name = name.substring(0, name.lastIndexOf(".xlsx"));

  // Display the name in the console.
  console.log(name);
}

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

getNames()

Represents a collection of workbook-scoped named items (named ranges and constants).

getNames(): NamedItem[];

Returns

ExcelScript.NamedItem[]

Examples

/**
 * This script looks for every named range with "Review" in the name 
 * and marks the range with a yellow fill.
 */
function main(workbook: ExcelScript.Workbook) {
  // Look at every named item in the workbook.
  workbook.getNames().forEach((namedItem) => {
    // Find names containing "Review".
    if (namedItem.getName().includes("Review")) {
      // Only change the fill color if the named item is a range (not a formula).
      let itemType: ExcelScript.NamedItemType = namedItem.getType();
      if (itemType === ExcelScript.NamedItemType.range) {
        // Set the range's fill color to yellow.
        namedItem.getRange().getFormat().getFill().setColor("yellow");
      }
    }
  });
}

getPivotTable(name)

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

getPivotTable(name: string): PivotTable | undefined;

Parameters

name: string

Name of the PivotTable to be retrieved.

Returns

ExcelScript.PivotTable | undefined

getPivotTables()

Represents a collection of PivotTables associated with the workbook.

getPivotTables(): PivotTable[];

Returns

ExcelScript.PivotTable[]

getPivotTableStyle(name)

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

getPivotTableStyle(name: string): PivotTableStyle | undefined;

Parameters

name: string

Name of the PivotTable style to be retrieved.

Returns

ExcelScript.PivotTableStyle | undefined

getPivotTableStyles()

Represents a collection of PivotTableStyles associated with the workbook.

getPivotTableStyles(): PivotTableStyle[];

Returns

ExcelScript.PivotTableStyle[]

getPredefinedCellStyle(name)

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

getPredefinedCellStyle(name: string): PredefinedCellStyle | undefined;

Parameters

name: string

Name of the style to be retrieved.

Returns

ExcelScript.PredefinedCellStyle | undefined

getPredefinedCellStyles()

Represents a collection of styles associated with the workbook.

getPredefinedCellStyles(): PredefinedCellStyle[];

Returns

ExcelScript.PredefinedCellStyle[]

getPreviouslySaved()

Specifies if the workbook has ever been saved locally or online.

getPreviouslySaved(): boolean;

Returns

boolean

getProperties()

Gets the workbook properties.

getProperties(): DocumentProperties;

Returns

ExcelScript.DocumentProperties

getProtection()

Returns the protection object for a workbook.

getProtection(): WorkbookProtection;

Returns

ExcelScript.WorkbookProtection

Examples

/**
 * This script protects the workbook with a password, if it isn't already protected.
 * The password is provided by the user through a prompt.
 */
function main(workbook: ExcelScript.Workbook, password?: string) {
  // Get the workbook-level protection object.
  const protection = workbook.getProtection();

  // Check if the workbook is already protected.
  if (!protection.getProtected()) {
      // Protect the workbook with the given password.
      // If the optional password was omitted, 
      // no password will be needed to unprotect the workbook.
    protection.protect(password);
  }
}

getQueries()

Returns a collection of Power Query queries that are part of the workbook.

getQueries(): Query[];

Returns

ExcelScript.Query[]

getQuery(key)

Gets a query from the collection based on its name.

getQuery(key: string): Query;

Parameters

key: string

The name of the query case-insensitive.

Returns

ExcelScript.Query

getReadOnly()

Returns true if the workbook is open in read-only mode.

getReadOnly(): boolean;

Returns

boolean

getSelectedRange()

Gets the currently selected single range from the workbook. If there are multiple ranges selected, this method will throw an error.

getSelectedRange(): Range;

Returns

ExcelScript.Range

getSelectedRanges()

Gets the currently selected one or more ranges from the workbook. Unlike getSelectedRange(), this method returns a RangeAreas object that represents all the selected ranges.

getSelectedRanges(): RangeAreas;

Returns

ExcelScript.RangeAreas

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

Represents a collection of slicers associated with the workbook.

getSlicers(): Slicer[];

Returns

ExcelScript.Slicer[]

getSlicerStyle(name)

Gets a SlicerStyle by name. If the slicer style doesn't exist, then this method returns undefined.

getSlicerStyle(name: string): SlicerStyle | undefined;

Parameters

name: string

Name of the slicer style to be retrieved.

Returns

ExcelScript.SlicerStyle | undefined

getSlicerStyles()

Represents a collection of SlicerStyles associated with the workbook.

getSlicerStyles(): SlicerStyle[];

Returns

ExcelScript.SlicerStyle[]

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

Represents a collection of tables associated with the workbook.

getTables(): Table[];

Returns

ExcelScript.Table[]

getTableStyle(name)

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

getTableStyle(name: string): TableStyle | undefined;

Parameters

name: string

Name of the table style to be retrieved.

Returns

ExcelScript.TableStyle | undefined

getTableStyles()

Represents a collection of TableStyles associated with the workbook.

getTableStyles(): TableStyle[];

Returns

ExcelScript.TableStyle[]

getTimelineStyle(name)

Gets a TimelineStyle by name. If the timeline style doesn't exist, then this method returns undefined.

getTimelineStyle(name: string): TimelineStyle | undefined;

Parameters

name: string

Name of the timeline style to be retrieved.

Returns

ExcelScript.TimelineStyle | undefined

getTimelineStyles()

Represents a collection of TimelineStyles associated with the workbook.

getTimelineStyles(): TimelineStyle[];

Returns

ExcelScript.TimelineStyle[]

getUsePrecisionAsDisplayed()

True if calculations in this workbook will be done using only the precision of the numbers as they're displayed. Data will permanently lose accuracy when switching this property from false to true.

getUsePrecisionAsDisplayed(): boolean;

Returns

boolean

getWorksheet(key)

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

getWorksheet(key: string): Worksheet | undefined;

Parameters

key: string

The name or ID of the worksheet.

Returns

ExcelScript.Worksheet | undefined

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

getWorksheets()

Represents a collection of worksheets associated with the workbook.

getWorksheets(): Worksheet[];

Returns

ExcelScript.Worksheet[]

Examples

/**
 * This script logs the names of all the worksheets in the workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get all the worksheets in the workbook. 
  let sheets = workbook.getWorksheets();

  // Get a list of all the worksheet names.
  let names = sheets.map ((sheet) => sheet.getName());

  // Write in the console all the worksheet names and the total count.
  console.log(names);
  console.log(`Total worksheets inside of this workbook: ${sheets.length}`);
}

refreshAllDataConnections()

Makes a request to refresh all the data connections.

refreshAllDataConnections(): void;

Returns

void

refreshAllLinksToLinkedWorkbooks()

Makes a request to refresh all the workbook links.

refreshAllLinksToLinkedWorkbooks(): void;

Returns

void

refreshAllPivotTables()

Refreshes all the pivot tables in the collection.

refreshAllPivotTables(): void;

Returns

void

setChartDataPointTrack(chartDataPointTrack)

True if all charts in the workbook are tracking the actual data points to which they are attached. False if the charts track the index of the data points.

setChartDataPointTrack(chartDataPointTrack: boolean): void;

Parameters

chartDataPointTrack: boolean

Returns

void

setDefaultPivotTableStyle(newDefaultStyle)

Sets the default PivotTable style for use in the parent object's scope.

setDefaultPivotTableStyle(
            newDefaultStyle: PivotTableStyle | string
        ): void;

Parameters

newDefaultStyle: ExcelScript.PivotTableStyle | string

The PivotTableStyle object, or name of the PivotTableStyle object, that should be the new default.

Returns

void

setDefaultSlicerStyle(newDefaultStyle)

Sets the default slicer style for use in the parent object's scope.

setDefaultSlicerStyle(newDefaultStyle: SlicerStyle | string): void;

Parameters

newDefaultStyle: ExcelScript.SlicerStyle | string

The SlicerStyle object, or name of the SlicerStyle object, that should be the new default.

Returns

void

setDefaultTableStyle(newDefaultStyle)

Sets the default table style for use in the parent object's scope.

setDefaultTableStyle(newDefaultStyle: TableStyle | string): void;

Parameters

newDefaultStyle: ExcelScript.TableStyle | string

The TableStyle object, or name of the TableStyle object, that should be the new default.

Returns

void

setDefaultTimelineStyle(newDefaultStyle)

Sets the default timeline style for use in the parent object's scope.

setDefaultTimelineStyle(newDefaultStyle: TimelineStyle | string): void;

Parameters

newDefaultStyle: ExcelScript.TimelineStyle | string

The TimelineStyle object, or name of the TimelineStyle object, that should be the new default.

Returns

void

setIsDirty(isDirty)

setIsDirty(isDirty: boolean): void;

Parameters

isDirty: boolean

Returns

void

setLinkedWorkbookRefreshMode(linkedWorkbookRefreshMode)

Represents the update mode of the workbook links. The mode is same for all of the workbook links present in the workbook.

setLinkedWorkbookRefreshMode(
            linkedWorkbookRefreshMode: WorkbookLinksRefreshMode
        ): void;

Parameters

linkedWorkbookRefreshMode: ExcelScript.WorkbookLinksRefreshMode

Returns

void

setUsePrecisionAsDisplayed(usePrecisionAsDisplayed)

setUsePrecisionAsDisplayed(usePrecisionAsDisplayed: boolean): void;

Parameters

usePrecisionAsDisplayed: boolean

Returns

void