Pivot Table Samples are broken

[kudlur]

Bug Report

• Host: Excel
• OS: Windows 10
• Browser: N/A
• Environment: PROD

Expected behavior:

In Desktop Version of Excel, running Pivot Table Samples should work

Actual behavior:

The steps where Pivot Table APIs are invoked, the samples crash with the following error
Rich API Error. AccessDenied. You cannot perform the requested Operation.

Please note:, there is absolutely no issue when the samples are run in Web version of Excel

Steps to Reproduce:

1. Run Desktop version of Excel
2. Launch ScriptLab Add-in
3. Click on "Samples" and enter "Pivot" in Search box
4. Click on "Create and Modify" Sample to run it
5. From the "Run" menu, click on "Run In this Pane"
6. Click on "Setup Sample" button
   7.Next click on "Create" button. This results in the AccessDenied error logged to the Console

Failure Logs:

c {name: "RichApi.Error", code: "AccessDenied", traceMessages: Array[0], innerError: null, debugInfo: Object}
name: "RichApi.Error"
code: "AccessDenied"
traceMessages: Array[0]
innerError: null
▶debugInfo: Object
code: "AccessDenied"
message: "You cannot perform the requested operation."
toString: function ()
errorLocation: "PivotTableCollection.add"
statement: "var add=pivotTables.add(...);"
▶surroundingStatements: Array[9]
0: "..."
1: "var worksheet1=worksheets.getItem(...);"
2: "var range1=worksheet1.getRange(...);"
3: "// Instantiate {range1}"
4: "var worksheet2=worksheets.getItem(...);"
5: "var pivotTables=worksheet2.pivotTables;"
6: "// >>>>>"
7: "var add=pivotTables.add(...);"
8: "// <<<<<"
▶fullStatements: Array[1]

[kudlur]

Very strange.. If i called any range functions before refreshing the pivot tables, I do not see any errors.. This find was purely accidental... For ex, the following works.. -

context.workbook.getSelectedRange().format.autofitRows(); // call this first context.workbook.pivotTables.refreshAll(); //now this does not throw error.

[Rick-Kirkham]

I can't reproduce this. The Pivot table snippets all work fine for me. @AlexJerabek Any ideas what might be happening here?

[AlexJerabek]

Hi @kudlur,
I'm encountering the same failures as you when using Excel on Desktop. As a temporary workaround, are you able to run the PivotTable snippets on Excel for the web?

[kudlur]

Hi @AlexJerabek

No problem running PivotTable Snippets (all of them) on Excel for the Web.

Also the snippets work on Desktop version of excel, if i called any range operations that modify the range, right before any PivotTable operations
For Ex:

context.workbook.getSelectedRange().format.autofitRows();
context.workbook.pivotTables.refreshAll()
context.sync();