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. That’s 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.
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‘.
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.
The step for deleting the items was grouped into a Scope, so it’s 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.
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.
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:
--@{outputs('Batch_ID')}
Content-Type: multipart/mixed; boundary="@{outputs('Changeset_ID')}"
Content-Transfer-Encoding: binary
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:
Also you must enable Concurrency Control and increase the Degree of Parallelism to something like 40, but you can set that as you want.
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:
--@{outputs('Changeset_ID')}
Content-Type: application/http
Content-Transfer-Encoding: binary
DELETE https://wiliammbr.sharepoint.com/_api/web/lists/getByTitle(%27BatchImportList%27)/items(@{items('Apply_to_each_item_found')?['ID']}) HTTP/1.1
If-Match: "1"
Accept: application/json;odata=nometadata
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 doesn’t 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:
--@{outputs('Changeset_ID')}--
--@{outputs('Batch_ID')}--
- Request:
join(variables('BatchRequestArray'), variables('NewLine'))
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:
multipart/mixed; boundary=@{outputs('Batch_ID')};
- The Body will be the Output of the Compose function Request.
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:
@{length(body('Check_if_any_item_exist')?['value'])}
Complete flow
That’s 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
Hi
I think this is something to keep in the piggy bank and become very valuable.
I am trying to implement this by replicating what is being posted
Flow runs say successful. No deletion is happening.
if you go t a minute lend me a hand please!!!!
[ like to bit more explanation on what is going inside inside”Compose Change request” ]
Thanks
Hey GF,
Basically the idea here is to compose each one of delete requests, one at a time, in a HUUUUGE string that is going to be posted to SharePoint.
You can try to open the result of the compose action and paste it here, so we can read and check if the syntax is correct, if the URLs and the list guids are well formatted and also what does the Send an HTTP Request to SharePoint returns to you… is it a success message? Is it an error message?
Those are my thoughts on troubleshooting it.
Hi,
On
–@{outputs(‘Batch_ID’)}
Content-Type: multipart/mixed; boundary=”@{outputs(‘Changeset_ID’)}”
Content-Transfer-Encoding: binary
I’m getting an error saying this is a string, and array can’t be updated with it, is the syntax correct?
Thanks
Francis
Hello Francis,
Can you share with me a printscreen of the two steps showing the error, please?
I would suggest double checking the Compose function being referenced in the Append array and if the Append array is properly initialized. But I must admit this is pretty odd because I believe you checked that and the problem still persists.
Francis, I just came across this blog and am running into the same issue you did. Did you ever figure this out? If so, what was your solution? thanks
Nick
Hi WIlliam,
I am getting below error:
{
“status”: 400,
“message”: “The header ‘Content-Type’ was specified multiple times. Each header must appear only once in a batch part.\r\nclientRequestId: 3a497727-ba60-40f3-9f84-9dd759674399\r\nserviceRequestId: 81dbcb9f-a0c6-b000-efda-ddc30486772c”,
“source”: “https://bakerhughes.sharepoint.com/sites/TPSDJLOperationalToolsDevelopment/_api/$batch”,
“errors”: [
“-1”,
“Microsoft.Data.OData.ODataException”
]
}
Hello Padmaja Madala!
It looks like we have multiple headers out of the order. Try to debug the flow and check how the batch request is created. Inside each changeset you need to have one Content-Type request header only.
The Batch should start with:
–batch_GUID1
Then the changeset with a new GUID:
–changeset_GUID2
After comes the second request, with the same changeset ID:
–changeset_GUID2
When all requests are combined, you need to enter a new changeset with two hyphens:
–changeset_GUID2–
Finally the batch again with the two hyphens.
–batch_GUID1–
Best,
Wiliam
Seems some restrictions in this field. I am not able to send the Compose Request output.
Let`s try to discuss that via LinkedIn then.
What is the difference between this and applying to the max the concurrency control?
test (previous comment didn’t appear)
Hi,
I have an error on Set variable after Compose beginning of the batch request.
Error message: BadRequest. The variable ‘BatchRequestArray’ of type ‘Array’ cannot be initialized or updated with value of type ‘String’. The variable ‘BatchRequestArray’ only supports values of types ‘Array’.
Could you please correct what can be the issue? I have everything set up as on the screens.
I have the same error
Not able to copy to my clipboard as per your video.
Kindly ignore my comments. Thanks
No issues Dhamotharan 🙂
looks like nobody said it but, what about the compose “Compose end of the batch”? you show it in the last image, but it isn’t explained in the guide.
That is a valid point! I believe the print screen with the full flow is not fully updated. The “Compose end of the batch” is actually included inside the “Append to array the end of the batch”. This is covered on section “Closing the batch command before sending the request”.
Good catch on that, thanks!
Best,
Wiliam
Hi William,
Just the post I was looking for. I’m trying to implement this for a SharePoint list containing odd 50,000 records. The SharePoint site content doesn’t show any reduction in item count and the flow is running continuously. Do you think I’ve missed anything?
Hi Udula, how are things going?
Did it work after waiting a few hours? When the list is really big this behaviour is mentioned is expected. Let`s see if we are lucky enough to have your list cleared.
Best,
Wiliam
Hi Wiliam, I just came across this blog and am running into the same issue Francis and others seemed to be getting with the Array variable. “The variable ‘BatchRequestArray’ of type ‘Array’ cannot be initialized or updated with value of type ‘String’. The variable ‘BatchRequestArray’ only supports values of types ‘Array'”. Was this ever resolved? I replied to Frank above to see if he ever got this working.
Thanks
Nick