ExcelScript.Table interface

Represents an Excel table.

Remarks

Examples

/**
 * This script creates a table from the current sheet's used range.
 * It then adds a total row to the table with the SUM of the last column.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the used range of the current worksheet.
    const sheet = workbook.getActiveWorksheet();
    const range = sheet.getUsedRange();

    // Create a table that has headers from that range.
    const table = sheet.addTable(range, true);

    // Have the table display the SUM for the last column.
    table.setShowTotals(true);
    const lastColumn = table.getColumn(table.getColumns().length);
    lastColumn.getTotalRowRange().setFormula(`=SUBTOTAL(109,[${lastColumn.getName()}])`);
}

Methods

addColumn(index, values, name)

Adds a new column to the table.

addRow(index, values)

Adds one row to the table.

addRows(index, values)

Adds one or more rows to the table.

clearFilters()

Clears all the filters currently applied on the table.

convertToRange()

Converts the table into a normal range of cells. All data is preserved.

delete()

Deletes the table.

deleteRowsAt(index, count)

Delete a specified number of rows at a given index.

getAutoFilter()

Represents the AutoFilter object of the table.

getColumn(key)

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

getColumnById(key)

Gets a column object by ID. If the column does not exist, will return undefined.

getColumnByName(key)

Gets a column object by Name. If the column does not exist, will return undefined.

getColumns()

Represents a collection of all the columns in the table.

getHeaderRowRange()

Gets the range object associated with the header row of the table.

getHighlightFirstColumn()

Specifies if the first column contains special formatting.

getHighlightLastColumn()

Specifies if the last column contains special formatting.

getId()

Returns a value that uniquely identifies the table in a given workbook. The value of the identifier remains the same even when the table is renamed.

getLegacyId()

Returns a numeric ID.

getName()

Name of the table.

getPredefinedTableStyle()

Constant value that represents the table style. Possible values are: "TableStyleLight1" through "TableStyleLight21", "TableStyleMedium1" through "TableStyleMedium28", "TableStyleDark1" through "TableStyleDark11". A custom user-defined style present in the workbook can also be specified.

getRange()

Gets the range object associated with the entire table.

getRangeBetweenHeaderAndTotal()

Gets the range object associated with the data body of the table.

getRowCount()

Gets the number of rows in the table.

getShowBandedColumns()

Specifies if the columns show banded formatting in which odd columns are highlighted differently from even ones, to make reading the table easier.

getShowBandedRows()

Specifies if the rows show banded formatting in which odd rows are highlighted differently from even ones, to make reading the table easier.

getShowFilterButton()

Specifies if the filter buttons are visible at the top of each column header. Setting this is only allowed if the table contains a header row.

getShowHeaders()

Specifies if the header row is visible. This value can be set to show or remove the header row.

getShowTotals()

Specifies if the total row is visible. This value can be set to show or remove the total row.

getSort()

Represents the sorting for the table.

getTotalRowRange()

Gets the range object associated with the totals row of the table.

getWorksheet()

The worksheet containing the current table.

reapplyFilters()

Reapplies all the filters currently on the table.

resize(newRange)

Resize the table to the new range. The new range must overlap with the original table range and the headers (or the top of the table) must be in the same row.

setHighlightFirstColumn(highlightFirstColumn)

Specifies if the first column contains special formatting.

setHighlightLastColumn(highlightLastColumn)

Specifies if the last column contains special formatting.

setName(name)

Name of the table.

setPredefinedTableStyle(predefinedTableStyle)

Constant value that represents the table style. Possible values are: "TableStyleLight1" through "TableStyleLight21", "TableStyleMedium1" through "TableStyleMedium28", "TableStyleDark1" through "TableStyleDark11". A custom user-defined style present in the workbook can also be specified.

setShowBandedColumns(showBandedColumns)

Specifies if the columns show banded formatting in which odd columns are highlighted differently from even ones, to make reading the table easier.

setShowBandedRows(showBandedRows)

Specifies if the rows show banded formatting in which odd rows are highlighted differently from even ones, to make reading the table easier.

setShowFilterButton(showFilterButton)

Specifies if the filter buttons are visible at the top of each column header. Setting this is only allowed if the table contains a header row.

setShowHeaders(showHeaders)

Specifies if the header row is visible. This value can be set to show or remove the header row.

setShowTotals(showTotals)

Specifies if the total row is visible. This value can be set to show or remove the total row.

Method Details

addColumn(index, values, name)

Adds a new column to the table.

addColumn(
            index?: number,
            values?: (boolean | string | number)[],
            name?: string
        ): TableColumn;

Parameters

index

number

Optional. Specifies the relative position of the new column. If null or -1, the addition happens at the end. Columns with a higher index will be shifted to the side. Zero-indexed.

values

(boolean | string | number)[]

Optional. A 1-dimensional array of unformatted values of the table column.

name

string

Optional. Specifies the name of the new column. If null, the default name will be used.

Returns

Examples

/**
 * This script adds a new column to a table.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table in the workbook.
  const table = workbook.getTables()[0];

  // Append an empty column to the table with the header "Total". 
  table.addColumn(-1, null, "Total");
}

addRow(index, values)

Adds one row to the table.

addRow(index?: number, values?: (boolean | string | number)[]): void;

Parameters

index

number

Optional. Specifies the relative position of the new row. If null or -1, the addition happens at the end. Any rows below the inserted row are shifted downwards. Zero-indexed.

values

(boolean | string | number)[]

Optional. A 1-dimensional array of unformatted values of the table row.

Returns

void

Examples

/**
 * This script adds a row to an existing table.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the first table in the current worksheet.
    const selectedSheet = workbook.getActiveWorksheet();
    const table = selectedSheet.getTables()[0];

    // Initialize the data to be added as a table row.
    // Note that length of the array must match the number of columns in the table.
    let rowData = ["Carrots", "Vegetable", 750];

    // Add a row to the end of the table.
    table.addRow(-1, rowData);
}

addRows(index, values)

Adds one or more rows to the table.

addRows(index?: number, values?: (boolean | string | number)[][]): void;

Parameters

index

number

Optional. Specifies the relative position of the new row. If null or -1, the addition happens at the end. Any rows below the inserted row are shifted downwards. Zero-indexed.

values

(boolean | string | number)[][]

Optional. A 2-dimensional array of unformatted values of the table row.

Returns

void

Examples

/**
 * This script adds multiple rows to an existing table.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the first table in the current worksheet.
    const selectedSheet = workbook.getActiveWorksheet();
    const table = selectedSheet.getTables()[0];

    // Initialize the data to be added as table rows.
    // Note that length of the array must match the number of columns in the table.
    let rowData = [["Apples", "Fruit", 5000],
                  ["Celery", "Vegetable", 600],
                  ["Onions", "Vegetable", 1500]];

    // Add the rows to the end of the table.
    table.addRows(-1, rowData);
}

clearFilters()

Clears all the filters currently applied on the table.

clearFilters(): void;

Returns

void

convertToRange()

Converts the table into a normal range of cells. All data is preserved.

convertToRange(): Range;

Returns

Examples

/**
 * This script converts a table to a range and removes the formatting.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the first table in the current worksheet.
    const selectedSheet = workbook.getActiveWorksheet();
    const table = selectedSheet.getTables()[0];

    // Convert the table to a range.
    const formerTable = table.convertToRange();

    // Remove the formatting from the table
    formerTable.clear(ExcelScript.ClearApplyTo.formats);
}

delete()

Deletes the table.

delete(): void;

Returns

void

Examples

/**
 * This script deletes a table. 
 * This removes all associated data and formatting.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the table named "Inventory".
    const table = workbook.getTable("Inventory");

    // Delete the table.
    table.delete();
}

deleteRowsAt(index, count)

Delete a specified number of rows at a given index.

deleteRowsAt(index: number, count?: number): void;

Parameters

index

number

The index value of the row to be deleted. Caution: the index of the row may have moved from the time you determined the value to use for removal.

count

number

Number of rows to delete. By default, a single row will be deleted. Note: Deleting more than 1000 rows at the same time could result in a Power Automate timeout.

Returns

void

getAutoFilter()

Represents the AutoFilter object of the table.

getAutoFilter(): AutoFilter;

Returns

getColumn(key)

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

getColumn(key: number | string): TableColumn | undefined;

Parameters

key

number | string

Column name or ID.

Returns

Examples

/**
 * This script adjusts the indentation of a specific table column.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table in the current worksheet.
  const selectedSheet = workbook.getActiveWorksheet();
  const table = selectedSheet.getTables()[0];

  // Get the data range of the second column.
  const secondColumn = table.getColumn(2);
  const data = secondColumn.getRangeBetweenHeaderAndTotal();

  // Add an indentation of 1 character space to the data range.
  data.getFormat().adjustIndent(1);
}

getColumnById(key)

Gets a column object by ID. If the column does not exist, will return undefined.

getColumnById(key: number): TableColumn | undefined;

Parameters

key

number

Column ID.

Returns

getColumnByName(key)

Gets a column object by Name. If the column does not exist, will return undefined.

getColumnByName(key: string): TableColumn | undefined;

Parameters

key

string

Column Name.

Returns

Examples

/**
 * This script removes a specific column from a table.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the table named "Inventory".
    const table = workbook.getTable("Inventory");

    // If it exists, remove the column named "Category".
    let categoryColumn = table.getColumnByName("Category");
    if (categoryColumn) {
        categoryColumn.delete();
    }
}

getColumns()

Represents a collection of all the columns in the table.

getColumns(): TableColumn[];

Returns

Examples

/**
 * This script adds a new column to a table.
 * It then sets the formulas in the new column to be the product
 * of the values in the two preceding columns.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table in the workbook.
  const table = workbook.getTables()[0];

  // Append an empty column to the table with the header "Total". 
  const totalColumn = table.addColumn(-1, null, "Total");

  // Get the names of the two preceding columns.
  const productColumnName1 = table.getColumns()[totalColumn.getIndex() - 1].getName();
  const productColumnName2 = table.getColumns()[totalColumn.getIndex() - 2].getName();
  
  // Set the formulas in the "Total" column to be the product of the two preceding columns.
  totalColumn.getRangeBetweenHeaderAndTotal().setFormula(
    `=[@[${productColumnName1}]]*[@[${productColumnName2}]]`
  );
}

getHeaderRowRange()

Gets the range object associated with the header row of the table.

getHeaderRowRange(): Range;

Returns

Examples

/**
 * This script centers the text in a table's header row cells.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table on the current worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const table = currentSheet.getTables()[0];

  // Get the header range.
  const headerRange = table.getHeaderRowRange();

  // Set the horizontal text alignment to `center`.
  headerRange.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
}

getHighlightFirstColumn()

Specifies if the first column contains special formatting.

getHighlightFirstColumn(): boolean;

Returns

boolean

getHighlightLastColumn()

Specifies if the last column contains special formatting.

getHighlightLastColumn(): boolean;

Returns

boolean

getId()

Returns a value that uniquely identifies the table in a given workbook. The value of the identifier remains the same even when the table is renamed.

getId(): string;

Returns

string

getLegacyId()

Returns a numeric ID.

getLegacyId(): string;

Returns

string

getName()

Name of the table.

getName(): string;

Returns

string

getPredefinedTableStyle()

Constant value that represents the table style. Possible values are: "TableStyleLight1" through "TableStyleLight21", "TableStyleMedium1" through "TableStyleMedium28", "TableStyleDark1" through "TableStyleDark11". A custom user-defined style present in the workbook can also be specified.

getPredefinedTableStyle(): string;

Returns

string

getRange()

Gets the range object associated with the entire table.

getRange(): Range;

Returns

Examples

/**
 * This script removes any extra formatting that's been applied to a table. 
 * This leaves only the base table style effects.
 * Any formatting outside of the table will be left as is.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table on the current worksheet.
  let worksheet = workbook.getActiveWorksheet();
  let table = worksheet.getTables()[0];

  // Get the range used by the table.
  let range = table.getRange();

  // Clear all the formatting that is not applied by the table and the table style.
  range.clear(ExcelScript.ClearApplyTo.formats);
}

getRangeBetweenHeaderAndTotal()

Gets the range object associated with the data body of the table.

getRangeBetweenHeaderAndTotal(): Range;

Returns

getRowCount()

Gets the number of rows in the table.

getRowCount(): number;

Returns

number

getShowBandedColumns()

Specifies if the columns show banded formatting in which odd columns are highlighted differently from even ones, to make reading the table easier.

getShowBandedColumns(): boolean;

Returns

boolean

getShowBandedRows()

Specifies if the rows show banded formatting in which odd rows are highlighted differently from even ones, to make reading the table easier.

getShowBandedRows(): boolean;

Returns

boolean

getShowFilterButton()

Specifies if the filter buttons are visible at the top of each column header. Setting this is only allowed if the table contains a header row.

getShowFilterButton(): boolean;

Returns

boolean

getShowHeaders()

Specifies if the header row is visible. This value can be set to show or remove the header row.

getShowHeaders(): boolean;

Returns

boolean

getShowTotals()

Specifies if the total row is visible. This value can be set to show or remove the total row.

getShowTotals(): boolean;

Returns

boolean

getSort()

Represents the sorting for the table.

getSort(): TableSort;

Returns

getTotalRowRange()

Gets the range object associated with the totals row of the table.

getTotalRowRange(): Range;

Returns

getWorksheet()

The worksheet containing the current table.

getWorksheet(): Worksheet;

Returns

reapplyFilters()

Reapplies all the filters currently on the table.

reapplyFilters(): void;

Returns

void

Examples

/**
 * This script reapplies the filters on every table in the workbook.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get all the tables.
  const tables = workbook.getTables();

  // Iterate over every table.
  tables.forEach((table) => {
    // Reapply the filters to account for new table entries.
    table.reapplyFilters();
  });
}

resize(newRange)

Resize the table to the new range. The new range must overlap with the original table range and the headers (or the top of the table) must be in the same row.

resize(newRange: Range | string): void;

Parameters

newRange

ExcelScript.Range | string

The range object or range address that will be used to determine the new size of the table.

Returns

void

setHighlightFirstColumn(highlightFirstColumn)

Specifies if the first column contains special formatting.

setHighlightFirstColumn(highlightFirstColumn: boolean): void;

Parameters

highlightFirstColumn

boolean

Returns

void

setHighlightLastColumn(highlightLastColumn)

Specifies if the last column contains special formatting.

setHighlightLastColumn(highlightLastColumn: boolean): void;

Parameters

highlightLastColumn

boolean

Returns

void

setName(name)

Name of the table.

setName(name: string): void;

Parameters

name

string

Returns

void

setPredefinedTableStyle(predefinedTableStyle)

Constant value that represents the table style. Possible values are: "TableStyleLight1" through "TableStyleLight21", "TableStyleMedium1" through "TableStyleMedium28", "TableStyleDark1" through "TableStyleDark11". A custom user-defined style present in the workbook can also be specified.

setPredefinedTableStyle(predefinedTableStyle: string): void;

Parameters

predefinedTableStyle

string

Returns

void

setShowBandedColumns(showBandedColumns)

Specifies if the columns show banded formatting in which odd columns are highlighted differently from even ones, to make reading the table easier.

setShowBandedColumns(showBandedColumns: boolean): void;

Parameters

showBandedColumns

boolean

Returns

void

setShowBandedRows(showBandedRows)

Specifies if the rows show banded formatting in which odd rows are highlighted differently from even ones, to make reading the table easier.

setShowBandedRows(showBandedRows: boolean): void;

Parameters

showBandedRows

boolean

Returns

void

setShowFilterButton(showFilterButton)

Specifies if the filter buttons are visible at the top of each column header. Setting this is only allowed if the table contains a header row.

setShowFilterButton(showFilterButton: boolean): void;

Parameters

showFilterButton

boolean

Returns

void

setShowHeaders(showHeaders)

Specifies if the header row is visible. This value can be set to show or remove the header row.

setShowHeaders(showHeaders: boolean): void;

Parameters

showHeaders

boolean

Returns

void

Examples

/**
 * This script makes a table's headers not visible in the grid.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the table named "CoverageTable".
    const coverageTable = workbook.getTable("CoverageTable");

    // Make the header row not visible.
    coverageTable.setShowHeaders(false);
}

setShowTotals(showTotals)

Specifies if the total row is visible. This value can be set to show or remove the total row.

setShowTotals(showTotals: boolean): void;

Parameters

showTotals

boolean

Returns

void

Examples

/**
 * This script adds the Total Row to an existing table.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the first table in the current worksheet.
    const selectedSheet = workbook.getActiveWorksheet();
    const table = selectedSheet.getTables()[0];

    // Set the Total Row to show.
    table.setShowTotals(true);
}