Power Automate Get items action retrieves all the item information from a SharePoint list. It also allows you to filter items based on condition while retrieving items from the SharePoint list.
In this Power Automate tutorial, I will explain how to get last & first item ID from SharePoint list in Power Automate. Moreover, we will discuss how to get the last and first item IDs from a SharePoint custom column using Power Automate.
Get Last Item ID from SharePoint List in Power Automate
Let’s find out how to get the last item ID from a SharePoint ID column using Power Automate.
Here, I have used a SharePoint list named ‘Events’ with a few columns and data types.
| Columns | Data Types |
| ID | Number |
| Event Name(renamed Title column) | Single line of text |
| Event Description | Multiple lines of text |
| Event Date & Time | Single line of text |
| Event Organizer | Person or Group |
Here, I want to retrieve the last item ID from a SharePoint list, so I have created a Power Automate cloud flow that will get triggered when an item is created or modified in a SharePoint List. Then, I want to reflect that last ID and details of that SharePoint item in an email.
The screenshot below shows the last item ID details of the SharePoint List in an email.
To achieve this, follow the steps below.
1. Open the Power Automate home page in your browser, click on + Create -> Select the Automated cloud flow -> Enter the flow name and choose the trigger’s flow (When an item is created and modified)
- In this trigger, provide the Site Address and List Name.
2. To filter items based on condition while retrieving items from the SharePoint list, add the ‘Get items’ flow action and set the details
- Site Address: Provide the SharePoint site address.
- List Name: Select the SharePoint list.
3. Next, to get the last item ID from a SharePoint list using the last function, add the ‘Compose’ flow action.
last(outputs('Get_items')?['body/value'])?['ID']Where ID is the SharePoint in-built column.
4. Add the ‘Send an email (V2)’ flow action and set all the details.
- To: Give the user the email address to whom you want to send email.
- Subject: Provide the purpose for sending this email.
- Body: Here, I have given the Output of Compose action from dynamic content. To provide a Link to Item, we used an expression in Code view (</>) mode, as shown below.
<a href='@{items('Apply_to_each')?['{Link}']}'>Click Here</a>
5. Now click on Save and Test. Test it Manually and click on Run flow. The Compose flow action will display the output based on the expression inserted in the Inputs of the Compose flow action, as shown below.
This is how to get the last item ID from a SharePoint ID column using Power Automate.
Get Last Item ID from a SharePoint Custom Column using Power Automate
I will show how to get the last item ID from a SharePoint custom column using Power Automate.
For this, I have taken a SharePoint list named ‘Product Inventory’ with a few columns and their data types.
| Columns | Data Types |
| Product Name | Single line of text |
| Product ID | Single line of text |
| Product Brand | Choice |
| Product Quantity | Number |
| Product Manufacture Date | Date and Time |
Example:
Suppose I have created a SharePoint list with a custom ID column. Now, I want to retrieve the last item ID from the SharePoint list and reflect the details of that SharePoint item in an email.
The screenshot below shows the ID of a SharePoint List, and the second image shows the ID we retrieved after the flow ran successfully in an email.
To achieve this, follow the steps below.
1. Navigate to https://make.powerautomate.com in your browser to open the Power Automate home page; click on + Create -> Select the Automated cloud flow -> Provide the flow name and choose trigger’s flow (When an item is created and modified).
- Provide the Site address and List Name for this trigger.
2. Add the ‘Get items‘ flow action to filter items based on condition while retrieving from the SharePoint list and setting the required fields.
- Site Address: Provide the SharePoint site address.
- List Name: Select the name of the SharePoint list.
3. Next, add the ‘Compose’ flow action to retrieve the SharePoint last item ID, as shown below.
last(outputs('Get_items')?['body/value'])['ProductID']Where Product ID is the SharePoint custom column for ID.
4. Add the ‘Send an email’ flow action to send the details of the SharePoint last item with the ID and set all the details.
- To: Here, provide the user email address; I have given Modified By Email from dynamic content.
- Subject: Give the purpose of this email.
- Body: Select values from Dynamic content as shown below. To provide the Link to Item use an expression in Code view (</>) mode.
<a href='@{items('Apply_to_each')?['{Link}']}'>Click Here</a>
5. Now, it’s time to Save and Test. Click on Test and test it Manually -> The Compose flow action will display the output based on the SharePoint custom column below.
This is how to get the last item ID from a SharePoint custom column using Power Automate.
Get First Item ID from SharePoint List in Power Automate
Follow the instructions below to get the first item ID from a SharePoint ID column using Power Automate.
I have taken a SharePoint List named ‘Subscription Details’ with a few columns and their data types.
| Columns | Data Types |
| ID | Number |
| Subscriber Name | Single line of text |
| Subscription Renewable Date | Date and Time |
| Subscription Expire Date | Date and Time |
| Subscription Status | Choice |
| Subscriber Email ID | Single line of text |
Example:
Suppose I want to retrieve the first item ID from a SharePoint list. So, I have created a Power Automate cloud flow that will be triggered when an item is created or modified in a SharePoint List.
The screenshot below shows the SharePoint List ID column, and the second image shows the first item ID that we retrieved after the flow ran successfully in an email.
To achieve this, follow the steps below.
1. Navigate to https://make.powerautomate.com in your browser to open the Power Automate home page; click on + Create -> Select the Automated cloud flow -> Provide the flow name and choose trigger’s flow (When an item is created and modified).
- Provide the Site Address and List Name for this trigger.
2. To filter items based on condition while retrieving from the SharePoint list, add the ‘Get items’ flow action and set the required fields.
- Site Address: Provide the SharePoint site address.
- List Name: Enter the name of the SharePoint List.
3. Next, add the ‘Compose’ flow action to retrieve the SharePoint first item ID from the ID column and set the required fields.
first(outputs('Get_items')?['body/value'])?['ID']
4. Then add the ‘Send an email(V2)’ to send the details of the SharePoint first item with the ID. Set all the required fields.
- To: I gave the Subscriber Email ID from Dynamic Content [from SharePoint list].
- Subject: Enter the purpose of this email.
- Body: I have selected the Output of compose flow action from Dynamic content here. Also, to provide the Link to the Item, use an expression in Code view (</>) mode.
5. Now, it’s time to Save and Test. Click on Test and test it Manually -> The Send an email(V2) flow action will display the output below.
This is how to get the first item ID from a SharePoint ID column using Power Automate.
Get the First Item ID from a SharePoint Custom Column in Power Automate
Let’s find out how to get the first item ID from a SharePoint custom column using Power Automate.
For this, I have created a SharePoint list named ‘Project List’ with a few columns and their data type.
| Columns | Data Types |
| Project ID | Single line of text |
| Project Name (renamed title column) | Single line of text |
| Project Status | Choice |
| Project Start Date | Date and Time |
Example:
I have created a SharePoint list with a custom ID column. Now, I want to retrieve the first item ID from the SharePoint list and include that information in an email.
The screenshot below shows the ID of a SharePoint List, and the second image shows the ID we retrieved after the flow ran successfully in an email.
To work around this, follow the steps below.
1. Open the Power Automate home page, click + Create -> Select the automated cloud flow -> Enter the flow name and choose the trigger’s flow (When an item is created and modified).
- In this trigger, provide the Site Address and List Name.
2. Next, add the ‘Get items’ flow action to filter items based on condition while retrieving items from the SharePoint list. And set all the details.
- Site Address: Provide the SharePoint site address.
- List Name: Select the name of the SharePoint list.
3. Add the ‘Compose’ flow action to get the first item ID from a SharePoint list using the first function.
first(outputs('Get_items')?['body/value'])['ProjectID']Where the ‘Project ID’ field is a string, and the data returned by outputs(‘Get_items’)? [‘body/value’] is an array of items, each with a ‘Project ID’ field.
4. Add the ‘Send an email(V2)’ to send the details of the SharePoint last item with the ID. Set all the required fields.
- To: Give the user an email address. I have given the Project Lead from Dynamic Content.
- Subject: Provide the purpose of this email.
- Body: I have selected the Output of compose flow action from Dynamic content here. Also, to provide the Link to the Item, use an expression in Code view (</>) mode.
5. Now, click on Save and Test. Then test it Manually -> The Send an email(V2) flow action will display the output below.
This is how to get the first item ID from a SharePoint custom column using Power Automate.
Conclusion
I hope this Power Automate tutorial taught you how to get the last and first item ID from the SharePoint list using Power Automate.
Also, you may like some more Power Automate tutorials:
- Power Automate Get items Order By
- Create CSV Table From Excel File Using Power Automate
- Get All SharePoint List Items in Power Automate
- Power Automate get SharePoint list items count
- How to Get First Item ID from SharePoint List in Power Automate?
- Create an HTML Table from an Array in Power Automate
Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.
