Forum Discussion
Office Scripts Custom Sort Failing During Automation
I am attempting to use office scripts within a OneDrive Excel document within Microsoft Teams to run a custom sort. Using the "Record Action" feature, I simply attempted to sort the range H3:J28 via the Timestamp or the H column. When running the custom sort manually, I get the below result, no errors.
When I run the same action using the recorded Script, for some reason the values within the I and the J columns are swapped. Is there a way to avoid this? The relevant script is listed below.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Custom sort on range range H3:J128 on selectedSheet
selectedSheet.getRange("H3:J28").getSort().apply([{key: 0, ascending: true}], false, true, ExcelScript.SortOrientation.columns);
}
Please try
function main(workbook: ExcelScript.Workbook) { const sheet = workbook.getActiveWorksheet() const range = sheet.getRange("H3:J28") const rangeSort: ExcelScript.SortField = { ascending: true, key: 0, sortOn: ExcelScript.SortOn.value } range.getSort().apply( [rangeSort], false, // case insensitive false, // no headers ExcelScript.SortOrientation.rows ) }
7 Replies
Hello, I have a similar issue but with two custom sort levle, but keep getting errors please help:
}// Custom sort on range range A2:AV610 on selectedSheet
selectedSheet.getRange("A2:AV610").getSort().apply([{ key: 5, ascending: true }, { key: 0, ascending: true }], false, true, ExcelScript.SortOrientation.columns);
Worked like a charm, than you! I am much more familiar with Excel VBA and the Office Scripts language has been a struggle for me.
You are welcome
Please try
function main(workbook: ExcelScript.Workbook) { const sheet = workbook.getActiveWorksheet() const range = sheet.getRange("H3:J28") const rangeSort: ExcelScript.SortField = { ascending: true, key: 0, sortOn: ExcelScript.SortOn.value } range.getSort().apply( [rangeSort], false, // case insensitive false, // no headers ExcelScript.SortOrientation.rows ) }Hi I'm trying to do the same, but with two custom sort levels keep getting errors. Can you help?
}// Custom sort on range range A2:AV610 on selectedSheet
selectedSheet.getRange("A2:AV610").getSort().apply([{ key: 5, ascending: true }, { key: 0, ascending: true }], false, true, ExcelScript.SortOrientation.columns);
Can't reproduce any errors, it works
function main(workbook: ExcelScript.Workbook) { const sheet = workbook.getActiveWorksheet() sheet.getRange("A2:AV610") .getSort().apply( [ { key: 5, ascending: true }, { key: 0, ascending: true } ], false, true, ExcelScript.SortOrientation.columns ) }Columns become sorted.
