SharePoint delete list items older than
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. Show Warning!! This post is all about deleting SharePoint data, Please take care when replicating this flow! Table of contents
The standard Power Automate methodA 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:
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 MethodIt 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 DetailI 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:
Flow Code and ImplementationTo implement this flow, do the following:
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; [emailprotected]{actions('settings')?['trackedProperties']['batchGUID']}" }, "body": "[emailprotected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[emailprotected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('BatchDelete'))}\nContent-Transfer-Encoding: binary\n\[emailprotected]{outputs('BatchDelete')}\[emailprotected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[emailprotected]{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": "[emailprotected]{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" }ConclusionIf 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. |