Additional menu

Tachytelic.net

Power Automate Flow to batch delete SharePoint List Items

by 101 Comments

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

Simplify Your SharePoint Batch Creation!

Deleting SharePoint list items in batches doesn’t have to be complicated.

If you’re finding the steps here a bit overwhelming, I’ve developed a custom C# connector that does all the heavy lifting for you—making batch deletion seamless and much easier to manage.

Check out the Power Automate Power Tools here and take your flows to the next level with minimal effort!

Take me to Power Tools! I don’t want to build an overly complex flow!

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:

A image of a Power Automate flow used to delete items from a SharePoint list.

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:

Image of a Power Automate Flow that uses the SharePoint batch API method to quickly delete items from a SharePoint List

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.

Buy Me a Coffee

Reader Interactions

Comments

  2. 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.

  3. 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!

  4. 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.

  5. Andrew says

    Hello Paulie, can you share your flow which deletes at 2x the speed of this one?.

  6. 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?

  7. 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.

  8. 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.

  11. 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!

  12. 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.

  13. 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?

  15. 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 ?

  16. 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.

  18. 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.

  19. 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.

  20. 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?

  21. 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.

  23. 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.

  26. 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!!!

  28. 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.

  29. 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.

  30. 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.

  34. 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!

  35. Rob Baker says

    Hi Paul, I was wondering if you had a chance to get a flow for the password expiration notification yet?

  38. 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)

  39. 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
    }
    }
    },

  41. 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.

  42. 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

  43. 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

  46. 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.

  47. 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–

  48. 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.

  50. Dhamu says

    Not able to copy the control to my clipboard as per your video. Need help on these controls.

  51. 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

  52. 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.

  54. 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

  56. 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!

  59. 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

  61. L Soprano says

    Thanks so much! I had a list with over 600 items and this solution has helped me greatly!

  62. 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!

  63. 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?

  64. Ryan says

    this batch delete is interesting. is there a zip/json file that can be downloaded like the batch create

  66. 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!

  67. 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?

  69. 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!

  70. 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.”}}

  71. 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?

  72. 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!

  73. 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.

  74. 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

  75. J.R. says

    It’s not any quicker than running it with the Delete Item action. 4000 items took an hour to delete

  76. 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

  77. 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

  78. 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

  79. Carietta Austin says

    When I use my power app trigger instead of a manual trigger the flow fails at get items.

  80. 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.

  81. 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!?

  83. 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 🙂

  84. 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?

  86. 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.

  87. PR says

    2 users have posted error i.e. “Resource not found” – no one is interested in answering that.

  88. 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’

  89. 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.

  90. 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?

  91. 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.

  92. 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.

  93. 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.

  94. 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?

  96. Kapil Kaushik says

    This flow doesn’t work enough, its taking almost 1 hour for deleting 6200 rows.
    same time taken by normal flow.

  97. Anthony Molina says

    Hi! What would I do if I were getting an excel attachment with roughly 3,500 rows of data? I am trying to get the excel sheet, turn it into a table, upload to a sharepoint document library, then upload that data to a sharepoint list. Since I receive the email daily, I would like to delete the items in the sharepoint list and replace/delete it with the new data. Since it is a lot of data, would there be way to implement your flow to what I want to do? Thanks!

  98. Paulie says

    Depending on how similar the data was to the previous day, you could also update the data instead of deleting and re-inserting. But 3,500 rows is a small enough amount that either approach will work. You could implement the flow as detailed here, or use Power Tools for Power Automate to achieve the same thing much easier.

    As long as the excel attachment you receive is in a consistent format every day, it should be straightforward. You just need to extract the attachment, grab the contents and then insert into SharePoint. For 3,5000 rows I would probably also batch create the SharePoint items. Don’t forget to spend some time working out the best plan for column indexing on your SharePoint list. Are you going to build an app from the list?

  101. Amy says

    Hi Paulie,
    I’m sure this flow is absolutely great when one can make it work !
    I tried, and the crtl+V from clipboard worked a charm.
    But I do have an error I can’t seem to workaround.
    ‘Unable to process template language in action Template […] The template language expression ‘actions(‘settings’)?[‘trackedProperties’][‘changeSetGUID’]’ cannot be evaluated because property ‘changeSetGUID’ cannot be selected”
    And indeed, there is no changeSetGUID nor trackedProperties property in my settings action, either in or out
    I used the “Compose” action from “Data operation” connector.
    Do you know how I could change this please ?
    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *