SharePoint is an amazing place to store information, and lists are the most useful way to organize the information. With the release of Microsoft Lists, this feature became even better and accessible to everyone. Power Automate helps us fetch the items in a list with the “get items” action. There is a lot of power (no pun intended) in this action, so let’s explore it in detail.
Where to find it?
You can find it under “Standard.”
Then expand, and you’ll find SharePoint in the list.
Then select the “Get items” action in the list:
Power Automate tends to save the most common actions in the main screen, so check there before going trough the full hierarchy. Also you can use the search to quickly find it.
Here’s how it looks like:
And here’s what it looks like with the advanced options:
Usage
There are 2 things we always need to configure.
- The site – where was the list created
- The list – where do want to fetch the information.
When you save, you get a warning.
SharePoint Lists can have a lot of information, so fetching all information can impact Flow’s performance. Even with this warning, SharePoint will only return the first 100 records.
This is to avoid pulling all values of a big list because we didn’t define yet any filter or limit. We rarely need the full dataset, and even if we do, we should paginate the dataset so that it fetches “blocks of data.” More on this later.
Limit Entries to Folder
Some people don’t know, but lists can be segmented into folders. It’s instrumental, and I have an article that describes the concept and how to do it. You can check it here. This option enables us to fetch information from one folder without having to do any other filter. Leaving it empty will fetch the “whole” dataset.
Please check the “SharePoint: Create a list based on folders” for more details.
Include Nested Items
Since folders can contain sub-folders, we need to indicate if we’ll only fetch from the selected folder or go deep in that hierarchy. Keep in mind that SharePoint will return information in the sub-folders of the folder that you selected, ignoring the other folders.
Filter Query
The most powerful option in action, in my opinion. So much so that I have a whole section dedicated to an “OData Reference” that can teach you how to filter the information and get only what you need.
Please keep in mind that:
- The column names may not be the same as you see them in your SharePoint List. For example, if you have a column called “First Name,” it will probably come from SharePoint as “Firstx0020Name”. SharePoint doesn’t like spaces and special characters so keep this in mind. If you want to know more, I developed a “SharePoint Best Practices” manual that goes deep into things like this.
- What you’re doing is “asking” SharePoint to get only the information you need based on the filter. So, if you need more information outside the filter, you need to do another “Get Items” action.
You’ll rarely need to fetch the whole dataset, so please think about performance and limit the information. There are other options below that can help with this.
Order By
Ordering data manually is a pain and doing it in Flow is a waste of effort. You can define the column(s) that you want to sort, and SharePoint will do it for you.
If you want to get the last item based on criteria, you can define the order by as “desc,” flipping the results, and then limit to 1 your Flow. Then you’ll get only one record with the information you need and don’t need to fetch everything and find the last element
Pay special attention to special characters and numbers. Sorting algorithms for each platform return these either at the beginning of the end, so it’s important to be in control of what you want to receive.
Top Count
Returns the number of records you defined or the number of records that exist, up until 100. For example, if you define the top as 50:
- If there are 70 or more records, SharePoint will return 50
- If there are 20 records, for example, SharePoint will return 20
- No records will return an empty dataset.
Limiting the number of records speeds up your Flow greatly because there’s less information to go from SharePoint to Power Automate, and there’s a lot less information to parse.
Limit Columns by View
When you fetch information, SharePoint returns everything it has for a specific item. You rarely need information, so it’s a good idea to define views that you can use to fetch the information.
If you don’t know how to create views, again, I have an article covering that, called “SharePoint: How to show additional columns in Views & Lists.”
Limitations
As mentioned before, the “get items” action only returns the first 100 elements. You can increase this limit, but I would advise that you do it with care and knowing the impact. To do it, go to “Settings.”
And enable “Pagination.”
You’ll get the results:
Please note the message in the “Pagination”:
Retrieve items to meet the specified threshold by following the continuation token. Due to connector’s page size, the number returned may exceed the threshold.
In most cases, Flow will fetch all records; even we define the pagination to 200. You must know this behavior so that if your Flow starts running slow, you know the reason.
Recommendations
Here are some things to keep in mind.
Only fetch what you need.
As a “rule of thumb,” only fetch the information that you need. Even if your list is small now, it can grow in size and make your Flows run slower. You can do this in 3 ways (or a combination of each of them):
- Defining a filter using the “Filter Query.” This removes the records you don’t need.
- Limit the number of records using the “Top Count.” If you only need the first 3 records, don’t fetch 100.
- Limit the number of columns using the “Limit Columns by View.” If you only need 2 columns, don’t fetch 20.
Name it correctly
The name is super important. Having the “Get Items” doesn’t tell you much. What items? What list? Always build the name so that other people can understand what you are fetching without opening the action and checking the details.
Always add a comment.
Adding a comment will also help to avoid mistakes. Indicate what information you’re interested in fetching and potential filters that you’ll apply. Do this before you start configuring the “Get Items” action so that you have a reference on what you want to do. It’s important to enable faster debugging when something goes wrong.
Always deal with errors.
Have your Flow fail graciously when the file doesn’t exist and notify someone that something failed. It’s horrible to have failing Flows in Power Automate since they may go unlooked for a while or generate even worse errors. I have a template that you can use that will help you make your Flow resistant to issues. You can check all details here.
Back to the Power Automate Action Reference.
Photo by Jonathan Farber on Unsplash
Hi Manuel, I’ve been trying to determine if a Get Items action returns an empty list. I have tried these various combinations in a Condition after the Get Items trigger but none of them work…any ideas?
empty(outputs(‘Get_items_-_Business_Stakeholders’)?[‘body/value’]) = true
empty(body(‘Get_items_-_Business_Stakeholders’)?[‘value’]) = true
length(outputs(‘Get_items_-_Business_Stakeholders’)?[‘body/value’]) = 0
length(body(‘Get_items_-_Business_Stakeholders’)?[‘value’]) = 0
Hi Rafael,
The equals comparison doesn’t work here. You need to do the following, )
equals(
for example:
equals(length(body(‘Get_items_-_Business_Stakeholders’)?[‘value’]) ,0)
Can you please try and see if it works?