Improve the performance of your Office Scripts
The purpose of Office Scripts is to automate commonly performed series of tasks to save you time. A slow script can feel like it doesn't speed up your workflow. Most of the time, your script will be perfectly fine and run as expected. However, there are a few, avoidable scenarios that can affect performance.
Reduce the number of read or write calls
The most common reason for a slow script is excessive communication with the workbook. This is particularly noticeable when using Excel on the web. At certain times, your script synchronizes its local data with that of the workbook. This means that any write operations (such as workbook.addWorksheet()) are only applied to the workbook when this behind-the-scenes synchronization happens. Likewise, any read operations (such as myRange.getValues()) only get data from the workbook for the script at those times. In either case, the script fetches information before it acts on the data. For example, the following code will accurately log the number of rows in the used range.
let usedRange = workbook.getActiveWorksheet().getUsedRange();
let rowCount = usedRange.getRowCount();
// The script will read the range and row count from
// the workbook before logging the information.
console.log(rowCount);
Office Scripts APIs ensure any data in the workbook or script is accurate and up-to-date when necessary. You don't need to worry about these synchronizations for your script to run correctly. However, an awareness of this script-to-cloud communication can help you avoid unneeded network calls.
Read workbook data outside of a loop
Any method that gets data from the workbook can trigger a network call. Rather than repeatedly making the same call, you should save data locally whenever possible. This is especially true when dealing with loops.
Consider a script to get the count of negative numbers in the used range of a worksheet. The script needs to iterate over every cell in the used range. To do that, it needs the range, the number of rows, and the number of columns. You should store those as local variables before starting the loop. Otherwise, each iteration of the loop will force a return to the workbook.
/**
* This script provides the count of negative numbers that are present
* in the used range of the current worksheet.
*/
function main(workbook: ExcelScript.Workbook) {
// Get the working range.
let usedRange = workbook.getActiveWorksheet().getUsedRange();
// Save the values locally to avoid repeatedly asking the workbook.
let usedRangeValues = usedRange.getValues();
// Start the negative number counter.
let negativeCount = 0;
// Iterate over the entire range looking for negative numbers.
for (let i = 0; i < usedRangeValues.length; i++) {
for (let j = 0; j < usedRangeValues[i].length; j++) {
if (usedRangeValues[i][j] < 0) {
negativeCount++;
}
}
}
// Log the negative number count to the console.
console.log(negativeCount);
}
Note
As an experiment, try replacing usedRangeValues in the loop with usedRange.getValues(). You may notice the script takes considerably longer to run when dealing with large ranges.
Avoid using try...catch blocks in or surrounding loops
We don't recommend using try...catch statements either in loops or surrounding loops. This is for the same reason you should avoid reading data in a loop: each iteration forces the script to synchronize with the workbook to make sure no error has been thrown. Most errors can be avoided by checking objects returned from the workbook. For example, the following script checks that the table returned by the workbook exists before trying to add a row.
/**
* This script adds a row to "MyTable", if that table is present.
*/
function main(workbook: ExcelScript.Workbook) {
let table = workbook.getTable("MyTable");
// Check if the table exists.
if (table) {
// Add the row.
table.addRow(-1, ["2012", "Yes", "Maybe"]);
} else {
// Report the missing table.
console.log("MyTable not found.");
}
}
Remove unnecessary console.log statements
Console logging is a vital tool for debugging your scripts. However, it does force the script to synchronize with the workbook to ensure the logged information is up-to-date. Consider removing unnecessary logging statements (such as those used for testing) before sharing your script. This typically won't cause a noticeable performance issue, unless the console.log() statement is in a loop.
Pause calculations while the scripts runs
If your script changes a lot of values, it may trigger excessive recalculations. Control the Excel calculation engine by setting the calculation mode to "manual" while your script runs. Use Application.setCalculation to switch Excel to manually recalculate formulas. Be sure to return the workbook to the original calculation mode when finished.
The following sample shows how to change the calculation mode. It also demonstrates how to manually recalculate the workbook with Application.calculate.
/**
* This script adjusts the calculation mode of the workbook and makes a manual recalculation.
* Wrap the CalculationMode changes around code that repeatedly updates values.
*/
function main(workbook: ExcelScript.Workbook) {
const application = workbook.getApplication();
// Turn off automatic calculations during the script.
application.setCalculationMode(ExcelScript.CalculationMode.manual);
// ...
// Perform a manual recalculation of the workbook.
application.calculate(ExcelScript.CalculationType.fullRebuild);
// ...
// Resume automatic calculations after the script finishes.
application.setCalculationMode(ExcelScript.CalculationMode.automatic);
}
Case-by-case help
As the Office Scripts platform expands to work with Power Automate, Adaptive Cards, and other cross-product features, the details of the script-workbook communication become more intricate. If you need help making your script run faster, please reach out through Microsoft Q&A. Be sure to tag your question with "office-scripts-dev" so experts can find it and help.