Getting the sourceString of all pivot tables in a workbook

Question

I'm trying to run through all pivottables in an Excel workbook and list their sources, but I'm not succeeding. I'm using code like this:

async function SearchInPivots(
  context, wb: Excel.Workbook) {
  let foundPivots = new foundCollection();
  let pts = wb.pivotTables.load("items");
  let ptCt = pts.getCount();
  await context.sync();
  let ptColl = [];
  let pt:Excel.PivotTable = null;
  for (let i: number = 0; i < ptCt.value; i++) {
    pt = pts[i].load("getDataSourceString");
    ptColl.push(pt);
  }
  await context.sync();

  for (let i: number = 0; i < ptColl.length; i++) {
    console.log(ptColl[i].getDataSourceString());
  }
  return foundPivots;
}

WHat is wrong with my code?

I've crosss-posted this question here too: https://techcommunity.microsoft.com/t5/developer/getting-the-sourcestring-of-all-pivot-tables-in-a-workbook/m-p/3661286#M1023 — jkpieterse, Oct 25 '22 at 14:01

Jakob Nielsen-MSFT · Accepted Answer · 2022-10-25T18:29:45.237

Try this code instead:

await Excel.run(async(context) => {

  // Get the PivotTables in the workbook
  const pivotTables = context.workbook.pivotTables.load("items");
  await context.sync();

  // Get the data sources for each PivotTable
  const dataSources = [];
  pivotTables.items.forEach((pivotTable) => {
    dataSources.push(
      pivotTable.getDataSourceString()
    );
  });
  await context.sync();

  dataSources.forEach((dataSource) => {
    console.log(dataSource.value);
  });
});

Here is the code in a Script Lab snippet

Getting the sourceString of all pivot tables in a workbook

1 Answers1