Office JS - Excel, Add pivot chart

Question

How can I add a PivotChart with office.js?

I can see the pivotOptions property for chart (https://learn.microsoft.com/en-us/javascript/api/excel/excel.chart?view=excel-js-preview#excel-excel-chart-pivotoptions-member) but I couldn't find a clear example of Pivot-Chart creation similar to the other docs pages (i.e. https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-pivottables)

Is it possible? Can you please show how?

Answer 1

Try it:

Excel.run(function (context) {
    // Get the active worksheet
    var sheet = context.workbook.worksheets.getActiveWorksheet();
    // Define the data range for the PivotTable
    var dataRange = sheet.getRange("A1:C10");
    // Create a PivotTable based on the data range
    var pivotTable = sheet.pivotTables.add("A15", dataRange, true);
    // Define the data range for the PivotChart
    var chartRange = sheet.getRange("E1:F10");
    // Create a PivotChart based on the PivotTable and data range
    var pivotChart = sheet.charts.add("A30", Excel.ChartType.columnClustered, pivotTable);
    pivotChart.setSourceData(chartRange);
    // Synchronize the changes with the workbook
    return context.sync();
}).catch(function (error) {
    console.log(error);
});

Answer 2

I got the same problem, and Jinghui's answer is not working. What is working is

    const dataAddress = "C2:D10"; // where your data is stored
    const pivotTableAddress = "A2"; // where you want your pivot to be created
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    const pivotTable = sheet.pivotTables.add(
        'Pivot Table name',
        dataAddress,
        pivotTableAddress
    );
    
    // in my case, i want asset as row & amountInMyCurrency as data
    pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem('asset'));
    pivotTable.dataHierarchies.add(
        pivotTable.hierarchies.getItem('amountInMyCurrency')
    );
    await sheet.context.sync();
      
    // then we add the chart like any other chart, Excel somehow pickup the pivotTable with just adding any cell of this pivot table
    const pivotChart = sheet.charts.add(
      Excel.ChartType.columnClustered,
      sheet.getRange(pivotTableAddress)
    );
    await sheet.context.sync();