Request to enable external data sources for pivot tables to be set via the ExcelAPI, a capability that is already present in Excel when creating pivot tables manually.
Currently the only way to set the data source for a pivot table seems to be the `Excel.PivotTableCollection.add()` method which only allows for a local range or table to be passed. This limits the max size of the source data to Excel's worksheet limit of ~1M rows x ~16k columns. May of our users are using datasets significantly larger than that.
2 Comments
Yes it is, how about wokrarounds like this:
- Split data across multiple sheets if within Excel’s row limit (1,048,576 rows × 16,384 columns).
- Use Power Query/Data Model in Excel for large datasets (though it's not accessible via the current API).
- Leverage external tools like Power BI or direct database connections for datasets beyond Excel’s limits.
Hi,
Thanks for the reply, but I'd appreciate an elaboration:
item 1: Splitting data by creating separate ranges or tables is of course possible, but from what I understand a pivot table can only have one of those set as a data source. So for the purposes of using pivot tables, splitting the data basically removes all but one of those parts from consderation. Is my understanding correct?
item 2: As you mentioned - not accessible via API, so not of use to us.
item 3: Doing it within our addin is the whole point, so external tools are not relevant to this discussion. As for direct database connection: are we talking about this "use an external data source" option available when creating a pivot table manually, as seen in the screenshot? My research indicates that this is not available via the modern API, and adding this capability is the whole point of this ER. Did I miss this fucntionality already existing in the API?
