ExcelScript.PivotLayout interface

Package:

ExcelScript

Represents the visual layout of the PivotTable.

Methods

getAutoFormat()	Specifies if formatting will be automatically formatted when it's refreshed or when fields are moved.
getBodyAndTotalRange()	Returns the range where the PivotTable's data values reside.
getColumnLabelRange()	Returns the range where the PivotTable's column labels reside.
getDataHierarchy(cell)	Gets the DataHierarchy that is used to calculate the value in a specified range within the PivotTable.
getEnableFieldList()	Specifies if the field list can be shown in the UI.
getFilterAxisRange()	Returns the range of the PivotTable's filter area.
getLayoutType()	This property indicates the PivotLayoutType of all fields on the PivotTable. If fields have different states, this will be null.
getPreserveFormatting()	Specifies if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items.
getRange()	Returns the range the PivotTable exists on, excluding the filter area.
getRowLabelRange()	Returns the range where the PivotTable's row labels reside.
getShowColumnGrandTotals()	Specifies if the PivotTable report shows grand totals for columns.
getShowRowGrandTotals()	Specifies if the PivotTable report shows grand totals for rows.
getSubtotalLocation()	This property indicates the SubtotalLocationType of all fields on the PivotTable. If fields have different states, this will be null.
setAutoFormat(autoFormat)	Specifies if formatting will be automatically formatted when it's refreshed or when fields are moved.
setAutoSortOnCell(cell, sortBy)	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.
setEnableFieldList(enableFieldList)	Specifies if the field list can be shown in the UI.
setLayoutType(layoutType)	This property indicates the PivotLayoutType of all fields on the PivotTable. If fields have different states, this will be null.
setPreserveFormatting(preserveFormatting)	Specifies if formatting is preserved when the report is refreshed or recalculated by operations such as pivoting, sorting, or changing page field items.
setShowColumnGrandTotals(showColumnGrandTotals)	Specifies if the PivotTable report shows grand totals for columns.
setShowRowGrandTotals(showRowGrandTotals)	Specifies if the PivotTable report shows grand totals for rows.
setSubtotalLocation(subtotalLocation)	This property indicates the SubtotalLocationType of all fields on the PivotTable. If fields have different states, this will be null.

Method Details

getAutoFormat()

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

getAutoFormat(): boolean;

Returns

boolean

getBodyAndTotalRange()

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

getBodyAndTotalRange(): Range;

Returns

ExcelScript.Range

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

getColumnLabelRange()

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

getColumnLabelRange(): Range;

Returns

ExcelScript.Range

getDataHierarchy(cell)

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

getDataHierarchy(cell: Range | string): DataPivotHierarchy;

Parameters

cell

ExcelScript.Range | string

A single cell within the PivotTable data body.

Returns

ExcelScript.DataPivotHierarchy

getEnableFieldList()

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

getEnableFieldList(): boolean;

Returns

boolean

getFilterAxisRange()

Returns the range of the PivotTable's filter area.

getFilterAxisRange(): Range;

Returns

ExcelScript.Range

getLayoutType()

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

getLayoutType(): PivotLayoutType;

Returns

ExcelScript.PivotLayoutType

getPreserveFormatting()

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

boolean

getRange()

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

getRange(): Range;

Returns

ExcelScript.Range

getRowLabelRange()

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

getRowLabelRange(): Range;

Returns

ExcelScript.Range

getShowColumnGrandTotals()

Specifies if the PivotTable report shows grand totals for columns.

getShowColumnGrandTotals(): boolean;

Returns

boolean

getShowRowGrandTotals()

Specifies if the PivotTable report shows grand totals for rows.

getShowRowGrandTotals(): boolean;

Returns

boolean

getSubtotalLocation()

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

getSubtotalLocation(): SubtotalLocationType;

Returns

ExcelScript.SubtotalLocationType

setAutoFormat(autoFormat)

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

setAutoFormat(autoFormat: boolean): void;

Parameters

autoFormat

boolean

Returns

void

setAutoSortOnCell(cell, sortBy)

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

cell

ExcelScript.Range | string

A single cell to use get the criteria from for applying the autosort.

sortBy

ExcelScript.SortBy

The direction of the sort.

Returns

void

setEnableFieldList(enableFieldList)

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

setEnableFieldList(enableFieldList: boolean): void;

Parameters

enableFieldList

boolean

Returns

void

setLayoutType(layoutType)

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

layoutType

ExcelScript.PivotLayoutType

Returns

void

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

setPreserveFormatting(preserveFormatting)

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

preserveFormatting

boolean

Returns

void

setShowColumnGrandTotals(showColumnGrandTotals)

Specifies if the PivotTable report shows grand totals for columns.

setShowColumnGrandTotals(showColumnGrandTotals: boolean): void;

Parameters

showColumnGrandTotals

boolean

Returns

void

setShowRowGrandTotals(showRowGrandTotals)

Specifies if the PivotTable report shows grand totals for rows.

setShowRowGrandTotals(showRowGrandTotals: boolean): void;

Parameters

showRowGrandTotals

boolean

Returns

void

setSubtotalLocation(subtotalLocation)

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

subtotalLocation

ExcelScript.SubtotalLocationType

Returns

void

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