Office script does not return array

I'm working on an Office Script and got stuck where one function returns an array correctly, whereas the other function does not return the array.

Below is my complete code.

• cleanSheet function removes information from the "Document Data Audit" worksheet to provide a "clean table" to the user to work with.
• getShipKeys function gets the shipKeys from the column 0,
• getKontrolDistinctShipKeys function gets data from the range in "Kontrol" worksheet, saves the values in an array, creates a set from the array to remove duplicate elements, and then convert the set back to an array to pass it to function main() to be used in the next function call (not implemented yet).

I have console.log statements to check if the values are passed over to/from the functions correctly.

The point that I'm stuck is:

• function getShipKeys creates and returns the shipKeys array correctly.
• function getKontrolDistinctShipKeys creates the array correctly (length is 1003), but the array that it returns to the function main has a length of 0. The code console.log(`fn main kontrolDistinctShipKeys: ${kontrolSheetDistinctShipKeys.length}`) under function main which checks the length of the array has the value of 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;
}

What is the point that I'm missing here?

Many thanks for helping with my sanity!

Tolga

In your main function neither of these two functions are having their return values checked:

    getShipKeys(usedRange, shipKeys);
    console.log(`fn main Shipkeys: ${shipKeys.length}`);
    getKontrolDistinctShipKeys(usedRange, kontrolSheet)

getShipKeys is working because shipkeys is being passed in through the function getShipKeys as a parameter with an object reference, and is being modified directly.

Maybe consider changing this to something like the following:

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 kontrolSheetDistinctShipKeys: string [] = [];

    // declare and assign from the function output
    let shipKeys: string[] = getShipKeys(usedRange);
    let kontrolShipKeys: string [] = getKontrolDistinctShipKeys(usedRange, kontrolSheet);

    console.log(`fn main Shipkeys: ${shipKeys.length}`);
    console.log(`fn main kontrolDistinctShipKeys: ${kontrolSheetDistinctShipKeys.length}`)
    compareShipKeys(shipKeys, kontrolSheetDistinctShipKeys)
}

and change getShipKeys to :

function getShipKeys(usedRange: ExcelScript.Range): string[]{
    let keys : 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()
        keys.push(shipKey);
    }
    return keys;
}

note the :

    // declare and assign from the function output

Hope that helps to get you on the right track