1

I have an Office Script that creates a Pivot Table. The last thing I would like to do is sort the pivot table on the same column values I filtered. Scripts doesn't record this action and I am not finding any examples. I do not understand how to format sortByValues.

// filter by value greater than $0
    newPivotTable.getRowHierarchy("Vendor Name").getFields()[0].applyFilter({
        valueFilter: {
            condition: ExcelScript.ValueFilterCondition.greaterThan, /* Relationship */
            comparator: 0, /* Value  */
            value: "Sum of Region Amt." /* The name of the data hierarchy.*/
        }
    });
// sort Vendor Name by "Sum of Region Amt." ascending

The code editor seems to accept newPivotTable.getRowHierarchy("Vendor Name").getPivotField("Vendor Name").sortByValues() but I can't get the method of sortByValues to work. I can't format the three parameters correctly.

Skin
  • 9,085
  • 2
  • 13
  • 29
JSP
  • 35
  • 5

1 Answers1

1

This isn't the most complete of answers but this worked for me on the data set I was testing with ...

function main(workbook: ExcelScript.Workbook) {
    let pivotTable = workbook.getPivotTable("MyPivotTable");    
    
    let valueFieldToSortOn = pivotTable.getDataHierarchy("Sum of Amount");
    
    let sortField: string = "Main Group";
    let sortOrder: ExcelScript.SortBy = ExcelScript.SortBy.descending;

    pivotTable.getRowHierarchy(sortField).getPivotField(sortField).sortByValues(sortOrder, valueFieldToSortOn);
}

Image

If you change the field name to Sub Group and then change the sorting direction, everything shifts as expected.

Skin
  • 9,085
  • 2
  • 13
  • 29
  • Thank you so much! I didn't add the pivot table before the data hierarchy. This is what I ended up using: `newPivotTable.getRowHierarchy("Vendor Name").getPivotField("Vendor Name").sortByValues(ExcelScript.SortBy.ascending,newPivotTable.getDataHierarchy("Sum of Region Amt."))` – JSP Mar 23 '23 at 17:07