I am trying to insert a slicer to filter a pivot table using Office Scripts. However, I always receive a run time error when using the below code. The objects passed to the add method seem to exist, as shown in the code output. Am I missing something obvious?

Code output:

• Type
• {3B375914-9B2C-4C2A-94B8-B7A40A8FF92E}
• Line 19: Workbook addSlicer: The argument is invalid or missing or has an incorrect format.

Source code:

    function main(workbook: ExcelScript.Workbook) {
      // 
      const farmPivot = workbook.getPivotTable("PivotTable4");
      const f = farmPivot.getHierarchy("Type").getFields()[0]
      console.log(f.getName())
      console.log(farmPivot.getId())
      const fSlicer: ExcelScript.Slicer = workbook.addSlicer(farmPivot, f)
      fSlicer.selectItems(["Lemon", "Lime"]);
      fSlicer.setLeft(400);
    }

I am trying to insert a slicer to filter a pivot table using Office Scripts. However, I always receive a run time error when using the below code. The objects passed to the add method seem to exist, as shown in the code output. Am I missing something obvious?

Code output:

• Type
• {3B375914-9B2C-4C2A-94B8-B7A40A8FF92E}
• Line 19: Workbook addSlicer: The argument is invalid or missing or has an incorrect format.

Source code:

    function main(workbook: ExcelScript.Workbook) {
      // 
      const farmPivot = workbook.getPivotTable("PivotTable4");
      const f = farmPivot.getHierarchy("Type").getFields()[0]
      console.log(f.getName())
      console.log(farmPivot.getId())
      const fSlicer: ExcelScript.Slicer = workbook.addSlicer(farmPivot, f)
      fSlicer.selectItems(["Lemon", "Lime"]);
      fSlicer.setLeft(400);
    }

• excel
• pivot-table
• office-scripts

1 Answer 1

The third argument (slicerDestination) of addSlicer is required, even though it is listed as optional in the MS documentation.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // let farmPivot = selectedSheet.getPivotTables()[0];
    let farmPivot = workbook.getPivotTable("PivotTable4");
    const f = farmPivot.getHierarchy("Type").getFields()[0]
    console.log(f.getName())
    // console.log(farmPivot.getId())
    const fSlicer: ExcelScript.Slicer = workbook.addSlicer(farmPivot, f, selectedSheet)
    fSlicer.selectItems(["Lemon", "Lime"]);
    fSlicer.setLeft(400);
}

slicerDestination string | ExcelScript.Worksheet

Optional. The worksheet in which the new slicer will be created. It can be a Worksheet object or the name or ID of a worksheet. This parameter can be omitted if the slicer collection is retrieved from a worksheet.

Microsoft documentation:

ExcelScript.Workbook interface