Get the last cell with data for a given column

Question

In Excel Office Scripts, I need to get a range starting from cell B2 all the way down to the last cell in column 2 that contains data, contiguously.

That is the equivalent, of selecting B2 and clicking Ctrl + Down.

What kind of method does that?

This is my code, and I want to replace .getRange("B2:B4") by something automatic.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Set font bold to true for range B2:B4 on selectedSheet
    selectedSheet.getRange("B2:B4").getFormat().getFont().setBold(true);
}

Answer 1

Please try.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    
    // Get continuous data range
    let dataRange = selectedSheet.getRange("B2").getExtendedRange(ExcelScript.KeyboardDirection.down);
    console.log(dataRange.getAddress());
    dataRange.getFormat().getFont().setBold(true);

    // Get non-continuous data range
    let lastCell = selectedSheet.getRange("B:B").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex();
    let dataRange2 = selectedSheet.getRangeByIndexes(1,1,lastCell,1)
    console.log(dataRange2.getAddress());
    dataRange2.getFormat().getFont().setBold(true);
}

Microsoft documentation:

getExtendedRange(direction, activeCell)

getRangeEdge(direction, activeCell)