ExcelScript.SortField interface

Package:

ExcelScript

Represents a condition in a sorting operation.

Properties

ascending	Specifies if the sorting is done in an ascending fashion.
color	Specifies the color that is the target of the condition if the sorting is on font or cell color.
dataOption	Represents additional sorting options for this field.
icon	Specifies the icon that is the target of the condition, if the sorting is on the cell's icon.
key	Specifies the column (or row, depending on the sort orientation) that the condition is on. Represented as an offset from the first column (or row).
sortOn	Specifies the type of sorting of this condition.
subField	Specifies the subfield that is the target property name of a rich value to sort on.

Property Details

ascending

Specifies if the sorting is done in an ascending fashion.

ascending?: boolean;

Property Value

boolean

color

Specifies the color that is the target of the condition if the sorting is on font or cell color.

color?: string;

Property Value

string

Examples

/**
 * This script sorts a range based on the color of the cells.
 * It brings all red cells to the top of the range.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the range (A1:D8) to sort from the current worksheet.
  const worksheet = workbook.getActiveWorksheet();
  const rangeToSort = worksheet.getRange("A1:D8");

  // Create a SortField for color sorting.
  // This sorts the rows based on the fill color of each row's cell in the first column.
  let colorSort: ExcelScript.SortField = {
    ascending: true,
    color: "FF0000", /* red */
    key: 0,
    sortOn: ExcelScript.SortOn.cellColor
  };

  // Apply the SortField to the range.
  rangeToSort.getSort().apply([colorSort]);
}

dataOption

Represents additional sorting options for this field.

dataOption?: SortDataOption;

Property Value

ExcelScript.SortDataOption

Examples

/**
 * This script sorts a table based on the values in column 1.
 * If the text of a column-1 value can be treated as a number, 
 * it will be sorted in numerical order, rather than Unicode order
 * (so 123 will come before 12.3).
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first table on the current worksheet.
  const currentSheet = workbook.getActiveWorksheet();
  const table = currentSheet.getTables()[0];

  // Create the sorting parameters.
  const countSortField: ExcelScript.SortField = {
    key: 1,
    ascending: true,
    dataOption: ExcelScript.SortDataOption.textAsNumber
  };

  // Apply the sort to the table.
  const sort = table.getSort();
  sort.apply([countSortField]);
}

icon

Specifies the icon that is the target of the condition, if the sorting is on the cell's icon.

icon?: Icon;

Property Value

ExcelScript.Icon

key

Specifies the column (or row, depending on the sort orientation) that the condition is on. Represented as an offset from the first column (or row).

key: number;

Property Value

number

Examples

/**
 * This script sorts the used range of the current worksheet.
 */
function main(workbook: ExcelScript.Workbook) {
    // Get the used range of the current worksheet.
    const activeRange = workbook.getActiveWorksheet().getUsedRange();

    // Sort the rows in ascending order based on the last column.
    activeRange.getSort().apply(
        [{
            ascending: true,
            key: activeRange.getColumnCount() - 1
        }],
        false, /* Don't match case. */
        true,  /* Treat the first row as a header rows. */
        ExcelScript.SortOrientation.rows
    );
}

sortOn

Specifies the type of sorting of this condition.

sortOn?: SortOn;

Property Value

ExcelScript.SortOn

subField

Specifies the subfield that is the target property name of a rich value to sort on.

subField?: string;

Property Value

string