ExcelScript.Workbook interface

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

Refreshes 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

getActiveSlicer()

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

getActiveSlicer(): Slicer;

Returns

getActiveWorksheet()

Gets the currently active worksheet in the workbook.

getActiveWorksheet(): Worksheet;

Returns

getApplication()

Represents the Excel application instance that contains this workbook.

getApplication(): Application;

Returns

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

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

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

Represents a collection of comments associated with the workbook.

getComments(): Comment[];

Returns

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

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

getCustomXmlParts()

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

getCustomXmlParts(): CustomXmlPart[];

Returns

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

getDefaultPivotTableStyle()

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

getDefaultPivotTableStyle(): PivotTableStyle;

Returns

getDefaultSlicerStyle()

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

getDefaultSlicerStyle(): SlicerStyle;

Returns

getDefaultTableStyle()

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

getDefaultTableStyle(): TableStyle;

Returns

getDefaultTimelineStyle()

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

getDefaultTimelineStyle(): TimelineStyle;

Returns

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

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

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

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

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

Examples

/**
 * This script removes all links to other workbooks.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get all the linked workbook references.
    const externalWorkbooks: ExcelScript.LinkedWorkbook[] = workbook.getLinkedWorkbooks();
    console.log(`There are ${externalWorkbooks.length} other workbooks linked to from this workbook.`);

    // Remove all the links to those workbooks.
    // This changes the value of cells with workbook links to "#CONNECT!".
    externalWorkbooks.forEach((workbookLink) => {
        workbookLink.breakLinks();
    });
}

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

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

getPivotTables()

Represents a collection of PivotTables associated with the workbook.

getPivotTables(): PivotTable[];

Returns

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

getPivotTableStyles()

Represents a collection of PivotTableStyles associated with the workbook.

getPivotTableStyles(): PivotTableStyle[];

Returns

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

getPredefinedCellStyles()

Represents a collection of styles associated with the workbook.

getPredefinedCellStyles(): PredefinedCellStyle[];

Returns

getPreviouslySaved()

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

getPreviouslySaved(): boolean;

Returns

boolean

getProperties()

Gets the workbook properties.

getProperties(): DocumentProperties;

Returns

getProtection()

Returns the protection object for a workbook.

getProtection(): WorkbookProtection;

Returns

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

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

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

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

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

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

getSlicerStyles()

Represents a collection of SlicerStyles associated with the workbook.

getSlicerStyles(): SlicerStyle[];

Returns

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

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

getTableStyles()

Represents a collection of TableStyles associated with the workbook.

getTableStyles(): TableStyle[];

Returns

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

getTimelineStyles()

Represents a collection of TimelineStyles associated with the workbook.

getTimelineStyles(): TimelineStyle[];

Returns

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

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

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

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.

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)

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.

setUsePrecisionAsDisplayed(usePrecisionAsDisplayed: boolean): void;

Parameters

usePrecisionAsDisplayed

boolean

Returns

void