Agile leadership and more

Microsoft Flow: Delete items of a Sharepoint List

Sven Sommerfeld -

At some point in your professional career there is a situation where you want to delete items of a SharePoint list. E.g. to clean up the list with old items or to bulk update the whole list. This can of course be done manually or automatically via a Microsoft Flow in the Power Automate toolchain. The benefits of doing this automatically are pretty straight forward: Efficiency and prevention of human errors.

Prepare your Sharepoint list

For this tutorial I am assuming a list called test_list with the following columns:

  • Title: Single line of text
  • Name: Singel line of text

As a first step you need to add the ID column to the list, as this will be the unique Identifier. You can add the ID by clicking on one column, go to column settings and then Show/Hide columns.

A popup appears where you now can select the ID to be shown and click Apply.

Define the flow

Next you need to define your flow which will be basically:

  1. Get all items of the list
  2. Go over each item and delete it

As trigger I have chosen a manual trigger but of course this can be a time trigger or something else (like change of file content or so).

The final flow looks like this:

So what is happening here?

  1. Manually trigger a flow: This is the manual trigger for the flow
  2. Get items: Here the items of the Sharepoint list are fetched. You need to select the Sharepoint site which contains the list and the list itself.
  3. Apply to each: This is an action which will go over each item of a list. value is teh output value of the previos "Get items" and will be the input parameter here. In the "Apply to each" you need to add a "Delete item" action and set your Sharepoint site and the list again. Please make sure these are the same values as in "Get items". You have to set the ID as Id in order to delete the item.

That's it. Save the flow and test it. The result should look like this:

Filter for items to be deleted using ODATA

If you only want to delete certain items of the list then you need to use an ODATA filter in the "Get items" action. The "Filter query" is in the advanced options.
If you want to only delete items containing '2', then the "Filter query" will be this:

substringof('2',Name)

If you want to delete all items older than two weeks, you can use the following query:

Created le 'addDays(utcNow(),-14)'

Please note that you have to add the addDays(utcNow(),-14) as an expression to the query.