Forum Discussion

AD9F95's avatar
AD9F95
Copper Contributor
Sep 06, 2024

Bug with Sorting in Office Scripts in Excel 365

I wrote an Office Script to sort automatically sort a range in my Excel worksheets, but it produces a really major bug where values are missing after a sort.  Specifically, it looks like the final columns that have been sorted end up with truncated data.  

 

The script is very simple, but it creates this error systematically:

 

 

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();

    let usedRange = selectedSheet.getUsedRange();

    //Display range to be sorted
    console.log(usedRange.getAddress());

    //Sort the sheet
    usedRange.getSort().apply([{ key: 0, ascending: true }], false, true, ExcelScript.SortOrientation.columns);

}

 

 

 

Here is what gets produced.  Note that the bottom right cells are truncated (starting from CJ17:CN34):

This seems like a major issue.  Has anyone run into this and does anyone know if there is a fix?

  • Hi AD9F95 - I'm sorry to hear you're running into this issue. I haven't been able to reproduce this when trying the script you shared, so my first guess is that you may be running into some data transfer limits, given how many columns your workbook has. Would you be able to share a sample workbook for us to troubleshoot with, removing any sensitive data as needed?

Resources