Linking a slicer to multiple pivot tables using office scripts

Question

I'm trying to add a slicer to connect to multiple pivot tables in Excel using office scripts. It seems like the office scripts can only connect 1 slicer to 1 pivot table. The recording action does not seem to be able to record the connectivity action in the pivot table slicer settings.

  let newSlicer = workbook.addSlicer(newPivotTable, newPivotTable.getHierarchy("Overdue").getFields()[0], selectedSheet);

The above script does not seem to be able to pass in more than one pivot table. Anyone got a solution to this? Much appreciation.

Answer 1

I don't think linking multiple PivotTables to a slicer is currently supported. But there may be a workaround. You can run the Office Scripts code below. You will have to update the variables with the names for your own PivotTable, field for the slicer, etc.:

function main(workbook:ExcelScript.Workbook){
  let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();
  let slicer1: ExcelScript.Slicer = getOrAddSlicer("PivotTable1","Col1",workbook);
  let slicer2: ExcelScript.Slicer = getOrAddSlicer("PivotTable1", "Col2", workbook);
}

function getOrAddSlicer(ptName:string,ptRowHierarchyName: string, workbook:ExcelScript.Workbook): ExcelScript.Slicer {
  let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();
  let pt: ExcelScript.PivotTable = sh.getPivotTable(ptName);
  let pf: ExcelScript.PivotField = pt.getRowHierarchy(ptRowHierarchyName).getPivotField(ptRowHierarchyName);
  let slicer: ExcelScript.Slicer = workbook.getSlicer(ptRowHierarchyName);
  if (slicer === undefined) {
    slicer = workbook.addSlicer(pt, pf, sh);
  }
  return slicer;
}

The getOrAddSlicer function will add a slicer to the active worksheet. Or select a slicer on the active sheet that's linked to a specific field if it's previously been added. After you've added all the slicers, you can copy and paste the PivotTable the slicers are linked to. After you've copied and pasted the PT, both PivotTables should also be linked to all of the slicers.