ExcelScript.TableColumn interface

Package:

ExcelScript

Represents a column in a table.

Remarks

Examples

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

  // Get the range of the table column named "Type".
  const typeColumn : ExcelScript.TableColumn = table.getColumn("Type");
  const range = typeColumn.getRange();

  // Do something with the range...
}

Methods

delete()	Deletes the column from the table.
getFilter()	Retrieves the filter applied to the column.
getHeaderRowRange()	Gets the range object associated with the header row of the column.
getId()	Returns a unique key that identifies the column within the table.
getIndex()	Returns the index number of the column within the columns collection of the table. Zero-indexed.
getName()	Specifies the name of the table column.
getRange()	Gets the range object associated with the entire column.
getRangeBetweenHeaderAndTotal()	Gets the range object associated with the data body of the column.
getTotalRowRange()	Gets the range object associated with the totals row of the column.
setName(name)	Specifies the name of the table column.

Method Details

delete()

Deletes the column from the table.

delete(): void;

Returns

void

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

getFilter()

Retrieves the filter applied to the column.

getFilter(): Filter;

Returns

ExcelScript.Filter

Examples

/**
 * This script adds a table filter to only show the top 10% of values 
 * belonging to a particular column.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the first table on the current worksheet.
    const table = workbook.getActiveWorksheet().getTables()[0];

    // Get the filter for the "PageViews" table column.
    const pageViewFilter = table.getColumnByName("PageViews").getFilter();

    // Apply a filter to only show the rows with the top 10% of values in this column.
    pageViewFilter.applyTopPercentFilter(10);
}

getHeaderRowRange()

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

getHeaderRowRange(): Range;

Returns

ExcelScript.Range

getId()

Returns a unique key that identifies the column within the table.

getId(): number;

Returns

number

getIndex()

Returns the index number of the column within the columns collection of the table. Zero-indexed.

getIndex(): number;

Returns

number

getName()

Specifies the name of the table column.

getName(): string;

Returns

string

getRange()

Gets the range object associated with the entire column.

getRange(): Range;

Returns

ExcelScript.Range

getRangeBetweenHeaderAndTotal()

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

getRangeBetweenHeaderAndTotal(): Range;

Returns

ExcelScript.Range

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

getTotalRowRange()

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

getTotalRowRange(): Range;

Returns

ExcelScript.Range

Examples

/**
 * This script logs the value in the total row of a table column.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the table named "Cities".
    const table = workbook.getTable("Cities");

    // Get the total row from the "Population" column.
    const column = table.getColumn("Population");
    const totalRange = column.getTotalRowRange();

    // Log the total value.
    console.log(totalRange.getValue());
}

setName(name)

Specifies the name of the table column.

setName(name: string): void;

Parameters

name

string

Returns

void

Examples

/**
 * This script renames a column in an existing table.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the "Employee" table.
  const employeeTable = workbook.getTable("Employee");

  // Rename a column from "EmplID" to "Employee ID".
  const idColumn = employeeTable.getColumnByName("EmplID");
  idColumn.setName("Employee ID");
}