Cacher is the code snippet organizer for pro developers

name: Refresh pivot table
description: Refresh pivot table
author: rramona2
host: EXCEL
api_set: {}
script:
    content: |+
        $("#setup").click(setup);
        $("#refresh-pivot-table").click(refreshPivotTable);

        async function refreshPivotTable() {
            await Excel.run(async (context) => {
                try {
                    const sheet = context.workbook.worksheets.getItem("Sample");

                    // First, force a change in the table data by setting the value of Amount in the third row to $500
                    const expensesTable = sheet.tables.getItem("ExpensesTable");

                    const range = expensesTable.columns.getItem("Amount").getDataBodyRange().getRow(2).values = [["$500"]];

                    await sheet.context.sync();

                    // Now, refresh the pivot table. This should pick up our changes made with code as well as any changes made in the UI
                    const expensesPivot = sheet.pivotTables.getItem("PivotTable1");

                    expensesPivot.refresh();

                    await context.sync();
                }
                catch (error) {
                    OfficeHelpers.UI.notify(error);
                    OfficeHelpers.Utilities.log(error);
                }
            })
        }

        /** Create a new table with some sample data */
        async function setup() {
            try {
                await Excel.run(async (context) => {

                    await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sample");

                    const sheet = context.workbook.worksheets.getItem("Sample");

                    const expensesTable = sheet.tables.add('A1:D1', true /*hasHeaders*/);
                     expensesTable.name = "ExpensesTable";

                    expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];

                    expensesTable.rows.add(null /*add at the end*/, [
                    ["1/1/2017", "The Phone Company", "Communications", "$120"],
                    ["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
                    ["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
                    ["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
                    ["1/11/2017", "Bellows College", "Education", "$350"],
                    ["1/15/2017", "Trey Research", "Other", "$135"],
                    ["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
                ]);

                if (Office.context.requirements.isSetSupported("ExcelApi", 1.2)) {
                    sheet.getUsedRange().format.autofitColumns();
                    sheet.getUsedRange().format.autofitRows();
                }

                sheet.activate();

                await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

    language: typescript
template:
    content: |+
        <section class="ms-font-m">
            <p>This sample shows how to refresh a PivotTable using the Excel JavaScript API.  Note that this API requires the Excel 1.3 requirement set.</p>
        </section>

        <section class="setup ms-font-m">
            <h3>Set up</h3>
            <button id="setup" class="ms-Button">
                <span class="ms-Button-label">Create table</span>
            </button>
            <p>Next, click inside the table and insert a PivotTable using the Insert menu in Excel, specifying the following options.<p>
            <ol>
                <li>Choose Existing sheet.</li>
                <li>Specify A15 as the location.</li>
                <li>Select the Amount and Category columns.</li>
                <li>Make sure the name of the PivotTable in the UI matches with the name specified in code.</li>
            </ol>
        </section>

        <section class="samples ms-font-m">
            <h3>Try it out</h3>
            <button id="refresh-pivot-table" class="ms-Button">
                <span class="ms-Button-label">Refresh PivotTable</span>
            </button>
        </section>


    language: html
style:
    content: "section.samples {\r\n    margin-top: 20px;\r\n}\r\n\r\nsection.samples .ms-Button, section.setup .ms-Button {\r\n    display: block;\r\n    margin-bottom: 5px;\r\n    margin-left: 20px;\r\n    min-width: 80px;\r\n}\r\n"
    language: css
libraries: |
    // Office.js
    https://appsforoffice.microsoft.com/lib/1/hosted/office.js

    // CSS Libraries
    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

    // NPM libraries
    core-js@2.4.1/client/core.min.js
    @microsoft/office-js-helpers@0.7.1/dist/office.helpers.min.js
    jquery@3.1.1

    // IntelliSense: @types/library or node_modules paths or URL to d.ts files
    @types/office-js
    @types/core-js
    @microsoft/office-js-helpers@0.7.1/dist/office.helpers.d.ts
    @types/jquery