ExcelScript.Table interface

Adds a new column to the table.

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

Parameters

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

Adds one row to the table.

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

Parameters

Returns

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

Adds one or more rows to the table.

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

Parameters

Returns

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

Clears all the filters currently applied on the table.

clearFilters(): void;

Returns

Examples

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

    // Remove any active filters from each table.
    tables.forEach((table) => {
      table.clearFilters();
    });
}

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

Deletes the table.

delete(): void;

Returns

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

Delete a specified number of rows at a given index.

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

Parameters

Returns

Represents the AutoFilter object of the table.

getAutoFilter(): AutoFilter;

Returns

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

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

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

getColumnById(key: number): TableColumn | undefined;

Parameters

Returns

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

getColumnByName(key: string): TableColumn | undefined;

Parameters

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

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

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

Specifies if the first column contains special formatting.

getHighlightFirstColumn(): boolean;

Returns

Specifies if the last column contains special formatting.

getHighlightLastColumn(): boolean;

Returns

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

Returns a numeric ID.

getLegacyId(): string;

Returns

Name of the table.

getName(): string;

Returns

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

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

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

getRangeBetweenHeaderAndTotal(): Range;

Returns

Gets the number of rows in the table.

getRowCount(): number;

Returns

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

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

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

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

getShowHeaders(): boolean;

Returns

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

getShowTotals(): boolean;

Returns

Represents the sorting for the table.

getSort(): TableSort;

Returns

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

getTotalRowRange(): Range;

Returns

The worksheet containing the current table.

getWorksheet(): Worksheet;

Returns

Reapplies all the filters currently on the table.

reapplyFilters(): void;

Returns

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

Returns

Specifies if the first column contains special formatting.

setHighlightFirstColumn(highlightFirstColumn: boolean): void;

Parameters

Returns

Specifies if the last column contains special formatting.

setHighlightLastColumn(highlightLastColumn: boolean): void;

Parameters

Returns

Name of the table.

setName(name: string): void;

Parameters

Returns

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

Returns

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

Returns

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

Returns

Examples

/**
 * This script sets all the tables in the workbook to have banded rows. 
 */
function main(workbook: ExcelScript.Workbook) {
  // Get all the tables.
  const tables = workbook.getTables();

  // For each table, set the banded row formatting to true.
  tables.forEach((table) => {
    table.setShowBandedRows(true);
  });
}

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

Returns

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

setShowHeaders(showHeaders: boolean): void;

Parameters

Returns

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

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

setShowTotals(showTotals: boolean): void;

Parameters

Returns

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