Office script writing last element of array to entire range

Question

I am trying to write the converted values of a selected range to the adjacent column of cells. I am able to get the values written to the cells, however, it only prints out the value of the last item in the range to all of the cells. I've included a screenshot of what the output is, along with my code.

I have read through the documentation and tried several ways to do this, but keep getting the same result and so I am stumped. Any assistance would be greatly appreciated!

Here is the current output:

function main(workbook: ExcelScript.Workbook) {
    // Get the currently highlighted range in the workbook.
    let range = workbook.getSelectedRange();
    let rangeAddress = range.getAddress();
    
    // Get the that range's value
    let cellInputs = range.getValues();
    let timeInSecondsArr: Array<number>;
    timeInSecondsArr = [];

    // Get cell values and add to array
    cellInputs.forEach((columnItem, columnIndex) => {
        cellInputs[columnIndex].forEach((rowItem, rowIndex) => {
            timeInSecondsArr.push(Number(rowItem));
        });
    });

    // Initialize array to add converted values to.
    let convertedValuesArr: Array<string>;
    convertedValuesArr = [];

    function convertValues() {
        let result = "";
        
        // convert the values.
        for (let i = 0; i < timeInSecondsArr.length; i++) {
            let timeInSeconds = timeInSecondsArr[i];
            let rightCell = range.getOffsetRange(0, 1);
            // Get the value for the ss in h:mm:ss
            function getSeconds() {
                let seconds = Math.floor(timeInSeconds % 60);
                return seconds;
            }
            // Get the value for the mm in h:mm:ss
            function getMinutes() {
                let m = Math.floor(timeInSeconds / 60);
                let minutes = m % 60;
                return minutes;
            }
            // Get the value for h in h:mm:ss
            function getHours() {
                let h = Math.floor(timeInSeconds / 60);
                let hours = Math.floor(h / 60);
                return hours;
            }
            result = (`${getHours()}:${getMinutes()}:${getSeconds()}`);
            convertedValuesArr.push(result); 
        }
        return result;
    }

    convertValues();

    let rightCells = range.getOffsetRange(0, 1);

    for(let i = 0; i < convertedValuesArr.length; i++){
        rightCells.setValue(convertedValuesArr[i]);
    } 
    
}

I think the problem is that calls to [getOffsetRange](https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#excel-excel-range-getoffsetrange-member) use a constant row number. The row value should probably be the row index in your loops. For example getOffsetRange(i, 1). You may also want to look at [getCell](https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#excel-excel-range-getcell-member) as an alternative as it seems a better choice for your code. — Yogi, Feb 21 '23 at 21:46
You can also simplify your convert function by using [Date.setSeconds](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/setSeconds). See this [example](https://jsfiddle.net/svc8o2t9/). — Yogi, Feb 21 '23 at 21:50

score 1 · Accepted Answer · answered Feb 22 '23 at 19:29

The error appears to be in the loop towards the end of your code:

    for(let i = 0; i < convertedValuesArr.length; i++){
        rightCells.setValue(convertedValuesArr[i]);
    }

What's happening here is that you're writing each element of the array to all of the offset range at once. So the reason all of the cells are showing the last element is because that's the final element that's written to the range from the array. To fix this, you have to write to the cells of the offset range individually. This can be done using getCell(). So the updated line of code would look like this:

    rightCells.getCell(i,0).setValue(convertedValuesArr[i]);

Office script writing last element of array to entire range

1 Answers1