Office Script中getValues()与循环返回数组长度不一致问题

问题场景 #

操作的工作表数据按列组织，各列有效行长度不同（如A列到A44行、B列到B14行、O列到O101行），不含表头的有效数据单元格共2269个，当前工作表动态范围为A2:BF21。

代码实现 #

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 []; // 无数据行时返回空数组
    }
    
    // 使用getValues()获取数据
    const kontrolShipKeys: (string )[] = kontrolSheetDataRange.getValues();

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

    // 使用嵌套循环遍历单元格
    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}`)
    // 输出: fn getKontrolDistinctShipKeys kontrolShipKeysArr length: 5800
}

问题现象 #

嵌套for循环返回5800个值（包含空值，符合预期）但计算量过大；getValues()始终返回长度为100的数组，与预期的单元格总数不符。

原因分析 #

1. getValues()返回二维数组结构：该方法返回的是行×列的二维数组，外层数组的长度等于目标范围的行数，内层数组的长度等于目标范围的列数。你看到的kontrolShipKeys.length是外层数组的长度，也就是kontrolSheetDataRange的行数，而非所有单元格的总数。
2. 范围行数的计算逻辑：代码中kontrolSheetDataRange通过getRangeByIndexes(1, 0, kontrolSheetRowCount - 1, kontrolSheetColumnCount)创建，第三个参数是范围的行数。如果kontrolSheetUsedRange.getRowCount()返回101，那么kontrolSheetRowCount - 1就是100，意味着该范围包含100行，所以getValues()返回的外层数组长度为100。
3. 类型赋值错误：你将getValues()的返回值赋值给(string)[]类型变量，这是错误的，正确类型应为string[][]，这也会导致对返回值结构的误解。

正确获取所有单元格值的方式 #

如果需要将二维数组转为一维数组获取所有单元格值，可使用数组的flat()方法：

const kontrolShipKeys = kontrolSheetDataRange.getValues() as string[][];
const allShipKeys = kontrolShipKeys.flat();
console.log(allShipKeys.length); // 此处会输出5800，与循环方式的结果一致

内容的提问来源于stack exchange，提问作者tbalci