Office Script函数返回数组异常：getKontrolDistinctShipKeys返回值未接收

开发Office Script时遇到数组传递问题：一个函数能正确返回数组，但另一个函数返回的数组无法传递到main函数中。

代码功能说明 #

• cleanSheet函数：清除"Document Data Audit"工作表信息，提供干净表格供用户使用。
• getShipKeys函数：从第0列获取shipKeys数组。
• getKontrolDistinctShipKeys函数：从"Kontrol"工作表区域获取数据，存入数组后通过Set去重，再转回数组返回给main函数（后续调用逻辑暂未实现）。

现象验证 #

通过console.log验证发现：

• getShipKeys函数能正确创建并返回shipKeys数组。
• getKontrolDistinctShipKeys函数内部生成的数组长度为1003，但main函数中接收的kontrolSheetDistinctShipKeys数组长度为0。

完整代码 #

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getWorksheet("Origin Hub - Document Data Audi");
    let kontrolSheet = workbook.getWorksheet("kontrol")
    const usedRange = selectedSheet.getUsedRange();
  //cleanSheet(selectedSheet)
    //let originTable = selectedSheet.addTable(usedRange, true);
    //originTable.setName("originTable");
    let shipKeys: string[] = [];
    let kontrolShipKeys: string [] = [];
    let kontrolSheetDistinctShipKeys: string [] = [];
    getShipKeys(usedRange, shipKeys);
    console.log(`fn main Shipkeys: ${shipKeys.length}`);
    getKontrolDistinctShipKeys(usedRange, kontrolSheet)
    console.log(`fn main kontrolDistinctShipKeys: ${kontrolSheetDistinctShipKeys.length}`)
    compareShipKeys(shipKeys, kontrolSheetDistinctShipKeys)
}

function cleanSheet(selectedSheet: ExcelScript.Worksheet){

    selectedSheet.getRange("1:4").delete(ExcelScript.DeleteShiftDirection.up);
    const columnDeletes = ["Ship Mode", "Item No", "Volume", "Weight", "Commercial Invoice No"];
    
    let tempUsedRange = selectedSheet.getUsedRange();
    let tempUsedRangeTable = selectedSheet.addTable(tempUsedRange, true)
    
    columnDeletes.forEach(columnName => {
        tempUsedRangeTable.getColumnByName(columnName).delete();
    });

    tempUsedRangeTable.getRange().getFormat().autofitColumns();
    tempUsedRangeTable.getRange().getFormat().autofitRows();
    tempUsedRangeTable.getAutoFilter().remove();
    tempUsedRangeTable.convertToRange();

}

function getShipKeys(usedRange: ExcelScript.Range, shipKeys: Array<string>): string[]{

    let firstRow = usedRange.getRowIndex();
    let firstDataRow = firstRow + 1
    let firstColumn = usedRange.getColumnIndex();
    let lastRow = usedRange.getLastRow();
    let lastDataRow = lastRow.getRowIndex();

    for (let dataRow = 1; dataRow <= lastDataRow; dataRow++){
        let shipKey = usedRange.getCell(dataRow, 0).getValue().toString()
        shipKeys.push(shipKey);
    }
    return shipKeys;
}

function getKontrolDistinctShipKeys(usedRange: ExcelScript.Range, kontrolSheet: ExcelScript.Worksheet): string[]{
    
    let kontrolSheetUsedRange = kontrolSheet.getUsedRange();
    let kontrolSheetUsedRangeRowCount = kontrolSheetUsedRange.getRowCount();
    let kontrolSheetUsedRangeColumnCount = kontrolSheetUsedRange.getColumnCount();
    let kontrolSheetDataRange = kontrolSheet.getRangeByIndexes(1, 0, kontrolSheetUsedRangeRowCount, kontrolSheetUsedRangeColumnCount)

    let kontrolSheetShipKeyValues = kontrolSheetDataRange.getValues();
    kontrolSheetShipKeyValues = kontrolSheetShipKeyValues.concat.apply([], kontrolSheetShipKeyValues);
    let kontrolDistinctShipKeysSet = new Set(kontrolSheetShipKeyValues);
    let kontrolSheetDistinctShipKeys = Array.from(kontrolDistinctShipKeysSet)

    console.log(`fn getKontrolDistinctShipKeys kontrolSheetDistinctShipKeys: ${kontrolSheetDistinctShipKeys.length}`);

    return kontrolSheetDistinctShipKeys;
}

核心问题是main函数未接收getKontrolDistinctShipKeys的返回值。

调用getKontrolDistinctShipKeys(usedRange, kontrolSheet)时，仅执行了函数但未将返回的数组赋值给kontrolSheetDistinctShipKeys变量，导致该变量始终为初始空数组（长度0）。而getShipKeys能正常工作，是因为它采用了数组引用传递：将空数组shipKeys传入函数后，函数内部直接对原数组执行push操作，修改了数组内容。

修改后的main函数代码 #

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getWorksheet("Origin Hub - Document Data Audi");
    let kontrolSheet = workbook.getWorksheet("kontrol")
    const usedRange = selectedSheet.getUsedRange();
  //cleanSheet(selectedSheet)
    //let originTable = selectedSheet.addTable(usedRange, true);
    //originTable.setName("originTable");
    let shipKeys: string[] = [];
    let kontrolShipKeys: string [] = [];
    // 接收函数返回的数组
    let kontrolSheetDistinctShipKeys: string [] = getKontrolDistinctShipKeys(usedRange, kontrolSheet);
    getShipKeys(usedRange, shipKeys);
    console.log(`fn main Shipkeys: ${shipKeys.length}`);
    console.log(`fn main kontrolDistinctShipKeys: ${kontrolSheetDistinctShipKeys.length}`)
    compareShipKeys(shipKeys, kontrolSheetDistinctShipKeys)
}

额外优化建议 #

为保持代码风格一致，可将getShipKeys改为返回数组的方式，避免依赖引用传递：

function getShipKeys(usedRange: ExcelScript.Range): string[]{
    const shipKeys: string[] = [];
    let lastRow = usedRange.getLastRow();
    let lastDataRow = lastRow.getRowIndex();

    for (let dataRow = 1; dataRow <= lastDataRow; dataRow++){
        let shipKey = usedRange.getCell(dataRow, 0).getValue().toString()
        shipKeys.push(shipKey);
    }
    return shipKeys;
}

在main函数中调用时改为：

let shipKeys: string[] = getShipKeys(usedRange);

这种方式更符合函数式编程习惯，也能避免引用传递带来的潜在副作用。

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