Office Script Excel Pivot Table Count Instead of Sum

Question

I'm using Office Script to generate a pivot table and that works. However, for my data hierarchy, I need "count" instead of the default "sum". How do I go about doing that?

I'm adding a hierarchy using the following - this works but summarizes by "sum"

pivotTable.addRowHierarchy(pivotTable.getHierarchy("Company"));
pivotTable.addDataHierarchy(pivotTable.getHierarchy("Unit ID"));

From the script reference page, I need to somehow embed the count function into the above snippet. Does anyone know how to do that? I tried "Record Actions", but it wasn't able to catch the switch from "sum" to "count".

Thanks a lot

Answer 1

Figured it out - to change the summarize function, we need to chain the aggregate function to the end of addDataHierarchy.

The second line in the previous example therefore becomes:

pivotTable.addDataHierarchy(pivotTable.getHierarchy("Unit ID")).setSummarizeBy(ExcelScript.AggregationFunction.count);

For more details refer to setSummarizeBy(summarizeBy).