ExcelScript.PivotLayout interface

Sets whether or not to display a blank line after each item. This is set at the global level for the PivotTable and applied to individual PivotFields. This function overwrites the setting for all fields in the PivotTable to the value of display parameter.

displayBlankLineAfterEachItem(display: boolean): void;

Parameters

Returns

The alt text description of the PivotTable.

Alt text provides alternative, text-based representations of the information contained in the PivotTable. This information is useful for people with vision or cognitive impairments who may not be able to see or understand the table. A title can be read to a person with a disability and is used to determine whether they wish to hear the description of the content.

getAltTextDescription(): string;

Returns

The alt text title of the PivotTable.

Alt text provides alternative, text-based representations of the information contained in the PivotTable. This information is useful for people with vision or cognitive impairments who may not be able to see or understand the table. A title can be read to a person with a disability and is used to determine whether they wish to hear the description of the content.

getAltTextTitle(): string;

Returns

Specifies if formatting will be automatically formatted when it's refreshed or when fields are moved.

getAutoFormat(): boolean;

Returns

Returns the range where the PivotTable's data values reside.

getBodyAndTotalRange(): Range;

Returns

Examples

/**
 * This sample finds the first PivotTable in the workbook and logs the values in the "Grand Total" cells.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first PivotTable in the workbook.
  let pivotTable = workbook.getPivotTables()[0];

  // Get the names of each data column in the PivotTable.
  let pivotColumnLabelRange = pivotTable.getLayout().getColumnLabelRange();

  // Get the range displaying the pivoted data.
  let pivotDataRange = pivotTable.getLayout().getBodyAndTotalRange();

  // Get the range with the "grand totals" for the PivotTable columns.
  let grandTotalRange = pivotDataRange.getLastRow();

  // Print each of the "Grand Totals" to the console.
  grandTotalRange.getValues()[0].forEach((column, columnIndex) => {
    console.log(`Grand total of ${pivotColumnLabelRange.getValues()[0][columnIndex]}: ${grandTotalRange.getValues()[0][columnIndex]}`);
    // Example log: "Grand total of Sum of Crates Sold Wholesale: 11000"
  });
}

Returns the range where the PivotTable's column labels reside.

getColumnLabelRange(): Range;

Returns

Gets the DataHierarchy that is used to calculate the value in a specified range within the PivotTable.

getDataHierarchy(cell: Range | string): DataPivotHierarchy;

Parameters

Returns

The text that is automatically filled into any empty cell in the PivotTable if fillEmptyCells == true. Note that this value persists if fillEmptyCells is set to false, and that setting this value does not set that property to true. By default, this is an empty string.

getEmptyCellText(): string;

Returns

Specifies if the field list can be shown in the UI.

getEnableFieldList(): boolean;

Returns

Specifies whether empty cells in the PivotTable should be populated with the emptyCellText. Default is false. Note that the value of emptyCellText persists when this property is set to false.

getFillEmptyCells(): boolean;

Returns

Returns the range of the PivotTable's filter area.

getFilterAxisRange(): Range;

Returns

This property indicates the PivotLayoutType of all fields on the PivotTable. If fields have different states, this will be null.

getLayoutType(): PivotLayoutType;

Returns

Specifies if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items.

getPreserveFormatting(): boolean;

Returns

Returns the range the PivotTable exists on, excluding the filter area.

getRange(): Range;

Returns

Returns the range where the PivotTable's row labels reside.

getRowLabelRange(): Range;

Returns

Specifies if the PivotTable report shows grand totals for columns.

getShowColumnGrandTotals(): boolean;

Returns

Specifies whether the PivotTable displays field headers (field captions and filter drop-downs).

getShowFieldHeaders(): boolean;

Returns

Specifies if the PivotTable report shows grand totals for rows.

getShowRowGrandTotals(): boolean;

Returns

This property indicates the SubtotalLocationType of all fields on the PivotTable. If fields have different states, this will be null.

getSubtotalLocation(): SubtotalLocationType;

Returns

Sets the "repeat all item labels" setting across all fields in the PivotTable.

repeatAllItemLabels(repeatLabels: boolean): void;

Parameters

Returns

The alt text description of the PivotTable.

Alt text provides alternative, text-based representations of the information contained in the PivotTable. This information is useful for people with vision or cognitive impairments who may not be able to see or understand the table. A title can be read to a person with a disability and is used to determine whether they wish to hear the description of the content.

setAltTextDescription(altTextDescription: string): void;

Parameters

Returns

The alt text title of the PivotTable.

Alt text provides alternative, text-based representations of the information contained in the PivotTable. This information is useful for people with vision or cognitive impairments who may not be able to see or understand the table. A title can be read to a person with a disability and is used to determine whether they wish to hear the description of the content.

setAltTextTitle(altTextTitle: string): void;

Parameters

Returns

Specifies if formatting will be automatically formatted when it's refreshed or when fields are moved.

setAutoFormat(autoFormat: boolean): void;

Parameters

Returns

Sets the PivotTable to automatically sort using the specified cell to automatically select all necessary criteria and context. This behaves identically to applying an autosort from the UI.

setAutoSortOnCell(cell: Range | string, sortBy: SortBy): void;

Parameters

Returns

The text that is automatically filled into any empty cell in the PivotTable if fillEmptyCells == true. Note that this value persists if fillEmptyCells is set to false, and that setting this value does not set that property to true. By default, this is an empty string.

setEmptyCellText(emptyCellText: string): void;

Parameters

Returns

Specifies if the field list can be shown in the UI.

setEnableFieldList(enableFieldList: boolean): void;

Parameters

Returns

Specifies whether empty cells in the PivotTable should be populated with the emptyCellText. Default is false. Note that the value of emptyCellText persists when this property is set to false.

setFillEmptyCells(fillEmptyCells: boolean): void;

Parameters

Returns

This property indicates the PivotLayoutType of all fields on the PivotTable. If fields have different states, this will be null.

setLayoutType(layoutType: PivotLayoutType): void;

Parameters

Returns

Examples

/**
 * This script sets the layout of the "Farms Sales" PivotTable to the "tabular"
 * setting. This places the fields from the Rows area in separate columns.
 */ 
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable named "Farm Sales".
  const pivot = workbook.getPivotTable("Farm Sales");

  // Get the PivotLayout object.
  const layout = pivot.getLayout();

  // Set the layout type to "tabular".
  layout.setLayoutType(ExcelScript.PivotLayoutType.tabular);
}

Specifies if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items.

setPreserveFormatting(preserveFormatting: boolean): void;

Parameters

Returns

Specifies if the PivotTable report shows grand totals for columns.

setShowColumnGrandTotals(showColumnGrandTotals: boolean): void;

Parameters

Returns

Specifies whether the PivotTable displays field headers (field captions and filter drop-downs).

setShowFieldHeaders(showFieldHeaders: boolean): void;

Parameters

Returns

Specifies if the PivotTable report shows grand totals for rows.

setShowRowGrandTotals(showRowGrandTotals: boolean): void;

Parameters

Returns

This property indicates the SubtotalLocationType of all fields on the PivotTable. If fields have different states, this will be null.

setSubtotalLocation(subtotalLocation: SubtotalLocationType): void;

Parameters

Returns

Examples

/**
 * This script displays group subtotals of the "Farms Sales" PivotTable.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable named "Farm Sales".
  const pivot = workbook.getPivotTable("Farm Sales");

  // Get the PivotLayout object.
  const layout = pivot.getLayout();

  // Show all the subtotals at the bottom of each group.
  layout.setSubtotalLocation(ExcelScript.SubtotalLocationType.atBottom);
}