Power Automate Get items

Do you want to know how to use Get items in Power Automate? In this Power Automate tutorial, I will explain how to use Power Automate Get items flow action to get SharePoint list items.

Finally, I will show you a few examples of “Power Automate get items filter query” where we can use filters to query list items in SharePoint.

Get Items in Power Automate

Power Automate Get Items flow action is used to get all items from a SharePoint list. Power Automate also allows you to filter items based on condition while retrieving items from the SharePoint list. It also allows us to use order by in get items in Power Automate.

Power Automate get items example

Now, let us see how to use get items in Power Automate with an example. I will show you how to get items from a SharePoint list in Power Automate.

The get items in Power Automate flow action accepts 8 parameters. These are

  • Site address: It accepts the SharePoint site address where the list exists.
  • List name: Choose the list name from where you want to get items.
  • Limit entries to folder: By providing the particular folder in the list or library, you can limit the entries of the folder.
  • Include Nested items: Using this property, we can restrict entries to only those within a particular folder or its sub-folders
  • Filter query: With this property, we can filter the returned items used in our flow.
  • Order By: This property will order the items in ascending or descending order.
  • Top count: This poverty limits the items we are fetching from the SharePoint list. By default, the item limit is 100. If your list size is larger, the list view threshold can increase to 5000
  • List view: With this property, restricting the columns returned by a view can be valuable, as it helps avoid excess data when you only need to interact with a specific set of columns defined in that SharePoint list view.
get items power automate

Now let’s take a simple example: I have a SharePoint list called Employee. Which contains the following columns:

  • Title-Single line of text
  • Name- Person
  • Department- Choice
  • Joining Date-Date/Time
  • Designation-Single line of text
power automate get items

Now, let’s see how to get all items from Sharepoint lists using the Power Automate Get Items flow action.

1. Open Power Automate Cloud, then click on +Create -> select Instant Cloud Flow.

get items in power automate

Then, provide a flow name and select the “Manually trigger a flow” action. Then click on Create.

get items sharepoint power automate

Now, you can see the “Manually trigger a flow” action is added to the flow page in Power Automate.

power automate sharepoint get items

2. To get items from a SharePoint list, click on the +New step -> select Get items action. Then Provide the information below:

  • Site address: Provide the SharePoint site address
  • List name: Provide the List name you want to get items.
get items power automate

3. Once you get the items from the list, we can display them in an HTML table. So, to display these SharePoint list items in tabular format, click on the +New step -> select the Create HTML table action. Then provide the below information:

  • From: Select value from dynamic content.
  • Columns: Select the Custom option.
  • Then, map the value like the one below.
See also  How to Extract substring from text using Power Automate Desktop?
HeaderValue
Name@item()?[‘Name/DisplayName’]
Department@item()?[‘Department/Value’]
Joining Date@item()?[‘Joiningdate’]
Designation@item()?[‘Designation’]
get items from sharepoint list power automate

4. Now, the flow is ready, and we can test it. For this, click Save and run the flow manually; the SharePoint list holds 6 items, and the HTML table below also contains 6 items with employee details.

get items sharepoint power automate

This is how to use the Power Automate get items to get all the items from a SharePoint Online list.

Power Automate get items filter query examples

Now, I will show you a few examples of using filter query in Power Automate get items. So, instead of retrieving all items from the SharePoint list, we can filter items using a filter query.

The Power Automate Filter Query is an OData system query option enabling users to filter data within a specific operation, like “Get items” or “Get rows.”

Syntax of Power Automate get items filter query:

<Internal Column> operator 'value'

Here, we will use 3 types of SharePoint columns to filter the data using Power Automate.

  • Choice column
  • Date column
  • Person column

1. Power Automate get items filter query based on Choice columns

The first example for the get items filter query we will see is based on a choice column in a SharePoint list.

In the above SharePoint Employee list, I will take the Department column. We will filter those items from the SharePoint list of Departments equal to IT.

For this, create an instant cloud flow that will trigger manually in Power Automate.

In the next step, add an action, i.e., Get items action. Then, provide the below information.

  • Site address: Provide the below SharePoint site address
  • List name: Provide the SharePoint list name
  • Filter Query: Provide the Query like below:
Department eq 'IT'
power automate get items filter query

To check the number of items whose department is IT, I will add the Create HTML table action. Then, provide the below information.

  • From: Select value from dynamic content.
  • Columns: Select the Custom option.
  • Then, map the value like the one below.
See also  How to split a string into an array in Power Automate?
HeaderValue
Name@item()?[‘Name/DisplayName’]
Department@item()?[‘Department/Value’]
Joining Date@item()?[‘Joiningdate’]
filter query in power automate get items

Now run the flow, and you can see that the number of items retrieved is 3 as 3 items contain department eq IT.

get items filter query

This is how to use the filter query in Power Automate get items flow action to filter items based on a choice column.

2. Power Automate SharePoint get items filter query based on a Date column

Now, let us see how to use the Power Automate SharePoint get items filter query using a date column.

In the SharePoint Employee list, I have a Joining Date column, which is a Date type column. Here, I will show you how to get items from the SharePoint list in Power Automate where the joining Date is equal to Today.

For this, create an instant cloud flow that will trigger manually in Power Automate.

In the next step, add the Power Automate get items. Then, provide the below information.

  • Site address: Provide the below SharePoint site address
  • List name: Provide the SharePoint list name
  • Filter Query: Provide the Query like below:

Here, to use the get items filter query in Power Automate, run the below query in the Filter Query below:

Joiningdate eq '@{formatDateTime(utcNow(),'yyyy-MM-dd')}'
power automate get items filter query examples

To check the number of items whose joiningDate is today, we will add Compose action. Then, provide the below information.

  • Inputs: Provide the value like below. It will add Apply to each action automatically.
power automate sharepoint get items filter query

Now run the flow; you can see the employee information whose Joining date eq Today.

get items filter query power automate

3. SharePoint get items filter query based on Person columns

Now, let us see how to use a filter query in get items in Power Automate based on a person column.

For this, I will use the Name column, which is a person column. We will filter those items from the list whose email is ‘lidiah@szg52.onmicrosoft.com’. Because while filtering the person field, we can filter only by person email.

For this, create an instant cloud flow that will trigger manually in Power Automate.

In the next step, add an action i.e., Get items action. Then, provide the below information.

  • Site address: Provide the below SharePoint site address
  • List name: Provide the SharePoint list name
  • Filter Query: Provide the Query like below:

Add the formula in the Filter Query.

Name/EMail eq 'lidiah@szg52.onmicrosoft.com'
get items from sharepoint list power automate

Now we will retrieve the details of the person whose email id is ‘lidiah@szg52.onmicrosoft.com’, and we will add the Compose action to display the filter items.

  • Inputs: Provide the details below. This will automatically Add Apply to each action.
filter query get items power automate

Now run the flow, and you can see the details of the person whose email ID is ‘lidiah@szg52.onmicrosoft.com’.

power automate get items filter

This is how to get items from the SharePoint list in Power Automate using the get items filter query.

Power Automate get items filter query operators and functions

Here, we will see the operators and functions that we can use in the Get items filter query in Power Automate.

See also  How to Convert String to Decimal in Power Automate?

List of operators and functions used in Power Automate SharePoint get items filter query.

Operators/functionsDescriptionExample
eqEqual toStatus eq ‘Pending’
neNot equal toStatus ne ‘Pending’
gtGreater thanRatings gt ‘3’
ltLess thanRatings lt ‘4’
geGreater than or equal todate ge ‘2023-11-1’
leLess than or equal todate le ‘2023-11-1’
andused when both the query satisfiesStatus ne ‘Pending’ and date le ‘2023-11-1’
orused when one of the query satisfiesStatus eq ‘Pending’ or date le ‘2023-11-1’
startswith(Fieldname, ‘string’)Start with the given/specified valuestartswith(Name, ‘L’)
endswith(Fieldname, ‘string’)End with the given/specified valueendswith(Name, ‘m’)
substringof(‘string’, Fieldname)Search for substring ‘Lap’ in field ‘Product’substringof(‘Lap’, Product)

Power Automate get a specific item from a SharePoint list

Do you want to get a particular SharePoint list item in Power Automate? In this Power Automate tutorial, I will explain how to use get item in Power Automate, especially how to get a specific item from a sharepoint list using the Power Automate get item flow action.

get item in Power Automate

Microsoft provides the get item flow action in Power Automate to get a single item by its ID from a SharePoint list.

There is a difference between get item vs get items in Power Automate. The Get item flow action will get a single item from a SharePoint list, whereas by using Power Automate Get items flow action, you can get multiple items from the SharePoint list.

Let us check now how to use get item in Power Automate to get a specific item from the sharepoint list.

We will find this action under the SharePoint on Power Automate flow:

Power Automate get item

After adding this to our flow, it will appear like below:

get item power automate

Here, we have to insert the SharePoint site address and list name. On ID, we have to provide an ID according to items in the SharePoint list.

Now, let us see, how the Power Automate get item works. For example, we have prepared a SharePoint list named ‘SalesOrder’ like below:

power automate get item

Here, ID is a default column in the SharePoint list. From this list, we want to retrieve the data whose ID is 7. To implement this, we will add a number input in ‘Manually trigger a flow’. Then, we will use this triggered ID in

get item sharepoint power automate, power automate get item from sharepoint list

When we will run the flow, it will ask us to input an ID. For example, we will insert 7; then, it will show all data of ID 7. As per our SharePoint list, it will show all data of Tab like below:

Power Automate get item id, power automate get specific item from sharepoint list

To retrieve specific columns from the SharePoint list, use the Compose action under the Power Automate get item flow action.

Suppose, you want to get only the Title, Model, and Order Date from the SharePoint list, then add a Compose action under the get item and select the desired columns like below:

power automate get item from sharepoint list

Now you can see the output will appear like this:

power automate get specific item from sharepoint list

This is how to get item from the sharepoint list in Power Automate by providing the item ID.

Conclusion

I hope now you got an idea of how to get items from a SharePoint list in Power Automate using the get items Power Automate flow action. I have explained how to use the get items filter query in Power Automate with a few Power Automate get items filter query examples.

You may also like:

  • Could you make a tutorial on how to get items from a certain action/connector? I need to get all values from a ‘Get item’ from sharepoint using an expression

  • Great article, thank you! Any insights as to why the Top Count description says “default = All” when default actually = 100?

  • You don’t mention how you get those values out of the “Compose” object(s)? How do you reference them outside of the “Apply to each” once it’s finished? I was thinking it would be some sort of expression, like
    body(‘Apply_to_each’)?[‘value’]?[0]?[‘ID’]
    ???
    It would have been nice to have gone into that, at the end. But I guess you’re supposed to do everything you want with them on-the-fly. So I figured out that your loop will already have the “value”:
    outputs(‘Get_items’)?[‘body/value’]
    and then, if you want a particular field off of that, like ID, it becomes
    items(‘Apply_to_each’)?[‘ID’]
    You should have mentioned this.

  • >