Skip to main content

Generate and Edit Pivot Table

APIDescriptionExample
Workbook.PivotCachesFunction that returns an object for managing all pivot table caches in the workbooklet workbook = instance.Application.ActiveWorkbook;

let pivotCaches = workbook.PivotCaches();
PivotCaches.CountProperty that returns the number of all pivot table caches in the workbooklet workbook = instance.Application.ActiveWorkbook;
let pivotCaches = workbook.PivotCaches();

console.log(pivotCaches.Count);
PivotCaches.CreateFunction that creates a new pivot table cachelet workbook = instance.Application.ActiveWorkbook;
let pivotCaches = workbook.PivotCaches();

pivotCaches.Create({
SourceType: instance.Enums.XlPivotTableSourceType.xlDatabase,
SourceData: worksheet.Name + "!A1:P701",
Version: 8
});
PivotCaches.ItemFunction that returns a pivot table cache object at a specific indexlet workbook = instance.Application.ActiveWorkbook;
let pivotCaches = workbook.PivotCaches();

let pivotCache = pivotCaches.Item(1);
PivotCache.CreatePivotTableFunction that creates a pivot table based on a pivot table cachelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;

let pivotCaches = workbook.PivotCaches();
let pivotCache = pivotCaches.Create({
SourceType: instance.Enums.XlPivotTableSourceType.xlDatabase,
SourceData: worksheet.Name + "!A1:P701",
Version: 8
});

let pivotSheet = workbook.Worksheets.Add();
let pivotTable = pivotCache.CreatePivotTable({
TableDestination: pivotSheet.Name + "!A3",
TableName: "PivotTable 1",
DefaultVersion: 8
});
PivotCache.RefreshFunction that refreshes the pivot table cachelet workbook = instance.Application.ActiveWorkbook;
let pivotCaches = workbook.PivotCaches();

let pivotCache = pivotCaches.Item(1);
pivotCache.Refresh();
Worksheet.PivotTablesFunction that returns the object managing pivot tables within the sheet
Note: When an index is passed as a parameter, returns the pivot table object for that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;

let pivotTables = worksheet.PivotTables(); // Returns object managing pivot tables within the sheet
let pivotTable = worksheet.PivotTables(1); // Returns pivot table object for specific index within the sheet
PivotTables.CountProperty that returns the number of pivot tableslet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();

console.log(pivotTables.Count);
PivotTables.AddFunction that adds a pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;

let pivotCaches = workbook.PivotCaches();
let pivotCache = pivotCaches.Create({
SourceType: instance.Enums.XlPivotTableSourceType.xlDatabase,
SourceData: worksheet.Name + "!A1:P701",
Version: 8
});

let pivotSheet = workbook.Worksheets.Add();
let pivotTables = pivotSheet.PivotTables();

let pivotTable = pivotTables.Add({
PivotCache: pivotCache,
TableDestination: pivotSheet.Name + "!A3",
TableName: "PivotTable 1",
DefaultVersion: 8
});
PivotTables.ItemFunction that returns a pivot table object at a specific indexlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();

let pivotTable = pivotTables.Item(1);
PivotTable.ColumnGrandProperty that returns or changes whether to display the column grand totalslet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

console.log(pivotTable.ColumnGrand);
pivotTable.ColumnGrand = true;
PivotTable.ColumnRangeProperty that returns a range object that includes the column area of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let range = pivotTable.ColumnRange;
PivotTable.DataBodyRangeProperty that returns the range object corresponding to the values area of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let range = pivotTable.DataBodyRange;
PivotTable.DataLabelRangeProperty that returns a range object that includes the labels of the data fields in the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let range = pivotTable.DataLabelRange;
PivotTable.DataPivotFieldProperty that returns the pivot field object that manages all data fields of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let pivotField = pivotTable.DataPivotField;
PivotTable.EnableDataValueEditingProperty that returns or changes whether to allow direct editing of the pivot table's data arealet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

console.log(pivotTable.EnableDataValueEditing);
pivotTable.EnableDataValueEditing = false;
PivotTable.GrandTotalNameProperty that returns or changes the label of the grand total column or row of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

console.log(pivotTable.GrandTotalName);
pivotTable.GrandTotalName = "Regional Total";
PivotTable.NameProperty that returns or changes the name of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

console.log(pivotTable.Name);
pivotTable.Name = "PivotTable 1";
PivotTable.PageRangeProperty that returns a range object that includes the page area of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let range = pivotTable.PageRange;
PivotTable.RowGrandProperty that returns or changes whether to display the row grand totalslet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

console.log(pivotTable.RowGrand);
pivotTable.RowGrand = true;
PivotTable.RowRangeProperty that returns a range object that includes the row area of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let range = pivotTable.RowRange;
PivotTable.TableRange1Property that returns a range object that includes the entire pivot table excluding the page fieldslet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let range = pivotTable.TableRange1;
PivotTable.TableRange2Property that returns a range object that includes the entire pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let range = pivotTable.TableRange2;
PivotTable.TableStyle2Property that returns or changes the style of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

console.log(pivotTable.TableStyle2);
pivotTable.TableStyle2 = "PivotStyleLight1";
PivotTable.ColumnFieldsFunction that returns the pivot field manager object that manages the column fields within the pivot table
Note: When an index is passed as a parameter, returns the pivot field object that manages the column field of that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let pivotFields = pivotTable.ColumnFields(); // Returns the Pivot Field Management Object.
let pivotField = pivotTable.ColumnFields(1); // Returns the Pivot Field Object at the specified index.
PivotFields.CountProperty that returns the number of pivot fieldslet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();

console.log(pivotFields.Count);
PivotFields.ItemFunction that returns a pivot field object at a specific indexlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();

let pivotField = pivotFields.Item(1);
PivotField.AutoSortFieldProperty that returns the auto-sort mode of the pivot field as a stringlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.AutoSortField);
PivotField.AutoSortOrderProperty that returns the auto-sort mode of the pivot field as an enum valuelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.AutoSortOrder);
PivotField.BaseFieldProperty that returns or changes the base field to which the pivot field calculation method is applied
Note: Works properly only when it is a data field		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.DataFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.BaseField);
pivotField.BaseField = "ORDER_DATE";
PivotField.BaseItemProperty that returns or changes the base pivot item of the base field to which the pivot field calculation method is applied
Note: Works properly only when it is a data field		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.DataFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.BaseItem);
pivotField.BaseItem = "5/16/89";
PivotField.CalculationProperty that returns or changes the calculation method of the pivot field
Note: Works properly only when it is a data field		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.DataFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.Calculation);
pivotField.Calculation = instance.Enums.XlPivotFieldCalculation.xlDifferenceFrom;
PivotField.CaptionProperty that returns or changes the label of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.Caption);
pivotField.Caption = "Sum : Sale Price";
PivotField.ChildFieldProperty that returns the child pivot field object of the pivot field
Note: Works properly when grouped and a child pivot field is specified		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let childField = pivotField.ChildField;
PivotField.DataRangeProperty that returns the range object containing the pivot field's datalet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let range = pivotField.DataRange;
PivotField.DragToColumnProperty that returns or changes whether the pivot field can be dragged to the column positionlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.DragToColumn);
pivotField.DragToColumn = true;
PivotField.DragToDataProperty that returns or changes whether the pivot field can be dragged to the data positionlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.DragToData);
pivotField.DragToData = true;
PivotField.DragToHideProperty that returns or changes whether the pivot field can be hidden by dragginglet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.DragToHide);
pivotField.DragToHide = true;
PivotField.DragToPageProperty that returns or changes whether the pivot field can be dragged to the page positionlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.DragToPage);
pivotField.DragToPage = true;
PivotField.DragToRowProperty that returns or changes whether the pivot field can be dragged to the row positionlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.DragToRow);
pivotField.DragToRow = true;
PivotField.FunctionProperty that returns or changes the type of function used to summarize the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.Function);
pivotField.Function = instance.Enums.XlConsolidationFunction.xlSum;
PivotField.GroupLevelProperty that returns the position of a specific pivot field in the pivot field grouplet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.GroupLevel);
PivotField.LabelRangeProperty that returns the range object containing the pivot field labelslet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let range = pivotField.LabelRange;
PivotField.NameProperty that returns or changes the name of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.Name);
pivotField.Name = "Sum : Sale Price";
PivotField.NumberFormatProperty that returns or changes the display format of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.NumberFormat);
pivotField.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)";
PivotField.OrientationProperty that returns or changes the orientation of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

console.log(pivotTable.PivotFields.Item(1).Orientation);

pivotTable.PivotFields(1).Orientation = instance.Enums.XlPivotFieldOrientation.xlRowField;
pivotTable.PivotFields(3).Orientation = instance.Enums.XlPivotFieldOrientation.xlColumnField;
pivotTable.PivotFields(7).Orientation = instance.Enums.XlPivotFieldOrientation.xlDataField;
PivotField.ParentFieldProperty that returns the parent pivot field object of the pivot field
Note: Works correctly when grouped and a parent pivot field is assigned		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let parentField = pivotField.ParentField;
PivotField.PositionProperty that returns or changes the position of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.Position);
pivotField.Position = 1;
PivotField.SourceNameProperty that returns the name of the source data of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.SourceName);
PivotField.SubtotalNameProperty that returns the name of the pivot field's subtotallet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.SubtotalName);
PivotField.ValueProperty that returns or changes the name of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

console.log(pivotField.Value);
pivotField.Value = "Sum : Sale Price";
PivotField.ChildItemsFunction that returns the object that manages the child pivot items of the pivot field
Note: When an index is passed as a parameter, returns the pivot item object for that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let pivotItems = pivotField.ChildItems(); // Returns pivot item management object
let pivotItem = pivotField.ChildItems(1); // Returns the pivot item object for a specific index
PivotItems.CountProperty that returns the number of pivot itemslet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();

console.log(pivotItems.Count);
PivotItems.AddFunction that adds a pivot itemlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();

pivotItems.Add("1998");
PivotItems.ItemFunction that returns the pivot item object for a specific indexlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();

let pivotItem = pivotItems.Item(1);
PivotItem.CaptionProperty that returns or changes the label of the pivot itemlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();
let pivotItem = pivotItems.Item(1);

console.log(pivotItem.Caption);
pivotItem.Caption = "Amarilla";
PivotItem.NameProperty that returns or changes the name of the pivot itemlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();
let pivotItem = pivotItems.Item(1);

console.log(pivotItem.Name);
pivotItem.Name = "Amarilla";
PivotItem.PositionProperty that returns or changes the position of the pivot item within the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();
let pivotItem = pivotItems.Item(1);

console.log(pivotItem.Position);
pivotItem.Position = 1;
PivotItem.SourceNameProperty that returns the name of the source data of the pivot itemlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();
let pivotItem = pivotItems.Item(1);

console.log(pivotItem.SourceName);
pivotItem.SourceName = "Amarilla";
PivotItem.ValueProperty that returns or changes the name of the pivot itemlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();
let pivotItem = pivotItems.Item(1);

console.log(pivotItem.Value);
pivotItem.Value = "Amarilla";
PivotItem.VisibleProperty that returns or changes the visibility state of the pivot itemlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();
let pivotItem = pivotItems.Item(1);

console.log(pivotItem.Visible);
pivotItem.Visible = false;
PivotItem.ChildItemsFunction that returns the object that manages the child pivot items of the pivot item
Note: When an index is passed as a parameter, returns the pivot item object for that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();
let pivotItem = pivotItems.Item(1);

let childItems = pivotItem.ChildItems(); // Returns pivot item management object
let childItem = pivotItem.ChildItems(1); // Returns the pivot item object for a specific index
PivotItem.DeleteFunction that removes a pivot itemlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);
let pivotItems = pivotField.ChildItems();
let pivotItem = pivotItems.Item(1);

pivotItem.Delete();
PivotField.HiddenItemsFunction that returns the object that manages hidden pivot items of the pivot field
Note: When an index is passed as a parameter, returns the pivot item object for that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let pivotItems = pivotField.HiddenItems(); // Returns pivot item management object
let pivotItem = pivotField.HiddenItems(1); // Returns the pivot item object for a specific index
PivotField.SubtotalsFunction that returns whether the pivot field's subtotals are displayed
Note 1) The display status is determined as true/false for the following 12 items
Index items
1 Automatic
2 Sum
3 Count
4 Average
5 Max
6 Min
7 Product
8 Count Nums
9 StdDev
10 StdDevp
11 Var
12 Varp
Note 2) If 'Automatic' corresponding to index 1 is true, all other values are false
Note 3) When an index is passed as a parameter, the display status of the corresponding index's subtotal is returned, and it works correctly only when it is not a data field		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let subtotalList = pivotField.Subtotals(); // Returns the Subtotal Display Status Array.
let subtotalAutomatic = pivotField.Subtotals(1); // Returns the subtotal display status at the specified index.
PivotField.SetSubtotalsFunction that changes whether the pivot field's subtotals are displayed
Note 1) The display status is determined as true/false for the following 12 items
Index items
1 Automatic
2 Sum
3 Count
4 Average
5 Max
6 Min
7 Product
8 Count Nums
9 StdDev
10 StdDevp
11 Var
12 Varp
cf 2) If Automatic (index 1) is true, all other values are false
cf 3) When the index is passed as a parameter, whether the subtotal is displayed at that index is changed; operates normally only when it is not a data field		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

const subtotals = [ false, true, true, true, false, false, false, false, false, false, false, false ];
pivotField.SetSubtotals(null, subtotals); // Change display for all subtotals using array form

pivotField.SetSubtotals(1, true); // Change whether subtotal is displayed at a specific index
PivotField.VisibleItemsFunction that returns an object that manages the visible pivot items of the pivot field
cf) When an index is passed as a parameter, returns the pivot item object at that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let pivotItems = pivotField.VisibleItems(); // Returns pivot item manager object
let pivotItem = pivotField.VisibleItems(1); // Returns pivot item object at specific index
PivotField.AutoSortFunction that sets the auto-sort conditions of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

pivotField.AutoSort({
Order: instance.Enums.XlSortOrder.xlDescending,
Field: "Sum of Sales"
});
PivotField.ClearAllFiltersFunction that removes all filters of the pivot fieldlet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

pivotField.ClearAllFilters();
PivotField.PivotItemsFunction that returns an object that manages all pivot items of the pivot field
cf) When an index is passed as a parameter, returns the pivot item object at that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);
let pivotFields = pivotTable.ColumnFields();
let pivotField = pivotFields.Item(1);

let pivotItems = pivotField.PivotItems(); // Returns pivot item manager object
let pivotItem = pivotField.PivotItems(1); // Returns pivot item object at specific index
PivotTable.DataFieldsFunction that returns a pivot field manager object that manages data fields within the pivot table
cf) When an index is passed as a parameter, returns the pivot field object that manages the data field at that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let pivotFields = pivotTable.DataFields(); // Returns pivot field manager object
let pivotField = pivotTable.DataFields(1); // Returns pivot field object at specific index
PivotTable.PageFieldsFunction that returns a pivot field manager object that manages page fields within the pivot table
cf) When an index is passed as a parameter, returns the pivot field object that manages the page field at that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let pivotFields = pivotTable.PageFields(); // Returns the Pivot Field Management Object.
let pivotField = pivotTable.PageFields(1); // Returns the Pivot Field Object at the specified index.
PivotTable.RowFieldsFunction that returns a pivot field manager object that manages row fields within the pivot table
cf) When an index is passed as a parameter, returns the pivot field object that manages the row field at that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let pivotFields = pivotTable.RowFields(); // Return pivot field manager object
let pivotField = pivotTable.RowFields(1); // Return pivot field object of specific index
PivotTable.AddDataFieldFunction that adds a data field to the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let pivotField = pivotTable.AddDataField({
Field: pivotTable.PivotFields("Score"),
Caption: "Total Score"
});
PivotTable.AddFieldsFunction that adds row, column, and page fields to the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

pivotTable.AddFields({
ColumnFields: [ "Status", "Closed_By" ]
});
PivotTable.ClearAllFiltersFunction that removes all filters of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

pivotTable.ClearAllFilters();
PivotTable.ClearTableFunction that removes all fields, filters, and sort information of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

pivotTable.ClearTable();
PivotTable.GetPivotDataFunction that returns a range object matching specific conditions of the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let range = pivotTable.GetPivotData({
DataField: "Quantity",
Field1: "Warehouse",
Item1: "Charis"
});
PivotTable.PivotCacheFunction that returns the pivot table cache object that composes the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let pivotCache = pivotTable.PivotCache();
PivotTable.PivotFieldsFunction that returns a pivot field manager object that manages all fields within the pivot table
cf) When an index is passed as a parameter, returns the pivot field object that manages all fields at that index		let workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

let pivotFields = pivotTable.PivotFields(); // Returns pivot field manager object
let pivotField = pivotTable.PivotFields(1); // Returns pivot field object at specific index
PivotTable.RefreshTableFunction that refreshes the pivot table from its source datalet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

pivotTable.RefreshTable();
PivotTable.RowAxisLayoutFunction that changes the layout settings for all pivot fields in the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

pivotTable.RowAxisLayout(instance.Enums.XlLayoutRowType.xlCompactRow);
PivotTable.SubtotalLocationFunction that changes the subtotal location for all pivot fields in the pivot tablelet workbook = instance.Application.ActiveWorkbook;
let worksheet = workbook.ActiveSheet;
let pivotTables = worksheet.PivotTables();
let pivotTable = pivotTables.Item(1);

pivotTable.SubtotalLocation(instance.Enums.XlSubtototalLocationType.xlAtTop);

Data Processing , Built-in Function Libraries , Autocomplete , Data Validation , Filter and Sort , Find and Replace