It was possible to change an existing pivot table's data source when developing an Excel COM https://stackoverflow.com/a/9764063/4664207 and when using https://answers.microsoft.com/en-us/msoffice/forum/all/change-pivot-table-data-source-through-vba/073b0e3b-1a9c-4d7d-bdfc-fc892040f7b6, but currently that does not seem to be possible in the JavaScript API.
It would be helpful to add this functionality to the JavaScript API because currently you can only set the PivotTable data source when https://docs.microsoft.com/en-us/javascript/api/excel/excel.pivottable?view=excel-js-preview#excel-excel-pivottable-getdatasourcestring-member(1) a new pivot table. It's common for Excel users to change a pivot table's data source, so it would be helpful to support it programmatically in the JavaScript API.
1 Comment
- AdrianWu
Microsoft
Hi jones-ch , the Excel JS API does not currently support changing the data source, only the value of the data source. However, if the reason to change the data source comes from your data source getting new data appended (like the COM add-in example you've provided), there is an effective way to assign a "table" instead of a "range" as your data source. This way, the pivot table must point to a fixed table, but the table itself can be changed, such as appending new rows. Hope this workaround is helpful!!
