Forum Discussion

Copper Contributor

Jul 18, 2021

Solved

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) {...

Lorenzo
Jul 19, 2021
J_Taylor40

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

Lorenzo

Silver Contributor

Jul 18, 2021

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...

J_Taylor40

Copper Contributor

Jul 18, 2021

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?

Lorenzo
Silver Contributor
Jul 19, 2021
J_Taylor40
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
- J_Taylor40
  Copper Contributor
  Jul 19, 2021
  The name of the table is "Clients". No, I don't plan to have more than one table in the sheet.
  - Lorenzo
    Silver Contributor
    Jul 19, 2021
    J_Taylor40
    
    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