0

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

Improve this question

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks a lot for the explanation Nick. Just one question though: in the line let shipKeys: string[] = getShipKeys(usedRange, shipKeys); - how can we assign shipKeys[] as this would mean that we are trying to use the variable before we declare it?
ah, apologies - you don't need to pass it in, I've edited the answer to reflect this
-2

Make sure you're using the correct return statement and formatting for your Office Script. Return [value1, value2]; is the only way to explicitly return arrays, not just log them or declare them.

Improve this answer

1 Comment

Frankly I did not understand what you mean here and how it relates to my question. The question that I have is why one return works and the other does not. How does formatting, correct return statements and logging/declaring them relate to my question? Can you present your answer with code?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.