How to check if SharePoint item already exists in Power Automate
Posted On May 2, 2021
“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]'
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.
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.
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.