Cloud Excel - Sort/Filter

[atomthompson]

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!

[Yutao]

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:

• Office Scripts in Excel on the web (overview)
• Announcing support for Office Scripts in the Excel Online (Business) connector
• Run Office Scripts with Power Automate
• Call scripts from a manual Power Automate flow
• Pass data to scripts in an automatically-run Power Automate flow

[atomthompson]

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]

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

[Yutao]

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!