ExcelScript.Workbook interface

Add a new binding to a particular Range.

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

Parameters

Returns

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

Returns

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

Returns

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

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

Parameters

Returns

Adds a new custom XML part to the workbook.

addCustomXmlPart(xml: string): CustomXmlPart;

Parameters

Returns

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

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

Parameters

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

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

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

Parameters

Returns

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

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

Parameters

Returns

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

Creates a blank PivotTableStyle with the specified name.

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

Parameters

Returns

Adds a new style to the collection.

addPredefinedCellStyle(name: string): void;

Parameters

Returns

Adds a new slicer to the workbook.

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

Parameters

Returns

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

Creates a blank slicer style with the specified name.

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

Parameters

Returns

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

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

Creates a blank TableStyle with the specified name.

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

Parameters

Returns

Creates a blank TimelineStyle with the specified name.

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

Parameters

Returns

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

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

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

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

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

getActiveChart(): Chart;

Returns

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

getActiveSlicer(): Slicer;

Returns

Gets the currently active worksheet in the workbook.

getActiveWorksheet(): Worksheet;

Returns

Represents the Excel application instance that contains this workbook.

getApplication(): Application;

Returns

Specifies if the workbook is in AutoSave mode.

getAutoSave(): boolean;

Returns

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

getBinding(id: string): Binding | undefined;

Parameters

Returns

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

getBindings(): Binding[];

Returns

Returns a number about the version of Excel Calculation Engine.

getCalculationEngineVersion(): number;

Returns

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

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

Returns

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

Returns

Gets the comment to which the given reply is connected.

getCommentByReplyId(replyId: string): Comment;

Parameters

Returns

Represents a collection of comments associated with the workbook.

getComments(): Comment[];

Returns

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

Returns

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

getCustomXmlPartByNamespace(namespaceUri: string): CustomXmlPart[];

Parameters

Returns

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

getCustomXmlParts(): CustomXmlPart[];

Returns

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

getCustomXmlPartsByNamespace(namespaceUri: string): CustomXmlPart[];

Parameters

Returns

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

getDefaultPivotTableStyle(): PivotTableStyle;

Returns

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

getDefaultSlicerStyle(): SlicerStyle;

Returns

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

getDefaultTableStyle(): TableStyle;

Returns

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

getDefaultTimelineStyle(): TimelineStyle;

Returns

Gets the first worksheet in the collection.

getFirstWorksheet(visibleOnly?: boolean): Worksheet;

Parameters

Returns

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

Gets the last worksheet in the collection.

getLastWorksheet(visibleOnly?: boolean): Worksheet;

Parameters

Returns

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

Returns

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

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

Gets the workbook name.

getName(): string;

Returns

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

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

getNamedItem(name: string): NamedItem | undefined;

Parameters

Returns

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

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

getPivotTable(name: string): PivotTable | undefined;

Parameters

Returns

Represents a collection of PivotTables associated with the workbook.

getPivotTables(): PivotTable[];

Returns

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

getPivotTableStyle(name: string): PivotTableStyle | undefined;

Parameters

Returns

Represents a collection of PivotTableStyles associated with the workbook.

getPivotTableStyles(): PivotTableStyle[];

Returns

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

getPredefinedCellStyle(name: string): PredefinedCellStyle | undefined;

Parameters

Returns

Represents a collection of styles associated with the workbook.

getPredefinedCellStyles(): PredefinedCellStyle[];

Returns

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

getPreviouslySaved(): boolean;

Returns

Gets the workbook properties.

getProperties(): DocumentProperties;

Returns

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

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

getQueries(): Query[];

Returns

Gets a query from the collection based on its name.

getQuery(key: string): Query;

Parameters

Returns

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

getReadOnly(): boolean;

Returns

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

getSelectedRange(): Range;

Returns

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

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

Returns

Represents a collection of slicers associated with the workbook.

getSlicers(): Slicer[];

Returns

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

getSlicerStyle(name: string): SlicerStyle | undefined;

Parameters

Returns

Represents a collection of SlicerStyles associated with the workbook.

getSlicerStyles(): SlicerStyle[];

Returns

Gets a table by name or ID. If the table doesn't exist, then this method returns undefined.

getTable(key: string): Table | undefined;

Parameters

Returns

Represents a collection of tables associated with the workbook.

getTables(): Table[];

Returns

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

getTableStyle(name: string): TableStyle | undefined;

Parameters

Returns

Represents a collection of TableStyles associated with the workbook.

getTableStyles(): TableStyle[];

Returns

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

getTimelineStyle(name: string): TimelineStyle | undefined;

Parameters

Returns

Represents a collection of TimelineStyles associated with the workbook.

getTimelineStyles(): TimelineStyle[];

Returns

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

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

Returns

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

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

Refreshes all the Data Connections.

refreshAllDataConnections(): void;

Returns

Makes a request to refresh all the workbook links.

refreshAllLinksToLinkedWorkbooks(): void;

Returns

Refreshes all the pivot tables in the collection.

refreshAllPivotTables(): void;

Returns

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

Returns

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

setDefaultPivotTableStyle(
            newDefaultStyle: PivotTableStyle | string
        ): void;

Parameters

Returns

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

setDefaultSlicerStyle(newDefaultStyle: SlicerStyle | string): void;

Parameters

Returns

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

setDefaultTableStyle(newDefaultStyle: TableStyle | string): void;

Parameters

Returns

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

setDefaultTimelineStyle(newDefaultStyle: TimelineStyle | string): void;

Parameters

Returns

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

Returns

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

Returns

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

Returns