Unfortunately, OfficeScripts currently does not support direct interaction with pivot tables in Excel. Therefore, there is no built-in function to check if a pivot table exists with a specific name.
However, you can use a workaround to check if a pivot table with a given name exists by iterating through all the worksheets in the workbook and comparing the names of the pivot tables. Here’s an example of how you can achieve this:
function checkPivotExists(pivotName) { // Get the active workbook var workbook = context.workbook; // Get the worksheets collection var worksheets = workbook.worksheets; // Iterate through all worksheets to find the pivot table with the given name for (var i = 0; i < worksheets.getCount(); i++) { var worksheet = worksheets.getItemAt(i); var pivotTables = worksheet.pivotTables; // Iterate through all pivot tables in the worksheet for (var j = 0; j < pivotTables.getCount(); j++) { var pivotTable = pivotTables.getItemAt(j); // Check if the pivot table name matches the given name if (pivotTable.name === pivotName) { return true; // Pivot table exists } } } return false; // Pivot table does not exist }
In this example, the checkPivotExists function takes a parameter pivotName, which represents the name of the pivot table you want to check for existence. The function iterates through all the worksheets in the workbook and then through all the pivot tables in each worksheet. It compares the pivot table name with the provided pivotName using pivotTable.name === pivotName. If a match is found, the function returns true, indicating that the pivot table exists. If no match is found, the function returns false, indicating that the pivot table does not exist.
To use this function, you can call it with the desired pivot table name as an argument. For example:
checkPivotExists("PivotTable1") .then(function (pivotExists) { console.log("Pivot table exists: " + pivotExists); }) .catch(function (error) { console.log("Error occurred: " + error); });
Please note that this workaround iterates through all the worksheets and pivot tables in the workbook, so it may not be efficient for workbooks with a large number of pivot tables. Additionally, keep in mind that OfficeScripts are only supported in Excel for the web and Excel Online, and they may have limitations compared to other programming environments.