Forum Discussion
Excel Sort Script does not work
I'm new to scripts so I tried to record one. However, the recorded script has errors. I am trying to sort a table by the first column. The script is:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Sort on table: selectedSheet column index: '0'
selectedSheet.getSort().apply([{key: 0, ascending: true}]);
}And the screenshot:
How can I sort by the first column if this doesn't work?
Thank you for your assistance.
https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.worksheet?view=office-scripts#getTable_key_ appears to be the appropriate method. However, they do not say if Table IDs starts at 0 or 1, I suspect 0 though
EDIT: Assuming the 2nd example on https://docs.microsoft.com/en-us/office/dev/scripts/resources/samples/copy-tables-combine works, the line of code:
const headerValues = workbook.getWorksheet(sheetNames[0]).getTables()[0]....
confirms Worksheets and Tables index start at 0
Could you try:
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Get 1st Table on selectedSheet let firstTable = selectedSheet.getTable(0); firstTable.getSort().apply([{ key: 0, ascending: true }]); }And instead of by ID, by your Table name:
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Get Table 'Clients' on selectedSheet let myTable = selectedSheet.getTable('Clients'); myTable.getSort().apply([{ key: 0, ascending: true }]); }If any option fails and you get an error message please provide that exact message
If the 1st (by ID = 0) works I would suggest you use that one so this will still work if you rename the Table later
EDIT There appears to be an https://stackoverflow.com/questions/tagged/office-scripts?tab=newest&page=1&pagesize=15
7 Replies
Hi J_Taylor40
Never did Office scripting and can't with my 365 subscription so don't expect too much
Looking at the script you posted I was under the impression that a range object (seems to be called 'interface') was missing. After some readings I found the following example https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.tablesort?view=office-scripts:function main(workbook: ExcelScript.Workbook) { // Get the current worksheet. let selectedSheet = workbook.getActiveWorksheet(); // Create a table with the used cells. let usedRange = selectedSheet.getUsedRange(); let newTable = selectedSheet.addTable(usedRange, true); // Sort the table using the first column. newTable.getSort().apply([{ key: 0, ascending: true }]); }Obviously this does what you want but this script "takes" all the cells used in the sheet
So, if the range you want to sort is actually, say B2:D10, I would suspect you would need to do something like:
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); let myRange = selectedSheet.getRange("B2:D10"); let newTable = selectedSheet.addTable(myRange, true); newTable.getSort().apply([{ key: 0, ascending: true }]); }Hope this helps & whatever the result is please provide feedback (can help others) - Thanks & nice day...
- Hi L z.
Thank you for the ideas. I tried both of them. However, because they create a table, they conflict with my table that I already have there.
I realised I failed to mention the present of the table. Is there some quick code adjustments that could be made for a table?I realised I failed to mention the present of the table
At least you know for next times you should provide the complete picture 🙂
Is there some quick code adjustments that could be made for a table?
Well, as I said, I can't do TypeScript and what I posted is only the result of some readings. I'll read again to try to help you... In the meantime could you provide:
1) The name of the existing Table on your sheet
2) Do you have, or plan to have, more than 1 Table in your sheet
