To loop through all formulas in a range using OfficeScripts, you can utilize the formulas property of the Range object. Here’s an example of a function that loops through all formulas in a range:
function loopThroughFormulasInRange(rangeAddress) { // Get the active worksheet var worksheet = context.workbook.worksheets.getActiveWorksheet(); // Get the range object based on the provided range address var range = worksheet.getRange(rangeAddress); // Load the formulas property of the range var formulas = range.formulas; context.load(formulas); // Execute the request and get the formulas return context.sync() .then(function () { var formulaArray = formulas.values; for (var i = 0; i < formulaArray.length; i++) { for (var j = 0; j < formulaArray[i].length; j++) { var formula = formulaArray[i][j]; console.log("Formula at cell (" + (i + 1) + "," + (j + 1) + "): " + formula); } } }) .catch(function (error) { console.log("Error occurred: " + error); }); }
In this example, the loopThroughFormulasInRange function takes a rangeAddress parameter, which represents the address of the range you want to loop through. The function fetches the active worksheet using context.workbook.worksheets.getActiveWorksheet() and gets the range object based on the provided range address using worksheet.getRange(rangeAddress).
The formulas property of the range is loaded using range.formulas. After executing the request and synchronizing the changes with context.sync(), the function retrieves the formulas from the formulas property. The formulas are stored in a two-dimensional array formulaArray, representing the formulas for each cell in the range.
The function then loops through the formulaArray and retrieves each formula value. The formulas are logged to the console along with the corresponding cell coordinates.
To use this function, you can call it as follows:
loopThroughFormulasInRange("A1:C3") .catch(function (error) { console.log("Error occurred: " + error); });
Executing the loopThroughFormulasInRange function with the desired range address will print the formulas in the specified range, along with their cell coordinates, to the console.
Please note that OfficeScripts are only supported in Excel for the web and Excel Online, and they may have limitations compared to other programming environments.