cancel
Showing results for 
Search instead for 
Did you mean: 
0 Kudos

Cloud Excel - Sort/Filter

Please add a function to be able to sort tables.  I'm trying to get tables to be added to reports but they need to be sorted in the report.

 

Also a function to clear filters from tables.  Right now I have flows that fail if anyone applies a filter to everyone.  That or add a way to lock tables so that filters can only be applied to the person using it.

 

Thank you!

Status: New
Comments
Yutao
Microsoft

I'm wondering if you would want to give Office Scripts a try.

 

You can first create some script in Excel (either from scratch or via the recorder) that sorts the table and clears the filters. Then you can add a "Run script" action to your Power Automate flow to trigger that script before sending out the report.

 

Here is a sample script that sorts a table on the second column and clears all the filters:

 

function main(workbook: ExcelScript.Workbook)
{
  workbook.getTable("Table1").getSort().apply([{ key: 1, ascending: true }]);
  workbook.getTable("Table1").clearFilters();
}

 

Please check out these articles to learn more about Office Scripts:

 

atomthompson
Regular Visitor

That might work. I’ll check on Monday, however I’m pretty sure scripts are disabled for us due to security concerns. So it would be really nice if Microsoft could add the functions to PA.

atomthompson
Regular Visitor

Confirmed, scripts are disabled.  Is there a backdoor way of getting the same result?

Yutao
Microsoft

Might be a long shot, but here is a possible workaround:

 

Microsoft Graph API does provide REST APIs to sort a table or clear its filters. So theoretically you could add a HTTP with Azure AD action to your flow and invoke the Graph API to manipulate tables in your workbook. It may take a while to figure out the correct request URL and body but is probably worth a try.

 

@DeepakS has a wonderful tutorial on How to use Microsoft Graph API in Power Automate -... - Power Platform Community. You should definitely check it out!