Refreshes a PivotTable based on table row additions.

Refresh.EXCEL.yaml

name: Refresh
description: Refreshes a PivotTable based on table row additions.
host: EXCEL
api_set: {}
script:
  content: |
    $("#setup").click(() => tryCatch(setup));
    $("#add-table-row").click(() => tryCatch(addTableRow));
    $("#refresh-pivottable").click(() => tryCatch(refreshPivotTable));

    async function addTableRow() {
      await Excel.run(async (context) => {
        // Add a row to the PivotTable's source table.
        const dataTable = context.workbook.tables.getItem("DataTable");
        dataTable.rows.add(null, [["G Farms", "Mango", "Organic", 200, 1000]]);
        await context.sync();
      });
    }

    async function refreshPivotTable() {
      await Excel.run(async (context) => {
        // Refresh the "Farm Sales" PivotTable.
        const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
        pivotTable.refresh();
        await context.sync();
      });
    }

    async function setup() {
      await Excel.run(async (context) => {
        context.workbook.worksheets.getItemOrNullObject("Sample").delete();
        const sheet = context.workbook.worksheets.add("Sample");

        const data = [
          ["Farm", "Type", "Classification", "Crates Sold at Farm", "Crates Sold Wholesale"],
          ["A Farms", "Lime", "Organic", 300, 2000],
          ["A Farms", "Lemon", "Organic", 250, 1800],
          ["A Farms", "Orange", "Organic", 200, 2200],
          ["B Farms", "Lime", "Conventional", 80, 1000],
          ["B Farms", "Lemon", "Conventional", 75, 1230],
          ["B Farms", "Orange", "Conventional", 25, 800],
          ["B Farms", "Orange", "Organic", 20, 500],
          ["B Farms", "Lemon", "Organic", 10, 770],
          ["B Farms", "Kiwi", "Conventional", 30, 300],
          ["B Farms", "Lime", "Organic", 50, 400],
          ["C Farms", "Apple", "Organic", 275, 220],
          ["C Farms", "Kiwi", "Organic", 200, 120],
          ["D Farms", "Apple", "Conventional", 100, 3000],
          ["D Farms", "Apple", "Organic", 80, 2800],
          ["E Farms", "Lime", "Conventional", 160, 2700],
          ["E Farms", "Orange", "Conventional", 180, 2000],
          ["E Farms", "Apple", "Conventional", 245, 2200],
          ["E Farms", "Kiwi", "Conventional", 200, 1500],
          ["F Farms", "Kiwi", "Organic", 100, 150],
          ["F Farms", "Lemon", "Conventional", 150, 270]
        ];

        const range = sheet.getRange("A1:E21");
        range.values = data;
        const table = sheet.tables.add(range, true);
        table.name = "DataTable";
        range.format.autofitColumns();

        const rangeToPlacePivot = sheet.getRange("G1");
        const pivotTable = sheet.pivotTables.add("Farm Sales", table, rangeToPlacePivot);

        pivotTable.rowHierarchies.add(pivotTable.hierarchies.getItem("Farm"));
        pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold at Farm"));
        pivotTable.dataHierarchies.add(pivotTable.hierarchies.getItem("Crates Sold Wholesale"));

        sheet.activate();
        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 refresh a PivotTable.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Setup</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Add data and PivotTable</span>\n    </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<p>Add a row to the table, then refresh the PivotTable. Note that the PivotTable doesn't automatically refresh.</p>\n\t<button id=\"add-table-row\" class=\"ms-Button\">\n      <span class=\"ms-Button-label\">Add table row</span>\n    </button>\n\t</p>\n\t<button id=\"refresh-pivottable\" class=\"ms-Button\">\n      <span class=\"ms-Button-label\">Refresh PivotTable</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/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1