Excel Script: getRange not returning column values

Question

Hi,

I was working on a script that would get the values in a Column based on the column letter . But anytime I run the code below it returns back null. Is this expected or a bug ?

I know there are getColumn which can get it back the indexed number but was trying to do it by letter.

function main(workbook: ExcelScript.Workbook) {

// Get the active cell and worksheet.
// Get the active worksheet
let sheet: ExcelScript.Worksheet \= workbook.getActiveWorksheet();

// Get the range for column A
let range \= sheet.getRange("A:A");


// Get the values for the range
let values \= range.getValues();

console.log(values);
}

P.S. Ignore the \= forum wouldnt post the code without it

Answer 1

Try to limit the area:

let range = sheet.getRange( "A1:A20" );

// or

let range = sheet.getRange( "A:A" ).getUsedRange( true );

Answer 2

Hi

Using "A:A" references the entire column (1,048,576 rows), and getRange("A:A").getValues() in Excel Scripts sometimes behaves unreliably or inefficiently with such a large range.

Please use this:

function main(workbook: ExcelScript.Workbook) {
    // Get the active cell and worksheet.
    let selectedSheet = workbook.getActiveWorksheet();
    let usedRange = selectedSheet.getUsedRange();
    
    let rowCount = usedRange.getRowCount();

    let range = selectedSheet.getRange(`A1:A${rowCount}`);

    let values = range.getValues();
    console.log(values);
}

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Hi there,

Thank you for reaching out!

It seems like the issue may be due to an empty column or a small syntax error. Here's a quick fix:

Try referencing a smaller range like "A1:A10" to ensure data exists.

Make sure you're using = instead of = for assignment.

If you want to get values by column letter, here’s a solution:

typescript

Copy

Edit

let columnLetter = "A";

let columnIndex = columnLetter.charCodeAt(0) - 65;

let range = sheet.getRangeByIndexes(0, columnIndex, sheet.getRowCount(), 1);

Let me know if this resolves the issue or if you need further assistance.

Best,Marie