Forum Discussion
AD9F95
Sep 06, 2024Copper Contributor
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?
- MichelleRanMSFT
Microsoft
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?
- AD9F95Copper ContributorDM'ed you.
I was not able to reproduce that, both on desktop and for web versions.