Office Script: different array length results between getValues() and looping

I'm stuck with the logic in Office Scripts. Following function is running on a worksheet

in a dynamic range, currently "A2:BF21"
that has the data is organized in columns, with different row lengths - e.g. column A has rows up to A44, column B up to B14, but column O up to O101.
that has 2269 cells that contain valid data (excluding header row).

function getKontrolDistinctShipKeys(kontrolSheet: ExcelScript.Worksheet): string[] {

    const kontrolSheetUsedRange = kontrolSheet.getUsedRange();
    const kontrolSheetRowCount = kontrolSheetUsedRange.getRowCount();
    const kontrolSheetColumnCount = kontrolSheetUsedRange.getColumnCount();

    const kontrolSheetDataRange = kontrolSheet.getRangeByIndexes(1, 0, kontrolSheetRowCount - 1, kontrolSheetColumnCount)
    if (kontrolSheetRowCount <= 1) {
        return []; // Return empty array if no data rows are present
    }
    
    // getValues()  
    
    const kontrolShipKeys: (string )[] = kontrolSheetDataRange.getValues();

    console.log(`fn getKontrolDistinctShipKeys kontrolShipKeys Length: ${kontrolShipKeys.length}`)
    // output: fn getKontrolDistinctShipKeys kontrolShipKeys Length: 100
    

    // for loop
    let kontrolShipKeysArr: (string)[] = []; 
    for (let colCount=1; colCount <= kontrolSheetColumnCount; colCount++){
            for (let rowCount = 1; rowCount < kontrolSheetRowCount; rowCount++){
                let kontrolShipKey = kontrolSheetDataRange.getCell(rowCount, colCount).getValue();
                kontrolShipKeysArr.push(kontrolShipKey)
    }
    }
        console.log(`fn getKontrolDistinctShipKeys kontrolShipKeysArr length: ${kontrolShipKeysArr.length}`)
    //output: fn getKontrolDistinctShipKeys kontrolShipKeysArr length: 5800
}

With the for loop, returning 5800 values is fine because it includes the nulls in the range. However, it is very compute intensive, and according to Microsoft's documentation getValues() should get the job done and return the raw values in the range.

I'm not able to wrap my head around this different behavior. Even if I manipulate the Worksheet and add/remove values to/from the cells, the getValues() statement always return 100 items.

Any ideas why getValues() gives such a result?

Thank you!

Cheers

Solution

The getValues() function returns a 2D array. This revised script demonstrates how to verify the item count within it.
Inserting a cell in row 101 or above won't affect the used range. The output (100) remains unchanged.
The script returns 5900 because the used range detects two bordered cells in column BF (the 59th column).


function main(workbook: ExcelScript.Workbook) {
    let selectedCell = workbook.getActiveCell();
    let kontrolSheet = workbook.getActiveWorksheet();
    const kontrolSheetUsedRange = kontrolSheet.getUsedRange();
    const kontrolSheetRowCount = kontrolSheetUsedRange.getRowCount();
    const kontrolSheetColumnCount = kontrolSheetUsedRange.getColumnCount();

    const kontrolSheetDataRange = kontrolSheet.getRangeByIndexes(1, 0, kontrolSheetRowCount - 1, kontrolSheetColumnCount)

    const kontrolShipKeys: (string)[] = kontrolSheetDataRange.getValues();
    // getValues returns a two dimension array
    console.log(kontrolShipKeys)
    
    // the output is same as the quantity of rows in kontrolSheetDataRange
    console.log(`fn getKontrolDistinctShipKeys kontrolShipKeys Length: ${kontrolShipKeys.length}`);

    // the quantity of cells in kontrolSheetDataRange
    console.log(`The quantity of cells: ${kontrolShipKeys.length * kontrolShipKeys[0].length}`);

}


function main(workbook: ExcelScript.Workbook) {
    let testArray = [[1,2],[3,4],[5,6]]
    
    console.log(testArray)

    console.log(`${testArray}`)
}

The output in console

Key Difference:

The first console.log preserves the array structure.
The second console.log flattens the array into a string (comma-separated list). This statement uses a template literal (${...}), which forces the array testArray to be converted to a string.