Moving Autogenerated Field in Pivot Table Using Office Script

Question

Currently, I have an Office Script which creates a pivot table that Excel adds a "Σ Values" field to automatically. I want this field, but I want it to be in the Rows, not in the Columns.

My code snippet:

pivotTable.addColumnHierarchy(pivotTable.getHierarchy('Scheduled Pick-up Date'));
pivotTable.addRowHierarchy(pivotTable.getHierarchy('Origin Name'));
pivotTable.addDataHierarchy(pivotTable.getHierarchy('Expected Cases'));
pivotTable.addDataHierarchy(pivotTable.getHierarchy('Payer1 Name'));

This is the result of the above snippet

And this is what I want the result to be

I have tried using the "Insert action", but moving a field in a pivot table is unsupported.

Thank you!

Answer 1

• One more line addRowHierarchy will change the pvt layout as your expected.

pivotTable.addColumnHierarchy(pivotTable.getHierarchy('Scheduled Pick-up Date'));
pivotTable.addRowHierarchy(pivotTable.getHierarchy('Origin Name'));
pivotTable.addDataHierarchy(pivotTable.getHierarchy('Expected Cases'));
pivotTable.addDataHierarchy(pivotTable.getHierarchy('Payer1 Name'));
pivotTable.addRowHierarchy(pivotTable.getHierarchy("Values")); ' ** new code