Forum Discussion

nicolaspavie's avatar
nicolaspavie
Copper Contributor
Jul 23, 2024

Excel scripting in JS : enhance getRange adress format resolution

Hi !

 

Just stumbled over a thing that I think could be interesting to be added in the Excel Javascript API, especially in the ranges' selection with addresses.

 

For a script I'm writing to parse some data in a worksheet in Excel 365 on desktop, I need to select a column starting a certain cell (basically to ignore headers).

 

When testing the Worksheet getRange(adress) method, I realized that I can either select a specific set of cells (like using the adress "A2:A1235") or a complete column or line (i.e. with adress "A:A" or "2:2"), but I cannot select either a column or a row from or up to an offset :

"A2:A" to select column A from row 2 to end of column or "B2:2" to select the line from column B is not valid according to the js console.

 

I don't know if this is just that I'm using the wrong way to format the address for what I want to do (in this case, the API doc needs to be clarified in my opinion), but if it is not originally planned, I think expanding the address format to support offset in both ways (with CL:C and C:CL formats for columns with an offset, and CL:L and L:CL for rows with an offset)  could be a neat feature.

 

Best regards

excel
Office Scripts

1 Reply

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 23, 2024

    nicolaspavie 

    As variant to select from A2 to end of the column

    
function main(workbook: ExcelScript.Workbook) {

    const sheet = workbook.getActiveWorksheet()
    const column = sheet.getRange("A:A")
    const cell = sheet.getRange("A2")

    const range = cell
        .getResizedRange(
            column.getRowCount() - cell.getRowIndex() -1,
            0)

    console.log(range.getRowCount())

}

    In general range could be selected not only by addresses - getUsedRange(), getRangeByIndexes() plus resizings, etc.

    I'd avoid selecting of entire cell or row, most probably it will be negatively affect performance.

Resources