Created
June 15, 2022 20:43
-
-
Save AlexJerabek/52effd9072fa3ab4c41db11f12cfda81 to your computer and use it in GitHub Desktop.
Refreshes a PivotTable based on table row additions.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment