Please help on Pivot Table Refresh online

Question

Please help on Pivot Table Refresh online

PBIX MM 1

Hello,

I'm having issue with refreshing pivot tables using Excel Online.
The pivot tables are built using data model with data source from the tables in existing workbook.
When I refresh manually using "Refresh Selected Connection" it works perfectly.

But when I refresh using office script, it doesn't work.

Any help, please!

Ceasar Chen_MSFT 4,481 Reputation points

2022-08-01T02:13:58.527+00:00

Hi, @PBIX MM
Your question is related to scripting, and I'm in charge of Office General. It is recommended that you wait for a reply from the relevant expert.

1 answer

Your answer

Ceasar Chen_MSFT 4,481 Reputation points

2022-08-01T02:13:58.527+00:00

Hi, @PBIX MM
Your question is related to scripting, and I'm in charge of Office General. It is recommended that you wait for a reply from the relevant expert.

Answer 1

Petra Ronald 26 Microsoft Employee

Hi, @PBIX MM
Currently, Power Query and pivot table refresh is not supported for Office Scripts. We track Office Scripts feature requests here: http://aka.ms/ExcelSuggestions. It looks like this feature has already been requested by others: https://feedbackportal.microsoft.com/feedback/idea/29b33e9d-262e-ec11-b6e6-00224827bbc2. Please upvote this existing request as these requests are considered when we go through our planning process.
Thanks,
The Office Scripts team

PBIX MM 1 Reputation point

2022-08-02T13:58:36.397+00:00

Hi @Petra Ronald ,

Thank you for your information.
Actually, I tried to refresh the data using Power Pivot sourcing the data from the same workbook ( by inserting data from SQL DB through Power Automate & Graph API at a scheduled time ).
The data is already there in the workbook and just need to populate in the Pivot Tables after refresh.
It works perfectly if I press "Refresh Selected Connection" but the recorded Office Script doesn't work as expected.
So, I don't think it's because of current limitations with Refresh option as Power Query was not used in this context.
And even if I use PQ, it should be fine as you can find PQ refresh is now available for selected data sources here.

Best,

PBIXMM
Sven Aelterman 36 Reputation points Microsoft Employee

2025-01-29T23:44:10.2866667+00:00

@Petra Ronald Is this still a limitation today? Would it also make it impossible to apply a manual filter on a pivot table that draws data from an external source?
Michelle Ran 346 Reputation points Microsoft Employee

2025-01-31T21:18:21.2933333+00:00

Hi @Sven Aelterman - Office Scripts supports refreshing pivot tables, including ones that use Power Query as a source - however, directly refreshing a PQ connection can only be done on desktop. For more information, see https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.pivottable?view=office-scripts#excelscript-excelscript-pivottable-refresh-member(1) and https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.workbook?view=office-scripts#excelscript-excelscript-workbook-refreshallpivottables-member(1)

Hope that helps!

Share via

Please help on Pivot Table Refresh online

1 answer

Your answer