Sort excel table using power automate

[Anonymous]

Hi,

I am new to Power Automate.Can anybody tell me how to sort an excel table in a flow?

Thanks,

Asiri

[chsanche]

Hi @Anonymous 

Unfortunately, there is no sorting functionality in MS Flow. There are several forums for this asking as well: https://powerusers.microsoft.com/t5/General-Power-Automate/Flow-Options-for-sorting-data/td-p/157360

Best Regards,

--

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Anonymous]

How about the answer provided by here? yashag2255 

https://powerusers.microsoft.com/t5/General-Power-Automate/Filter-Query-Order-By-on-Excel-Rows/td-p/...

[dheckler]

I had a similar need.  I ended up creating an automation script in excel online for the worksheet and then in Flow use the 'Run Script' task.

[123213123]

would you mind sharing the script?

[dheckler]

Sure - here you go. In the code sample below, I was sorting by two different columns, date (key:3) and time (key:4). The script was actually created using the 'Record Actions' functionality in excel online. I can't take credit for writing it.

function main(workbook: ExcelScript.Workbook) {
	let timeLog = workbook.getTable("timeLog");
	// Custom sort on table timeLog
	timeLog.getSort().apply([{key: 3, ascending: false}, {key: 4, ascending: false}], false);
}

Once I created the sort script and tested it, I created the flow using the Run Script action for excel.

[123213123]

i need to do sort a to z for one column only, how can i adjust the script correctly can you maybe help?

[Anonymous]

Just looking at the code.

timeLog.getSort().apply([{key: 3, ascending: false}, {key: 4, ascending: false}], false);

timeLog.getSort().apply([{key: 3, ascending: false}], false);

[shavora]

hi @Anonymous how did you figure this out? I am also trying to apply your solution to mine, to sort alphabetically but the script gave errors when I ran it. Did you follow some guide on how to build it? I'd love to understand.

[GDBarkin]

Great solution!  I used the script method, and the only difference is that I used the "Run Script from Sharepoint Library" step to pinpoint the Excel file to run the script on:

[maheshBI]

Hi!!, Hope you've solved this problem, if not, let me show a use case that worked for me, assuming that you need to create a table from a range that must be sorted.

1. Open Excel, 
2. Check if the Ribbon has the Automate Menu, if not, you have to enable this from File, options, Custumize Ribbon, and add the Automate Tab to the right side, click on OK.
3. Select Automate Tab and Click New Script
4. On the Right side of excel wil will have this Window

Erase the existing code and paste this one.

function main(workbook: ExcelScript.Workbook) {
//worksheets from the Workbook
const worksheets = workbook.getWorksheets();

// Scans each worksheet
for (let i = 0; i < worksheets.length; i++) {
const worksheet = worksheets[i];

// Active range
let range = worksheet.getUsedRange();

//Last column and row
let lastRow = range.getRowCount();
let lastColumn = range.getColumnCount();

// Table Range
let tableRange = worksheet.getRangeByIndexes(0, 0, lastRow, lastColumn);

// Create Table from sel range
let table = worksheet.addTable(tableRange, true);
//Sort Table by the first Key ascending
let tableSort= table.getSort().apply([{ key: 0, ascending: true }]);

}

// Completed Response
return "You Have Created and Sorted your Table";
}

5. From the save window Save this script
6. Go to your flow and Add this Action:

Please fill all the parameterm and from the Script Box, locate the script (*.osts file usually stored in the OfficeScripts Folder in your onedrive for business.

7. Run and Enjoy!!!

let me know how this works for you

[McPowerPlatform]

Hi!!, Hope you've solved this problem, if not, let me show a use case that worked for me, assuming that you need to create a table from a range that must be sorted.

1. Open Excel, 
2. Check if the Ribbon has the Automate Menu, if not, you have to enable this from File, options, Custumize Ribbon, and add the Automate Tab to the right side, click on OK.
3. Select Automate Tab and Click New Script
4. On the Right side of excel wil will have this Window

Erase the existing code and paste this one.

function main(workbook: ExcelScript.Workbook) {
//worksheets from the Workbook
const worksheets = workbook.getWorksheets();

// Scans each worksheet
for (let i = 0; i < worksheets.length; i++) {
const worksheet = worksheets[i];

// Active range
let range = worksheet.getUsedRange();

//Last column and row
let lastRow = range.getRowCount();
let lastColumn = range.getColumnCount();

// Table Range
let tableRange = worksheet.getRangeByIndexes(0, 0, lastRow, lastColumn);

// Create Table from sel range
let table = worksheet.addTable(tableRange, true);
//Sort Table by the first Key ascending
let tableSort= table.getSort().apply([{ key: 0, ascending: true }]);

}

// Completed Response
return "You Have Created and Sorted your Table";
}

5. From the save window Save this script
6. Go to your flow and Add this Action:

Please fill all the parameterm and from the Script Box, locate the script (*.osts file usually stored in the OfficeScripts Folder in your onedrive for business.

7. Run and Enjoy!!!

let me know how this works for you

[SysAdmSG]

Hi @McPowerPlatform ,

Thank you for your detailed steps.

In my scenario, I am trying to sort an existing table in an Excel, and I encountered the following error during script execution:

We were unable to run the script. Please try again.
Office JS error: Line 22: {"message":"A table can't overlap another table. ","code":"InvalidArgument","type":"Worksheet","method":"addTable","line":20}
clientRequestId: d708b05b-c4bc-4d8d-89f6-569f96531ef4

Below is the table of an existing Excel which I want to sort by column H in ascending order:

Below is my script, referencing to your steps:

function main(workbook: ExcelScript.Workbook) {
    //worksheets from the Workbook
    const worksheets = workbook.getWorksheets();

    // Scans each worksheet
    for (let i = 0; i < worksheets.length; i++) {
        const worksheet = worksheets[i];

        // Active range
        let range = worksheet.getUsedRange();

        //Last column and row
        let lastRow = range.getRowCount();
        let lastColumn = range.getColumnCount();

        // Table Range
        let tableRange = worksheet.getRangeByIndexes(0, 0, lastRow, lastColumn);

        // Create Table from sel range
        let table = worksheet.addTable(tableRange, true);
        //Sort Table by the first Key ascending
        let tableSort = table.getSort().apply([{ key: 7, ascending: true }]);
    }

    // Completed Response
    return "You Have Created and Sorted your Table";
}

 
May I know if you will be able to advise on the solution?

Thank you.

[McPowerPlatform]

Hi, The main reason for the error you are getting is probably because you already had created the table in the workbook, please check that, if everything is ok, try this script and please let me know if this works for you:

function main(workbook: ExcelScript.Workbook) {

    //worksheets from the Workbook

    const worksheets = workbook.getWorksheets();

    // Scans  each worksheet

    for (let i = 0; i < worksheets.length; i++) {

        const worksheet = worksheets[i];

        // Active range

        let range = worksheet.getUsedRange();

        //Last column and row

        let lastRow = range.getRowCount();

        let lastColumn = range.getColumnCount();

        // Table Range

        let tableRange = worksheet.getRangeByIndexes(0, 0, lastRow, lastColumn);

        // Create Table from sel range

        let table = worksheet.addTable(tableRange, true);

        
  //Add a name for the table , in this case PowerPlatform

      let tables = workbook.getTables();

      tables.forEach(table => {

        table.setName("PowerPlatform");

      }); 

        //Sort Table by the first Key ascending or first column of the table

        let tableSort = table.getSort().apply([{ key: 0, ascending: true }]);

    }

    // Completed Response

    return "You Have Created and Sorted your Table";

}

//End of the script

[SysAdmSG]

Hi @McPowerPlatform ,

Thank you for your reply.

The current flow is that, I create the Excel file, follow by Creating the Table with headers, and then populate each row data.

Therefore, if I were to remove my current 'Create table' step, and replace the action of table creation using the script, may I know how I can modify the script to include Headers?

Thank you.

[McPowerPlatform]

Ok, Now I see the real picture, seems that you are missing a step, let's try to solve this with another perspective, just a quick question I realize that you create an excel file in sharepoint as your first step, and I see that you are adding content, what is that content (Outputs)??, are you tring to create a Blank excel file and then populate it with a Dataverse Table??, can you be more specific in that step??

[SysAdmSG]

Hi @McPowerPlatform ,

Yes, you are right. The 'outputs' comes from a 'Sample File Content' of a blank Excel file.

You are also correct on the sequence, as follow:

1. A Blank Excel is created

2. A Table is created in the Excel

3. For each dataverse row fetched, create a new row in the Excel Table

4. And lastly, I execute the sorting script to the Excel

[McPowerPlatform]

OK, I made it, works just fine.
1. First in any Sharepoint Document Library or Folder you need to store a blank Excel file with the required Structure, this can be used as a Template. Name it as TemplateTraveller.xlsx.

2. Using Excel, select New Script from the Automate Menu, 

3. Using the Code Editor, paste the following code, and the Save the Script in and folder of your SharePoint document library, this script will create a table for the new created excel file.

function main(workbook: ExcelScript.Workbook) {
//worksheets from the Workbook
const worksheets = workbook.getWorksheets();

// Scans each worksheet
for (let i = 0; i < worksheets.length; i++) {
const worksheet = worksheets[i];

// Active range
let range = worksheet.getUsedRange();

//Last column and row
let lastRow = range.getRowCount();
let lastColumn = range.getColumnCount();

// Table Range
let tableRange = worksheet.getRangeByIndexes(0, 0, lastRow, lastColumn);

// Create Table from sel range
let table = worksheet.addTable(tableRange, true);

//Add a name for the table , in this case PowerPlatform , you can change it
let tables = workbook.getTables();
tables.forEach(table => {
table.setName("PowerPlatform");
});

}

// Completed Response
return "You Have Created and Sorted your Table";
}

//End of the script

4. Once you have saved the script, repeat steps 2 and 3 and create the following script that will sort the table for you. PowerPlatform is the name of the table., key:0 is the first column of your table, you can change it.
 

function main(workbook: ExcelScript.Workbook) {
let myTable = workbook.getTable("PowerPlatform");
myTable.getSort().apply([{ key: 0, ascending: true }]);

}

5. Create the Flow with the following specifications:

Let me know how this works for you