How to check if SharePoint item already exists in Power Automate

“I’d like to check with Power Automate if employee entry already exists in a Sharepoint list I’ve created; if not, create an item, otherwise update it.”


When you store data in a SharePoint list, e.g. input from MS Forms, you might not want to add a new item for each entry. You might want to create a new item if it’s a new entry, but update the item if it already exists. So, how do you check if an item already exists?

Get the item(s)

There’re two steps to check if SharePoint item already exists. Firstly, you must ‘Get items’ from the SharePoint list that correspond to the potentially new entry. It’s the same approach as when you build a lookup to SharePoint list. You’ll need one column that’s unique, a column whose data are in the list and also in the new entry. It can be an ID that’s provided on the input, user email address, or any other unique identifier.

Once you know which column to use, you can use it in the ‘Get items’ action in the Filter Query. Get only the items where the unique column is equal to the new entry. For example, a single line of text column called ‘Email’ that contains ‘Responder’s Email’ from MS Forms.

Email eq '[Responder's Email dynamic content]'
Power Automate lookup SharePoint items

Note: Always use the eq (equals) operator, you want to get only the item with the same value. And don’t forget to use the column internal name.

Check if the ‘Get items’ output is empty

The second step is to check if the ‘Get items’ action returned any item. After applying the Filter Query it’ll return an item only if it already exists. If it didn’t find any item, the output will be an empty array. That means you’ll need to add a ‘Condition’ to check if the output is empty. You can use the length(…) expression here to check if the output array length is 0 (empty) or more than 0 (not empty) items.

length(outputs('Get_items')?['body/value']) is equal to 0

If the length(…) is 0, the item doesn’t exist yet, you can create it. If it’s not 0, the item already exists and you can use the ID from ‘Get items’ to update it.

Power Automate check if SharePoint item exists

Don’t worry about the ‘Apply to each’ around ‘Update item’. If you used a column with unique values in the Filter Query, it’ll return just a single item.

Summary

The approach to check with Power Automate if a SharePoint item exists is the same as checking if a file already exists. There’re always two steps, do a ‘lookup’ to the list and check if anything was returned. If not, the item doesn’t exist and you can create it. Otherwise you’ve got the item in the ‘Get items’ output, including the ID to use for the update.

If you’d like to search by other column than single line of text, take a look on the Filtering section or subscribe to the newsletter below to get a SharePoint Filter Query cheat sheet.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

4 Comments

Add a Comment

Your email address will not be published. Required fields are marked *