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.

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 //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 //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; [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": "//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.