Share via

Need an Office script function to suppress/ enable subtoals only for a particular row value in an excel Pivot Table

Shweta Pradhan 0 Reputation points
2025-08-26T10:07:07.8366667+00:00

I have a scenario where i want to create a pivot table using office script.
For example below it the data
User's image

Based on this data i need to create a pivot table, but i want to hide the highlighted state level subtotals.
User's image The subtotals can be hidden using an office script but when the excel file is finally generated i get an error warning that the file is corrupted.
User's image

User's image

This part of the script which is causing this error is
let codeField = pivotTable1.getHierarchy("State").getPivotField("State");

    if (codeField) {

        codeField.setSubtotals({ automatic: false, sum: false, average: false, count: false });

    }

Is there any other way to disable the subtotals in an office script.
I need to use an office script since these reports are to be generated daily, so this office script will be used in a power automate flow to extract the data from a Power bi dashboard and then create a pivot table on the extracted data .So we cant disable the subtotals manually.
I have tried to disable the subtotals at the state level using field setting, pivot table settings but none of them worked since those steps give a generic code and turns off the subtoals for everything.

Microsoft 365 and Office | Excel | For business | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jess-Q 1,420 Reputation points Microsoft External Staff Moderator
    2025-08-26T13:35:26.1366667+00:00

    Hi @Shweta Pradhan

    Thank you for posting your question in the Microsoft Q&A forum.    

    Regarding your issue, I wanted to let you know that my knowledge of Office Scripts is quite limited. In this Excel tag, we primarily focus on features and formulas, so I’m not able to provide much help with scripting-related questions. I also don’t have the environment or technical setup to test Office Scripts code directly. 

    However, I did come across this link Excel script for generating pivot tables without subtotals that discusses hiding subtotals, which might be relevant to your issue. It could be worth checking out to see if it helps. 

    If that doesn’t resolve your problem, I recommend posting your question directly to Stack Overflow under the Office Scripts tag. They have a dedicated community with deeper expertise in scripting and automation, and you’re likely to get more accurate and helpful responses there. 

    For more details, please refer to: Newest 'office-scripts' Questions - Stack Overflow 

    Disclaimer: Those links non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it. 

    Once again, I truly apologize for having to redirect you to another community. I genuinely wish I could help more, but given my limited knowledge in this area, I wouldn’t want to risk giving you inaccurate advice. I hope you understand, and I really appreciate your patience.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.