o find the first row and column with values in a sheet using OfficeScripts, you can utilize the UsedRange property of the Worksheet object. Here’s an example of a function that retrieves the first row and column with values in a sheet:
function findFirstRowAndColumnWithValues() { // Get the active worksheet var worksheet = context.workbook.worksheets.getActiveWorksheet(); // Load the UsedRange property of the worksheet var usedRange = worksheet.getUsedRange(); context.load(usedRange, "rowIndex", "columnIndex"); // Execute the request and get the used range return context.sync() .then(function () { var firstRow = usedRange.rowIndex; var firstColumn = usedRange.columnIndex; console.log("First Row with Values: " + firstRow); console.log("First Column with Values: " + firstColumn); }) .catch(function (error) { console.log("Error occurred: " + error); }); }
In this example, the findFirstRowAndColumnWithValues function fetches the active worksheet using context.workbook.worksheets.getActiveWorksheet(). The UsedRange property of the worksheet is then loaded using worksheet.getUsedRange(), and the rowIndex and columnIndex properties of the used range are specified to be loaded.
After executing the request and synchronizing the changes with context.sync(), the function retrieves the first row and column with values from the usedRange object. The values are logged to the console.
To use this function, you can call it as follows:
findFirstRowAndColumnWithValues() .catch(function (error) { console.log("Error occurred: " + error); });
Executing the findFirstRowAndColumnWithValues function will print the first row and column with values 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.