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

[paulccook]

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!

[v-yuazh-msft]

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:

The flow would run successfully as below:

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

[paulccook · Accepted solution]

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

[jhuynh1]

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