To loop through all non-formula values in a range using OfficeScripts, you can utilize the values property of the Range object. Here’s an example of a function that loops through all non-formula values in a range:
function loopThroughValuesInRange(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 values property of the range var values = range.values; context.load(values); // Execute the request and get the values return context.sync() .then(function () { for (var i = 0; i < values.length; i++) { for (var j = 0; j < values[i].length; j++) { var value = values[i][j]; console.log("Value at cell (" + (i + 1) + "," + (j + 1) + "): " + value); } } }) .catch(function (error) { console.log("Error occurred: " + error); }); }
In this example, the loopThroughValuesInRange 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 values property of the range is loaded using range.values. After executing the request and synchronizing the changes with context.sync(), the function retrieves the values from the values property. The values are stored in a two-dimensional array.
The function then loops through the values array and retrieves each non-formula value. The values are logged to the console along with the corresponding cell coordinates.
To use this function, you can call it as follows:
loopThroughValuesInRange("A1:C3") .catch(function (error) { console.log("Error occurred: " + error); });
Executing the loopThroughValuesInRange function with the desired range address will print the non-formula values 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.