Power Automate delete item from SharePoint list

This blog post will cover how to batch delete items in SharePoint with Power Automate. Our Flow will explore the SharePoint REST API calling the Batch endpoint and deleting 1000 items per time. For every Batch request made, you can add multiple Changesets up to 1000 Changeset requests. Moreover, for each Changeset, you can only add up to 1000 requests. Thats why our example will try to delete 1000 items per batch request just to make things simple.

In the tests I made, using this example we are able to delete 500 items in 2 minutes, 5.000 items in 12 minutes and 15.000 items in 40 minutes. All of my tests were ran in a Free Microsoft Flow account, so you probably may experience a better performance if you have a licensed account.

First steps on the Flow

In this example I chose to manually trigger a flow, just for it being easy to test. Needless to say tat you can change that for whatever you want.

Manually trigger a flow

After that we added four Initialize variable actions for different purposes:

• NewLine: simple string where I pressed enter to create a New line \n;
• BatchRequestArray: array variable initialized with an empty array [];
• ItemCount: integer variable initialized with 1.

Initializing variables

After that we start a Do until loop based on the variable ItemCount that will be updated later. Inside this loop we are going to have the steps for deleting the items, checking if there are more items inside the list and then updating the ItemCount variable.

Do until loop for clearing a list

The step for deleting the items was grouped into a Scope, so its easier to understand how the flow works. But you can also skip the Scope approach if you want. Inside this Scope we got the first step as Get items in list to be deleted where we limit the items being returned to 1000.

Getting items in SharePoint that will be deleted

After that we compose some data as Batch ID and Changeset ID. They are going to be used a lot in the next steps. Also we reinitialize the BatchRequestArray to an empty array, just to ensure we are in clean sheets here.

Composing and preparing the request

Adding the header of the Batch Request

Now we need to start composing the header of our batch request by adding it to our BatchRequestArray variable. Our compose function is composed by a combination of the Batch ID and the Changetset ID:

Preparing the Batch Header

Building the batch command for deleting each item in SharePoint

Now we will need to perform multiple composes iterating in the items returned by the action Get items in list to be deleted. So hit add to insert a new Apply to each command that will look like this:

Loop to make the batch body

Also you must enable Concurrency Control and increase the Degree of Parallelism to something like 40, but you can set that as you want.

Enabling paralellism

Now, inside the Apply to each function, you need to add a compose function that will be appended to the BatchRequestArray in every iteration.

The compose function for the Changeset request needs to have the data related to the item being deleted. This is something like the example below by using the Changeset ID, the current item ID and the name of the list you are going to delete the item:

Composing the Changeset request

Important notes:

• the DELETE, the URL and the HTTP version should be in the same line;
• trim any white space in the end of each line or you will face an error message like The request line DELETE is invalid. The request line at the start of each operation must be of the format;
• the blank lines should be respected as per the sample below;
• the Site Collection and Web should be present on the URL to the list;
• the list title should be used, not the list name. Pay attention to those differences or SharePoint will say this list doesnt exist.

Closing the batch command before sending the request

At last we need to close the batch body with Outputs of Changeset Id and Batch Id. Once you have appended that, you can prepare the request with a Join in the BatchRequestArray variable with separator as the NewLine variable.

• The Outputs being used are:

• Request:

Close the batch body

Making Batch request to SharePoint in Power Automate

Once we have the request body ready by using the Compose function Request in the previous paragraph, now we need to add a Send an HTTP request to SharePoint action.

• The Content-Type header must use the Batch ID like:

• The Body will be the Output of the Compose function Request.

Send the batch request to SharePoint

After the flow delete the items, it needs to check if are there any items in the list yet. We just configure the Get items action from SharePoint to return only one item and then update the ItemCount variable with the length of what was found, something like this:

Final steps on the flow after deleting

Complete flow

Thats it! In short words, I hope you found this post useful when working with SharePoint lists and a lot of data. Sometimes this will come in hand when you need batch delete items in SharePoint with Power Automate. Also you may have intereste on checking my post about Batch insert items in SharePoint with Power Automate.

References:
Microsoft documentation