How to get last column in specific range in Office Script?

Question

I have values in range A1:E10 and I'd like to get the next empty column in that range (excluding headers). In this case last column used is "C" and next empty column would be "D".

My curren code only gets the last column index in all used range, but I don`t know how to get the last column letter in specific range.

function main(workbook: ExcelScript.Workbook) {

    const mysheet = workbook.getWorksheet("Sheet1");
    const usedRange = mysheet.getUsedRange(true);
    const LastCol = usedRange.getLastColumn().getColumnIndex();

    console.log(Last column: " + LastCol);
}

Michal · Accepted Answer · 2025-04-16 22:41:26Z

1

There probably are better ways but the one I can think about is just to iterate over each column backwards:

function main(workbook: ExcelScript.Workbook) {
    const mysheet = workbook.getWorksheet("Sheet1");
    const range = mysheet.getRange("A2:E10");
    const columns = range.getColumnCount();

    for (let i = 0; i < columns; i++) {
        if (range.getColumn(i).getValues().every(row => row[0] === null || row[0] === "")) {
            console.log("Next empty column: " + String.fromCharCode(65 + i));
            return;
        }
    }

    console.log("No empty column found in the specified range.");
}

answered Apr 16 at 22:41

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rasec Malkic Apr 16 at 23:03

Thanks for the help. In my actual data the range goes from E6:BX53 but for example if the column index of last colum is 39, the convertion of String.fromCharCode(65 + 39) is givinh me column H and should be column AM

Skin · Accepted Answer · 2025-04-17 01:17:59Z

1

I know it's already answered but like the answer says, there are better ways ...

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let rangeToGetColumnLetterFrom = selectedSheet.getRange("A2:E10").getUsedRange();

    let lastColumnLetterInRangeAddress = rangeToGetColumnLetterFrom.getLastCell().getEntireColumn().getOffsetRange(0,1).getAddress();
    let lastColumnLetterInRange = lastColumnLetterInRangeAddress.split('!')[1].split(':')[0]
    
    console.log(lastColumnLetterInRange);
}

... you can take the above approach which is a lot more direct.

You just need to define the range you want to work with.

edited Apr 17 at 1:17

answered Apr 17 at 1:10

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to get last column in specific range in Office Script?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related