Creates and modifies a PivotTable.

Create and modify (1).EXCEL.yaml

name: Create and modify (1)
description: Creates and modifies a PivotTable.
host: EXCEL
api_set: {}
script:
  content: |
    $("#setup").click(() => tryCatch(setup));

    async function setup() {
      await Excel.run(async (context) => {
        context.workbook.worksheets.getItemOrNullObject("Data").delete();
        const dataSheet = context.workbook.worksheets.add("Data");
        context.workbook.worksheets.getItemOrNullObject("Pivot").delete();
        const pivotSheet = context.workbook.worksheets.add("Pivot");

        const data = [["Date", "Sales"], ["2000-1-1", 2000], ["2000-2-2", 3000], ["2000-3-3", 4000]];

        const range = dataSheet.getRange("A1:B4");
        range.values = data;
        range.format.autofitColumns();

        pivotSheet.activate();
        await context.sync();

        const rangeToAnalyze = context.workbook.worksheets.getItem("Data").getRange("A1:B4");
        const rangeToPlacePivot = context.workbook.worksheets.getItem("Pivot").getRange("A2");
        context.workbook.worksheets.getItem("Pivot").pivotTables.add("Farm Sales", rangeToAnalyze, rangeToPlacePivot);
        await context.sync();

        const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
        const hierarchy = pivotTable.hierarchies.getItem("Date");
        const field = hierarchy.fields.getItem("Date");
        const hierarchy2 = pivotTable.addDateGroup(field, "ByYears");
        pivotTable.rowHierarchies.add(hierarchy2);
        const hierarchy3 = pivotTable.addDateGroup(field, "ByMonths");
        pivotTable.rowHierarchies.add(hierarchy3);
        const hierarchy4 = pivotTable.hierarchies.getItem("Sales");
        pivotTable.dataHierarchies.add(hierarchy4);
        await context.sync();
      });
    }

    /** Default helper for invoking an action and handling errors. */
    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
  language: typescript
template:
  content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create PivotTables and add hierarchies to form rows, columns, and data sets.</p>\n</section>\n\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Setup sample</span>\n    </button>\n</section>"
  language: html
style:
  content: |-
    section.samples {
        margin-top: 20px;
    }
    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/beta/hosted/office.js
  @types/office-js

  [email protected]/dist/css/fabric.min.css
  [email protected]/dist/css/fabric.components.min.css

  [email protected]/client/core.min.js
  @types/core-js

  [email protected]
  @types/[email protected]

Create and modify.EXCEL.yaml

name: Create and modify
description: Creates and modifies a PivotTable.
host: EXCEL
api_set: {}
script:
  content: |
    $("#setup").click(() => tryCatch(setup));

    async function setup() {
      await Excel.run(async (context) => {
        context.workbook.worksheets.getItemOrNullObject("Data").delete();
        const dataSheet = context.workbook.worksheets.add("Data");
        context.workbook.worksheets.getItemOrNullObject("Pivot").delete();
        const pivotSheet = context.workbook.worksheets.add("Pivot");

        const data = [["Date", "Sales"], ["2000-1-1", 2000], ["2000-2-2", 3000], ["2000-3-3", 4000]];

        const range = dataSheet.getRange("A1:B4");
        range.values = data;
        range.format.autofitColumns();

        pivotSheet.activate();
        await context.sync();

        const rangeToAnalyze = context.workbook.worksheets.getItem("Data").getRange("A1:B4");
        const rangeToPlacePivot = context.workbook.worksheets.getItem("Pivot").getRange("A2");
        context.workbook.worksheets.getItem("Pivot").pivotTables.add("Farm Sales", rangeToAnalyze, rangeToPlacePivot);
        await context.sync();

        const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
        const hierarchy = pivotTable.hierarchies.getItem("Date");
        const field = hierarchy.fields.getItem("Date");
        const hierarchy2 = pivotTable.addDateGroup(field, "ByYears");
        pivotTable.rowHierarchies.add(hierarchy2);
        await context.sync();
      });
    }

    /** Default helper for invoking an action and handling errors. */
    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
  language: typescript
template:
  content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create PivotTables and add hierarchies to form rows, columns, and data sets.</p>\n</section>\n\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Setup sample</span>\n    </button>\n</section>"
  language: html
style:
  content: |-
    section.samples {
        margin-top: 20px;
    }
    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/beta/hosted/office.js
  @types/office-js

  [email protected]/dist/css/fabric.min.css
  [email protected]/dist/css/fabric.components.min.css

  [email protected]/client/core.min.js
  @types/core-js

  [email protected]
  @types/[email protected]