The standard actions in Power Automate make it simple to perform CRUD operations against a SharePoint list. But they are designed to be easy to use and do not focus on performance. This post will explain how to delete many items from a SharePoint list using the $batch API.
Warning!!
This post is all about deleting SharePoint data, Please take care when replicating this flow!
Table of contents
The standard Power Automate method
A flow to delete items from a SharePoint list is very simple to create and might look something like this:
This flow will work fine, and on a small list it would be perfect, but it has a number of problems:
- If the list contains more than 5,000 items, the Get Items action will have to be placed in a loop.
- It will consume a lot of API actions and may cause you to exceed your daily limit.
- It is very slow.
This flow took four minutes to complete on a small list containing only 500 items.
A few people from my YouTube Channel and others that have commented on the method I used to get more than 5,000 items from a SharePoint list have been looking for a faster and more efficient method.
The SharePoint Batch Method
It is possible to make batch requests of actions for SharePoint to execute using the OData $batch query option. There are many applications for this method.
Because of the increased complexity of this flow, I suggest that you would only use it if you want to speed up the process of deleting items or reduce the number of API actions that you are using.
This flow took 47 seconds to complete on the same list containing 500 items.
To build this flow I used the excellent documentation about the batch method from this website. The batch is a request to the SharePoint API that is formatted in a particular way. Here is an example request which would delete two items:
--batch_9ad2bc1f-9117-4ecf-ae24-d543ac65edda Content-Type: multipart/mixed; boundary="changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a" Content-Length: 166999 Content-Transfer-Encoding: binary --changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a Content-Type: application/http Content-Transfer-Encoding: binary DELETE https://accendo1.sharepoint.com/sites/PowerAutomateText/_api/web/lists/getByTitle('Invoices')/items(2603) HTTP/1.1 Content-Type: application/json;odata=verbose Accept: application/json;odata=nometa IF-MATCH: * --changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a Content-Type: application/http Content-Transfer-Encoding: binary DELETE https://accendo1.sharepoint.com/sites/PowerAutomateText/_api/web/lists/getByTitle('Invoices')/items(2604) HTTP/1.1 Content-Type: application/json;odata=verbose Accept: application/json;odata=nometa IF-MATCH: * --changeset_3eb52cb0-6b29-4925-95d7-bf6aeb36a08a-- --batch_9ad2bc1f-9117-4ecf-ae24-d543ac65edda--
The API is quite fussy and if the request is not delivered in exactly the expected format it will fail.
Flow Detail
I have created a flow which, hopefully removes most of the complication associated with this method, which you can copy and paste into your environment. Here is a screenshot of the flow:
The Flow follows this process:
- Creates a variable used to hold the number of items found.
- A compose action defines the settings for the remainder of the flow.
- The site address
- The list name
- Another compose action creates a template for the change sets.
- A Do Until loop is started and continues until there are no items left.
- Get Items collects the list items in batches of 1,000 items (this is the limit for a batch)
- A select action creates the change set for each item.
- A compose action joins the change set.
- Finally a SharePoint HTTP action performs the request.
Flow Code and Implementation
To implement this flow, do the following:
- Create a new flow
- Add an Initialize variable action and name the variable itemCount.
- Copy and paste the Scope code below into your flow.
- Modify the settings action to create the correct values for your environment.
- Optional Step. Add an OData File to the Get Items action to narrow the selection of items to be deleted. Check out this excellent OData Cheat Sheet by Tom Riha for help with that.
Here is the code for the scope, which performs does the deletions.
{ "id": "519d6f92-4bd5-4ffb-9f91-0086-c5119f9e", "brandColor": "#8C3900", "connectionReferences": { "shared_sharepointonline_1": { "connection": { "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11" } } }, "connectorDisplayName": "Control", "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=", "isTrigger": false, "operationName": "Delete_SharePoint_Items", "operationDefinition": { "type": "Scope", "actions": { "Do_until": { "type": "Until", "expression": "@equals(variables('itemCount'), 0)", "limit": { "count": 60, "timeout": "PT1H" }, "actions": { "Get_items": { "type": "ApiConnection", "inputs": { "host": { "connection": { "name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']" } }, "method": "get", "path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/tables/@{encodeURIComponent(encodeURIComponent(outputs('settings')['listName']))}/items", "queries": { "$top": 1000 }, "authentication": { "type": "Raw", "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']" } }, "runAfter": {}, "trackedProperties": { "ItemCount": "@{length(body('Get_items')['value'])}" }, "metadata": { "flowSystemMetadata": { "swaggerOperationId": "GetItems" } } }, "Select": { "type": "Select", "inputs": { "from": "@body('Get_items')['value']", "select": "@replace(outputs('Template'), '|ID|', string(item()['Id']))" }, "runAfter": { "Set_variable": ["Succeeded"] }, "description": "replace(outputs('Template'), '|ID|', string(item()['Id']))" }, "BatchDelete": { "type": "Compose", "inputs": "@join(body('Select'), decodeUriComponent('%0A'))", "runAfter": { "Select": ["Succeeded"] }, "description": "join(body('Select'), decodeUriComponent('%0A'))" }, "Send_an_HTTP_request_to_SharePoint": { "type": "ApiConnection", "inputs": { "host": { "connection": { "name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']" } }, "method": "post", "body": { "method": "POST", "uri": "/_api/$batch", "headers": { "X-RequestDigest": "digest", "Content-Type": "multipart/mixed; boundary=batch_@{actions('settings')?['trackedProperties']['batchGUID']}" }, "body": "--batch_@{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('BatchDelete'))}\nContent-Transfer-Encoding: binary\n\n@{outputs('BatchDelete')}\n--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\n--batch_@{actions('settings')?['trackedProperties']['batchGUID']}--" }, "path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest", "authentication": { "type": "Raw", "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']" } }, "runAfter": { "BatchDelete": ["Succeeded"] }, "limit": { "timeout": "P1D" }, "metadata": { "flowSystemMetadata": { "swaggerOperationId": "HttpRequest" } } }, "Results": { "type": "Compose", "inputs": "@base64ToString(body('Send_an_HTTP_request_to_SharePoint')['$content'])", "runAfter": { "Send_an_HTTP_request_to_SharePoint": ["Succeeded"] }, "description": "base64ToString(body('Send_an_HTTP_request_to_SharePoint')['$content'])" }, "Set_variable": { "type": "SetVariable", "inputs": { "name": "itemCount", "value": "@length(body('Get_items')['value'])" }, "runAfter": { "Get_items": ["Succeeded"] } } }, "runAfter": { "Template": ["Succeeded"] } }, "Template": { "type": "Compose", "inputs": "--changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nDELETE @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items(|ID|) HTTP/1.1\nContent-Type: application/json;odata=verbose\nAccept: application/json;odata=nometa\nIF-MATCH: *\n", "runAfter": { "settings": ["Succeeded"] } }, "settings": { "type": "Compose", "inputs": { "siteAddress": "", "listName": "" }, "runAfter": {}, "description": "list to batch delete items from", "trackedProperties": { "batchGUID": "@{guid()}", "changeSetGUID": "@{guid()}" } } }, "runAfter": { "Initialize_variable": ["Succeeded"] }, "description": "Delete items from SharePoint using the $batch API method" } }
The first step in the scope is the settings action. Modify this action to the correct values for your environment:
{ "siteAddress": "https://SharePointSiteName.sharepoint.com/sites/ListName/", "listName": "someList" }
Conclusion
If you need to delete many items from a list in an efficient manner this flow should improve your performance and reduce the number of API actions that your flow consumes. I hope you are able to implement it into your environment easily. Please drop me a question in the comments if you need further help.
Support This Website
Thank you for visiting. It’s because of readers like you that I continue to write on this blog.
If you found value in what I share, I’ve set up a ‘Buy Me a Coffee’ page as a way to show your support.
Lluis Serra says
Thanks for this! ,it’s really fast
Paulie says
My pleasure, do you think there is any need to make it faster? I have another version which deletes at 2x the speed of this one.
robert Wilson says
I cannot get this to work, I keep getting an invalid request, when I look at the outputs. The workflow says it has worked properly, but nothing gets deleted!
Paulie says
You can view the actual error in the outputs of the API request. But for most people having this problem, they did not put a trailing slash on the siteAddress field of the settings compose action.
Andrew says
Hello Paulie, can you share your flow which deletes at 2x the speed of this one?.
Paulie says
I can’t at the moment, but I will see if I can dig it out later for you. Is the one in the post not fast enough? How many items are you deleting?
Andrew says
Thanks Paulie, 40,000. We modified the Delete Flow to use the Threaded approach as highlighted when adding SharePoint list items. Our throughput for deletion was between 5-10 items per sec.
Andrew says
Ultimately we are trying to find a way we can maintain 30,000 SharePoint List items using a Power Automate flow connecting to a 30,000 row excel file. The Excel file row data will have amended rows, new rows and rows removed.
We looked at the Delete items Flow and Add items Flow (threaded) actions and combined them. We have a premium account.
Its really about how quick can we refresh the data set.
Paulie says
Decent – hope it’s all worked out for you.
coachdanilomedeiros says
Paulie, I’ve reviewed my codes more than 20 times and no success. The flow runs but no delete occurs neither errors. Pleeeease help me!
Ruairi says
Amazing. Just fantastic!….not just the actual method, but the way you explain everything. You make what could be extremely complicated quite simple to follow. I just had to update my credentials on a few of the steps and that was it!
Thank you so much!
Just one quick question, I see in your “Get Items” action, you do not have any pagination options or anything like that. I generally thought the “get items” action only retrieved the first 100 rows, but here it pulled all the entries no problem. How did you achieve that, is there some option referenced in the source code?
Thanks so much again!
Jason says
Thank you for your amazing work. I combine this with the batch create items to create remove and create our student timetables daily. Works nicely.
Zach says
This is great! But I’m unable to batch delete records from certain SharePoint lists. I’ve noticed this doesn’t work with larger lists. For example I have a list of 40,000+ items that I’m trying to delete roughly 5,000 items from. When I run this flow it runs without any error, but the run time is about 1 second and nothing actually ends up being deleted. Any idea what this would be happening? Do I need to add additional steps to handle the large list?
Alexandre Suberbielle says
This is awesome, many thanks
Avi says
I’ve tried this flow and it runs successfully but doesn’t actually delete any record in the SP list 🙁
I then checked the HTTP request outputs and saw this error message:
“value”: “List ‘Excel for CORE reportTEMP’ does not exist at site with URL ‘https://amdocs.sharepoint.com/sites/GovPro’.”
and this is after going thru the whole SP list and collecting it’s records properly.
Have any clue ?
Avi says
I finally found my mistake
in Settings step of the flow, the “listName” MUST be the DISPLAY name of the list and NOT the name as appears in the URL for the list !
So if for any reason you have changed the name of the list – put TAHT NEW NAME in the Settings. And DO NOT put any “%20” instead of space. Just put in the list name as it appears in the list display.
Paulie says
If you look into the flow a bit more you will see that the list name is URI encoded at the point it is referenced.
Avi says
Hey Paulie,
Thanx a million for these brilliant posts (delete in bulk and copy in bulk) they have improved our throughput massively 🙂
I would LOVE to get a threaded solution for Delete in Batch, in order to speedup the process.
We have incorporated both your solutions (delete and coy in batch) into one flow which deals with lists/excels of approx. 2000 rows each, and I found that although the copy process takes approx. 2 min. – the deletion process takes x8 the amount of time!
So it would be of much an improvement to use a threading method for the deletion too.
Juan says
I’m getting this error for some reason and cant find what I did wrong
Error
Action ‘Send_an_HTTP_request_to_SharePoint’ failed
Status code
404
Headers
Key Value
Access-Control-Allow-Methods GET, PUT, PATCH, DELETE, POST
Access-Control-Allow-Origin *
Access-Control-Max-Age 3600
Access-Control-Expose-Headers *
Date Tue, 07 Dec 2021 22:11:46 GMT
Content-Length 54
Content-Type application/json
Body
{
“statusCode”: 404,
“message”: “Resource not found”
}
Can someone please advice what I did wrong.
Thanks in advance.
Ryan says
Hi Paul, I too am experiencing issues using your method. I have a list with 270000 items I want to delete. The method works and does not produce any error messages. However, it doesn’t work as expected and no items appear to be deleted. Monitoring the flow it appears that it does start to delete items but then strangely it ends up adding more to the list than what I started with. I cannot tell if these new items are real or not, but the number of items in the list grows. Any thoughts to why this might be happening? Any thoughts on changes to the Scope code to fix this?
Ryan says
Hi Paul. Please disregard and delete previous comment there was something else going on in the background with my list and power automate flows causing a conflict.
Rebuc Arisso (@RebucArisso) says
Works perfect!
Question: can this be adapted to work on document libraries as well? I have a flow where I need to delete list items as well as files in a specific library, can this work for that too?
Luis Carmoega says
Paulie,
Great tool for the toolbox! I have one question: I’m setting up a process to archive items from a list that gets updated daily. I archive daily copying to the archive list anything more than 2 days old and deleting those removed items from the production list. Thing is, the list is over 100k items. Looking to integrate this process with a similar for creating items on the archive.
Paulie says
Hi Luis, I’m pretty sure I did a batch create flow as well! If I didn’t publish it i deffo did it.
Paulie says
Here it is:
http://tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/
Rob Baker says
Hi Paulie,
This worked great and I was able to take the parts from your “create items threaded version” and apply it in the bulk deleted one on here so I successfully created a threaded solution for Delete in Batch!!! Fantastic Tutorials!!!
Paulie says
Rob – happy that it was useful to you. Good work!
Luis Carmoega says
Thank you for the link! It’ll definitely be very helpful! I do however have a quick question: I just implemented a test of the delete code and in the Output of the “Send an HTTP Request to Sharepoint” action it is giving me an error indicating “Resource not found” – I’ve tried everything and can’t seem to find the culprit. Any recomendations? The GetItems section works fine, and captures the List items. So that tells me the variables in Settings are correctly configured.
Rob Baker says
Hi Paulie,
Do you have a way of getting password expiration dates from users in Power Automate? I am trying to get that information to send out automated password expiration reminders to users via email if password expires in 10 days from expiration date.
Paulie says
Hi Rob, I have no idea if it is possible, but it is a really cool idea for a flow. I will have a look to see if it is possible tonight.
Rob Baker says
Pauli,
I got some progress today. The HTTP GET request in MS graph the api is https://graph.microsoft.com/v1.0/users?$select=displayName,userPrincipalName,lastPasswordChangeDateTime
how to sort my JSON results now for users who have to change PW. Our policy is 90 days for PW changes.
Paulie says
Yes, I did the same. Pretty simple from there – will put something together for you.
Rob Baker says
I just saw your reply! Thank you! can’t wait.
Marvin Bautista says
Hi Paul, thank you for sharing this. I’m new to PowerAutomate and I’m struggling to figure out how to paste the Scope code into my flow. Do i paste in the Scope control itself or is there another control I should use? Thanks!
Rob Baker says
Hi Paul, I was wondering if you had a chance to get a flow for the password expiration notification yet?
Paulie says
I did actually build it, but I haven’t had a chance to publish yet.
Alex says
Great work, Paulie! Thanks a lot.
Super way of explaining the steps. Keep it up.
ram charan says
Hi, I want to delete items after two working days of created date, daily items will get created in lakhs in sp list. Please help me how can I achieve this..? (saturday and sunday should not be calculated as a working day)
Gustavo Cerda says
Hello, good day, thanks for sharing your knowledge, it’s great to be able to count on it. I run the flow step by step and apparently it has no problems but the truth is that it does not delete the records in my list. I saw that in the output that is sent to HTTP and when reviewing the body, a “Value” appears: “Invalid request”. I leave an extract of the code. If possible, could you guide me on this please?
“$multipart”: [
{
“headers”: {
“Content-Type”: “application/http”,
“Content-Transfer-Encoding”: “binary”
},
“body”: {
“$content-type”: “application/http; msgtype=response”,
“$ Content”: “SFRUUC8xLjEgNDAwIEJhZCBSZXF1ZXN0DQpDT05URU5ULVRZUEU6IGFwcGxpY2F0aW9uL2pzb247b2RhdGE9dmVyYm9zZTtjaGFyc2V0PXV0Zi04DQoNCnsiZXJyb3IiOnsiY29kZSI6Ii0xLCBNaWNyb3NvZnQuU2hhcmVQb2ludC5DbGllbnQuSW52YWxpZENsaWVudFF1ZXJ5RXhjZXB0aW9uIiwibWVzc2FnZSI6eyJsYW5nIjoiZXMtRVMiLCJ2YWx1ZSI6IlNvbGljaXR1ZCBubyB2XHUwMGUxbGlkYS4ifX19”
“$applicationHttp”: {
“headers”: {},
“body”: {
“error”: {
“code”: “-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,
“message”: {
“lang”: “es-ES”,
“value”: “Invalid request.”
}
}
},
“statusCode”: 400
}
}
},
Tomas says
Hello Paulie, can you share your flow which deletes at 2x the speed of this one?.
JL van der Schelling says
Thanks for the tutorial. I’m getting an in the Template step. How can I fix this?
Unable to process template language expressions in action ‘Template’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘actions(‘settings’)?[‘trackedProperties’][‘changeSetGUID’]’ cannot be evaluated because property ‘changeSetGUID’ cannot be selected.
Chris says
Hi Paulie,
I have this working great.
The only issue I have, is when the items are deleted, they go to the recycle bin, which makes the recycle bin very full/cluttered/hard to find other files.
How can I tweak this to permanently delete files rather than them going to the recycle bin?
Thanks
PATRICK CHEUNG says
I tried to follow the video and after copying the code by pressing the “Copy to clipboard” button, and when creating a step for the scope, the my clipboard does not found the scope.
Not sure if I have miss something, or I would need to create all the steps in power automate manually.
Thanks !
Patrick
Paulie says
When you have copied it to clipboard. Go back to power automate clipboard and press CTRL-V
PATRICK CHEUNG says
Thanks Paulie ! it works !
Kin says
Hi Paulie, I implemented your solution it works but the performance didn’t improve for me it is taking 6mins to delete 639 records. Can you please help me.
Kin says
I just noticed that after few 100 records are deleted there is a error in HTTP Request: {“error”:{“code”:”-2146233083, Microsoft.SharePoint.Client.ClientServiceTimeoutException”,”message”:{“lang”:”en-US”,”value”:”The operation has timed out.”}}}
–batchresponse_edde0ae0-6947-40b3-ab7c-d5d246c9516b–
Yusuf Abiodun says
Hello Paulie. is it possible you do the Batch Update as well.
I really appreciate your work on the batch delete and batch create. it worked wonders for me.
i will appreciate if you also do one on BATCH UPDATE.
thanks and God bless.
javier alejandro reyes says
Can you share the link to download the flow? thanks
Dhamu says
Not able to copy the control to my clipboard as per your video. Need help on these controls.
Vishal says
Getting error as below:
A supported MIME type could not be found that matches the content type of the response. None of the supported type(s) ‘multipart/mixed’ matches the content type ‘application/json; charset=utf-8’.
clientRequestId: 08787f38-1f87-4a47-b423-a8b76990aa70
serviceRequestId: a6c855a0-f0a3-2000-2cad-5a6be899b8f6
Action : Send an HTTP Request to sharePoint
Joel Taylor says
Thank you, Paulie. This and the Create Flows are amazing.
I have been trying to get the delete functioning first, but I am struggling to get it to work.
Initially, I was getting 404 errors on ‘Get Items’ and ‘Send an HTTP request…’. I ‘fixed’ this but manually entering the site address and list. The error output for ‘Get Items’ was “The response is not in a JSON format.” and ‘Send an HTTP request…’ was “clientRequestId: 64128a6b-9397-4d5d-9774-59313ce181a6 serviceRequestId: 47c261a0-a099-1000-8f55-5c27eed6660c”.
Now (with adding the manual locations), the flow is succeeding, but I am getting errors returned:
HTTP/1.1 400 Bad Request
CONTENT-TYPE: application/json;odata=verbose; charset=utf-8
{“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”Invalid request.”}}}
–batchresponse_edef5a4f-190b-460f-84a4-2ae9ad1d931d
Content-Type: application/http
Content-Transfer-Encoding: binary
The URL that is created from the ‘settings’ seems to be correct. When I copy and paste it (for example, https://company.sharepoint.com/sites/Behaviour/Lists/Students/ ) the SP list loads well – but I was still getting the 404 errors. (Note, I did have a ‘/’ at the end of ‘siteAddress’.)
If the URL above was wrong, maybe that is the reason for the incorrect HTTP request – https://company.sharepoint.com/sites/Behaviour/Lists/Students/_api/web/lists/getByTitle('Students‘)/items(|ID|) HTTP/1.1.
padmaja says
Hi Paulie,i created the flow as you said,but for one sec its deleting only 100 records.
Remi Salome says
Hi Paulie – the flow was running well under 9min to remove c. 4500 items but for the last two weeks it takes over 30 min. Looking at the response, I get some sort of time out error after c. 200 items;
,”body”:{“error”:{“code”:”-2146233083, Microsoft.SharePoint.Client.ClientServiceTimeoutException”,”message”:{“lang”:”en-US”,”value”:”The operation has timed out.”}}}
Any ideas what is causing this?
Thanks,
Remi
Guy Boswell says
Wonderful. thank you very much Paul
Venkata says
Hi Paulie, Good day!
I want to update the more than 10000 items in a sharepoint list from Powerapps collection. Patch is taking too much time and also, update item action in the flow is also taking time to update.
Is there any flow to update the items as fast as possible?
Thank You in advance!
Guillaume says
Hi Paulie,
thanks for your time.
I really appreciate this 2 flows ‘Batch Create’ (http://tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/) and this one ‘Batch Delete’.
I tried to combine in one flow to archive items (Create first from list 1 to lis2 then delete same items from list 1) but don’t have success.
Any ideas to do this ?
Jerry Sun says
Good sharing, thank you!
Michael Kempinsky says
Hi Paul,
Thanks for sharing this. We are now ready for your next v2.0 flow where it can delete list items in more faster way.
Thanks,
M
JasonL says
This worked PERFECTLY!!
Thank you for this, sirrah
L Soprano says
Thanks so much! I had a list with over 600 items and this solution has helped me greatly!
Richard says
The flow appears to work successfully but nothing is actually getting deleted. I checked the inputs and outputs and don’t see anything untoward (but then I’m a noob so maybe I missed it). I can see its identifying the list items but nothing is being deleted. Any help would be greatly appreciated.
And Microsoft, if you are reading this, for gods sake, make your sharepoint flows delete much quicker in the first place so we don’t have to resort to these geniuses workarounds!
Richard Jones says
Following up my comment I got this in my Results input:
“–batchresponse_(I’ve blanked out the gobbledygook numbers in case it was important)\r\nContent-Type: application/http\r\nContent-Transfer-Encoding: binary\r\n\r\nHTTP/1.1 400 Bad Request\r\nCONTENT-TYPE: application/json;odata=verbose;charset=utf-8\r\n\r\n{\”error\”:{\”code\”:\”-1, Microsoft.SharePoint.Client.InvalidClientQueryException\”,\”message\”:{\”lang\”:\”en-US\”,\”value\”:\”Invalid request.\”}}}\r\n-
Any thoughts? Is it because I’m using v.2 implementation method of Sharepoint Lists?
Ryan says
this batch delete is interesting. is there a zip/json file that can be downloaded like the batch create
Paulie says
There is the scope code on the post, which you can just paste into your flow.
BP says
Does this clear out every item in a list? If not, is there something to run afterwards that could get any stragglers left in the list? I am looking to clear the whole list and just leave a blank list after done. Thank you for your wisdom!
Chase Theodore says
Hi Paulie, I have noticed some interesting actions using this method. Example: I have a list with 618 lines in it. In the first batch it identifies this 618 (as it should 618/1,000) so you would assume it would all be deleted within this batch, but it does a second batch and it counts 427 items. Then a third batch of 210, and finally a 4th batch of 0. So its like it sees and batches 1,000 items but it only deletes around 200 of these items and then re-batches. What would be your thoughts on this?
Kannan says
Thanks it helped a lot
Craig says
Thanks for this post. You mentioned in an earlier comment that you had another methodology that is up to 2x the speed of this one, could you post that?
Thanks again, this really helped me with some lists that I truncate and reload from an SQL query each night!
Tammy says
Hi,
I’ve been using this method for over a year now and suddenly started getting timeout issue like described in a couple of comments above. Anyone figured out what is causing this and how to resolve?
“error”:{“code”:”-2146233083, Microsoft.SharePoint.Client.ClientServiceTimeoutException”,”message”:{“lang”:”en-US”,”value”:”The operation has timed out.”}}
Paulie says
I’ve had the same thing myself but haven’t looked into it much yet. It’s a shame, have you tried using a smaller batch size?
Jenny says
Hi Paulie, would you mind sharing another version which is 2x the speed of this one? Currently, it’s deleting with 3 – 4 items per second speed and I would like to boost up the performance. Much appreciated!
Paulie says
I don’t think there is much point. Microsoft have changed something since this was posted and slowed things down. It’s not nearly as fast as it used to be. It’s still faster than the normal way. But not as fast as it used to be.
Sam1 says
Is it possible for you to package this flow up as a ZIP file and upload it somewhere? In power automate the “my clipboard” doesn’t register your SCOPE code when I copy and paste it unfortunately
J.R. says
It’s not any quicker than running it with the Delete Item action. 4000 items took an hour to delete
AK7 says
“–batchresponse_73a20c57-e0f1-4205-8e29-364db7291e4e\r\nContent-Type: application/http\r\nContent-Transfer-Encoding: binary\r\n\r\nHTTP/1.1 400 Bad Request\r\nCONTENT-TYPE: application/json;odata=verbose;charset=utf-8\r\n\r\n{\”error\”:{\”code\”:\”-1, Microsoft.SharePoint.Client.InvalidClientQueryException\”,\”message\”:{\”lang\”:\”en-US\”,\”value\”:\”Invalid request.\”}}}\r\n–batchresponse_73a20c57-e0f1-4205-8e29-364db7291e4e\r\nContent-Type: application/http\r\nContent-Transfer-Encoding: binary\r\n\r\nHTTP/1.1 400 Bad Request\r\nCONTENT-TYPE: application/json;odata=verbose;charset=utf-8\r\n\r\n{\”error\”:{\”code\”:\”-1, Microsoft.SharePoint.Client.InvalidClientQueryException\”,\”message\”:{\”lang\”:\”en-US\”,\”value\”:\”Invalid request.\”}}}\r\n–batchresponse_73a20c57-e0f1-4205-8e29-364db7291e4e\r\nContent-Type: application/http\r\nContent-Transfer-Encoding: binary\r\n\r\nHTTP/1.1 400 Bad Request\r\nCONTENT-TYPE: application/json;odata=verbose;charset=utf-8\r\n\r\n{\”error\”:{\”code\”:\”-1, Microsoft.SharePoint.Client.InvalidClientQueryException\”,\”message\”:{\”lang\”:\”en-US\”,\”value\”:\”Invalid request
I am getting this error and first time it works as expected. when i scheduled the flow its throwing this error. anything i need to check further
Claude Pichou says
Hi Paulie,
Your implementation works like a charm.
I only get this issue : I want to make it a Child Flow so that I can all it from many of my other flows. While calling it from the Parent Flow I prepared, I get kind of that error when i want to save it :
“{“error”:{“code”:”ChildFlowsUnsupportedForNonOpenApiConnections”,”message”:”The workflow with id ‘a589d482-bae5-ee11-904d-6045bd926ea1’, name SubFlow_BulkDeleteListItems cannot be used as a child workflow because it is on an old version of Flow. Please re-create it inside a solution.”}}”.
by the way, both child & parent flows are in the same solution but I am not able to figure out how to address this (without proliferating your implementation everywhere I need to BulkDelete in my flows 🙂 ) ???
Thanks for your valued blog
Simon Matthews says
I happened across this method while looking for a way to delete over 8mil rows from a SharePoint list, (list being updated by another flow and forgotten about)
I was too stupid to work out how to copy the code as in the tutorial, so ended up creating the actions manually by looking at the pictures and the settings in the code.
a couple of things i missed that messed me up:
in the settings compose action, i missed needing to add in the setting tracker properties, batchGUID @{guid()} and changeSetGUID @{guid()}
i also missed adding the trailing / to the Site Address]
i didn’t miss but more luck than judgement, the list name is as you see is as mentioned above, mine was “Assurance Test”
the send HTTP request action i missed adding in the uri /_api/$batch
and for my case i changed the parameters of the do until action to count 5000 and timeout P5D
this gives me 5000 calls, of 1000 each so 0.5mil rows i can delete, and upped the time out so it will run for long enough
I also added a filter to only target 0.5mil rows at a time, based on id, and then set off multiple instances, so i have one running for 0-0.5mil, 0.5-1mil etc should get rid of the all the rows in about 3 days
works a treat, and will be keeping it for any future out of control SharePoint lists
Carietta Austin says
When I use my power app trigger instead of a manual trigger the flow fails at get items.
Steve Fisher says
Sir, This looks like a great solution for me (hoping the speed is good on it). I cannot copy/paste the code into Power Automate though. Do you have an alternate method for us to get it?
Thanks.
J.K. says
Too sad, as it looks like nowadays you only can copy existing actions and not custom code to a flow. So copy & past the code you provided is no longer valid, unless I do it wrong!?
Paulie says
Did you try it in the old designer?
J.K. says
Yes, even the old designer did not let me paste the code. Currently I try to add the actions manually and just copy parts of the “big” code to rebuild the flow, but that is cumbersome 🙂
J.K. says
Another QQ the Get items action used, wouldn’t it better/faster to use an HTTP request action to get the items?
Paulie says
Could be faster. But the get items part isn’t the slow bit.
J.K. says
I just learned how to add code by copy & paste using the “old designer”, when going to the “My clipboard” even if I have copied already code into my clipboard I am greeted just with an image and text saying “Save time and past …” then there is a brief hint on how to copy any trigger or action. Bot the code from the clipboard is not showing as expected.
The simple trick seeing this image and text to simply hit CTRL+V to insert what you have at your clipboard, so it is just one extra step that is required.
PR says
2 users have posted error i.e. “Resource not found” – no one is interested in answering that.
PR says
Hi Juan & Luis
Got the solution – in the scope code shared which we have to copy paste seems there is error in step ‘Send_an_HTTP_request_to_SharePoint’ – user has to manually select sharepoint site and that’s why user is getting the error ‘resource not found’
sahil says
Hi PAULIE,
Just used this solution in my flow and i just copy pasted the whole code you were showing in the above blog and the flow is running flawless but the main issues i am facing is that i tried this thing in a list which contains 5k+ items the flow took 4 minutes to run completely but once it is finished i wasn’t able to find any errors but still its not deleting any items..
i just saw other comments from past 1-2 years also and some people are also facing that issue with them and i am not able to find any reply by you stating the solution if you have any new update on it can you please share.
Hugo says
Hello, I would like to delete only the lines that have the value disabled in the status column, how do I do it?
Paulie says
Query your list with an odata filter for the items that you want and then use those as the input for the batch.
Sindhu Ashok Kumar says
Hi Paulie,
I have created same flow, but I made few modifications I used schedule trigger and I want to filter the data from monday to saturday it will be around 9k records that I want to delete in batch wise.
Joe says
Hello Paulie,
I am attempting to use you flow to mass delete a list of roughly 4,000 items. I continue to get an error at the Get Items of:
“message”: “The response is not in a JSON format.”,
“innerError”: “Cannot read server response.”
I have verified that I have the “/” at the end of my site address. I also validated the list name, it is a single word and has not been changed. Any help would be appreciated. Thank you.
Rohit D says
Thank you Paulie. This does delete items . However, for me its taking around 3 minutes to delete 500 Items. I have put value 1000 in Get Items action.
I have just copied the scope code you provided, and test ran on three different lists that have single Title column. Do you think its Just Sharepoint/Microsoft issue and blame it on the load?
Jay says
Thanks so much!