cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

How to use 'order by' / 'get items' / 'insert row' - bring sorted rows into excel with flow

I need to create an excel document daily that captures any changes to a SharePoint list.

The list on SharePoint contains the columns 'vendor' / 'product' / 'promotion' and is sorted by vendor first, and product second.

Using the 'Get Items' SharePoint connector to feed 'Insert Rows' Excel connector is yielding the correct result, except that it is not putting the items in the order they are displayed on the SP list (sorted by vendor, then product). It is adding them to excel in the order they were added to the sharepoint list (I. E. The last addition to the sharepoint list is last on the excel table).

I am using insert row instead of create table or create worksheet because the formating of the cells must be maintained. By leaving the first cell in my template intact and deleting all of the old entries before getting the updated ones, the formatting is preserved.

Is there a way to bring the sharepoint row items in order?

Thanks!
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

Re: How to use 'order by' / 'get items' / 'insert row' - bring sorted rows into excel with flow

Thank you Alice.

I ended up figuring this out but believe there is a bug or two.

1) Everytime I put a 'Order By' in 'Get Items' it would trigger the action getting wrapped in an 'Apply to each' action, making it unworkable. If 'Filter by' was on, this would strangely not happen, so I ended up putting a dummy 'filter by' in that would get all rows.

Is this normal behaviour in some circumstances?

2) I renamed the 'Title' column to 'Vendor' in SharePoint, but it seems to be only the display name. I needed to sort by 'Title', which was not the header in my list.

Thanks again

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: How to use 'order by' / 'get items' / 'insert row' - bring sorted rows into excel with flow

Hi @  paulccook,

 

 

If you would like to get sharepoint items in order and the items is sorted by vendor first, and product second.

 

The workable Sytnax for the order by in the "Get items" action should be:

vendor asc, product asc

 

I have made a test on my side, the screenshot of my flow as below:

Capture.PNG

 

The flow would run successfully as below:

Capture.PNG

 

 

The Sytnax usage is described here:

Use OData query operations in SharePoint REST requests

For orderby:

"

Use the $orderby query option to specify how to sort the items in your query return set. To sort by multiple fields, specify a comma-separated list of fields. You can also specify whether to sort the items in ascending or descending order by appending the asc or desc keyword to your query.

"

 

Regards,
Alice Zhang

Highlighted
Regular Visitor

Re: How to use 'order by' / 'get items' / 'insert row' - bring sorted rows into excel with flow

Thank you Alice.

I ended up figuring this out but believe there is a bug or two.

1) Everytime I put a 'Order By' in 'Get Items' it would trigger the action getting wrapped in an 'Apply to each' action, making it unworkable. If 'Filter by' was on, this would strangely not happen, so I ended up putting a dummy 'filter by' in that would get all rows.

Is this normal behaviour in some circumstances?

2) I renamed the 'Title' column to 'Vendor' in SharePoint, but it seems to be only the display name. I needed to sort by 'Title', which was not the header in my list.

Thanks again

View solution in original post

Highlighted
Helper I
Helper I

Re: How to use 'order by' / 'get items' / 'insert row' - bring sorted rows into excel with flow

I had the same issue. Because it seems SharePoint Get Items only gets the top 1000, so I wanted to use Order By to to delete by the oldest, but it never worked. What I had to do was in the Filter Query, I added (PublishedDate lt 'Calculated time'), calculated time just being Substract from time, 60 days before utcNow().

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (8,252)