ExcelScript.PivotLayout interface

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

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

getEnableFieldList(): 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 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

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

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

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