I have sheet and data, but I don't know how many filled columns will be in sheet. I need data from 3 to 5 row and from columns A to (dynamic, for each sheet could be different).
If I have data in Table it is easy, but I don't know how it looks like in Range.
async function getData() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const expensesTable = sheet.tables.getItem("Table");
const headerRange = expensesTable.getHeaderRowRange().load("values");
const bodyRange = expensesTable.getDataBodyRange().load("values");
await context.sync();
console.log(JSON.stringify(headerRange, null, 4))
console.log(JSON.stringify(bodyRange.values, null, 1));
});
}
Note: Used Range is 0 index from the range itself. So if data starts in Cell A1 then Col A = 0 Row 1 = 0. If data starts in B1 then data in B1 would be 0,0.
async function getData() {
await Excel.run(async (context) => {
var ws = context.workbook.worksheets.getActiveWorksheet();
var usedrng = ws.getUsedRange(true)
usedrng.load("values")
await context.sync()
var val = usedrng.values[0][0]
console.log('val:' + val)
});
}
If you want to find the most right non-empty column, you may use Range.getExtendedRange with right direction. It returns a range object that includes the current range and up to the edge of the range, based on the provided direction. This matches the Ctrl+Shift+Arrow key behavior in the Excel on Windows UI.