Some thoughts on programming stuff

Batch delete items in SharePoint with Power Automate

This blog post will cover how to batch delete items in SharePoint with Power Automate. Our Flow will explore the SharePoint REST API calling the Batch endpoint and deleting 1000 items per time. For every Batch request made, you can add multiple Changesets up to 1000 Changeset requests. Moreover, for each Changeset, you can only add up to 1000 requests. That’s why our example will try to delete 1000 items per batch request just to make things simple.

In the tests I made, using this example we are able to delete 500 items in 2 minutes, 5.000 items in 12 minutes and 15.000 items in 40 minutes. All of my tests were ran in a Free Microsoft Flow account, so you probably may experience a better performance if you have a licensed account.

How do I recover and restore deleted files? | BT
Delete key (BT)

First steps on the Flow

In this example I chose to manually trigger a flow, just for it being easy to test. Needless to say tat you can change that for whatever you want.

Manually trigger a flow - Batch delete items in SharePoint with Power Automate
Manually trigger a flow

After that we added four Initialize variable actions for different purposes:

  • NewLine: simple string where I pressed enter to create a New line ‘\n’;
  • BatchRequestArray: array variable initialized with an empty array ‘[]’;
  • ItemCount: integer variable initialized with ‘1‘.
Initializing variables - Batch delete items in SharePoint with Power Automate
Initializing variables

After that we start a Do until loop based on the variable ItemCount that will be updated later. Inside this loop we are going to have the steps for deleting the items, checking if there are more items inside the list and then updating the ItemCount variable.

Do until loop for clearing a list - Batch delete items in SharePoint with Power Automate
Do until loop for clearing a list

The step for deleting the items was grouped into a Scope, so it’s easier to understand how the flow works. But you can also skip the Scope approach if you want. Inside this Scope we got the first step as Get items in list to be deleted where we limit the items being returned to 1000.

Getting items in SharePoint that will be deleted - Batch delete items in SharePoint with Power Automate
Getting items in SharePoint that will be deleted

After that we compose some data as Batch ID and Changeset ID. They are going to be used a lot in the next steps. Also we reinitialize the BatchRequestArray to an empty array, just to ensure we are in clean sheets here.

Composing and preparing the request - Batch delete items in SharePoint with Power Automate
Composing and preparing the request

Adding the header of the Batch Request

Now we need to start composing the header of our batch request by adding it to our BatchRequestArray variable. Our compose function is composed by a combination of the Batch ID and the Changetset ID:

--@{outputs('Batch_ID')}
Content-Type: multipart/mixed; boundary="@{outputs('Changeset_ID')}"
Content-Transfer-Encoding: binary
Preparing the Batch Header - Batch delete items in SharePoint with Power Automate
Preparing the Batch Header

Building the batch command for deleting each item in SharePoint

Now we will need to perform multiple composes iterating in the items returned by the action Get items in list to be deleted. So hit add to insert a new Apply to each command that will look like this:

Loop to make the batch body - Batch delete items in SharePoint with Power Automate
Loop to make the batch body

Also you must enable Concurrency Control and increase the Degree of Parallelism to something like 40, but you can set that as you want.

Enabling paralellism - Batch delete items in SharePoint with Power Automate
Enabling paralellism

Now, inside the Apply to each function, you need to add a compose function that will be appended to the BatchRequestArray in every iteration.

The compose function for the Changeset request needs to have the data related to the item being deleted. This is something like the example below by using the Changeset ID, the current item ID and the name of the list you are going to delete the item:

--@{outputs('Changeset_ID')}
Content-Type: application/http
Content-Transfer-Encoding: binary

DELETE https://wiliammbr.sharepoint.com/_api/web/lists/getByTitle(%27BatchImportList%27)/items(@{items('Apply_to_each_item_found')?['ID']}) HTTP/1.1
If-Match: "1"
Accept: application/json;odata=nometadata
Composing the Changeset request - Batch delete items in SharePoint with Power Automate
Composing the Changeset request

Important notes:

  • the DELETE, the URL and the HTTP version should be in the same line;
  • trim any white space in the end of each line or you will face an error message like “The request line ‘DELETE’ is invalid. The request line at the start of each operation must be of the format”;
  • the blank lines should be respected as per the sample below;
  • the Site Collection and Web should be present on the URL to the list;
  • the list title should be used, not the list name. Pay attention to those differences or SharePoint will say this list doesn’t exist.

Closing the batch command before sending the request

At last we need to close the batch body with Outputs of Changeset Id and Batch Id. Once you have appended that, you can prepare the request with a Join in the BatchRequestArray variable with separator as the NewLine variable.

  • The Outputs being used are:
--@{outputs('Changeset_ID')}--
--@{outputs('Batch_ID')}--
  • Request:
join(variables('BatchRequestArray'), variables('NewLine'))
Close the batch body - Batch delete items in SharePoint with Power Automate
Close the batch body

Making Batch request to SharePoint in Power Automate

Once we have the request body ready by using the Compose function Request in the previous paragraph, now we need to add a Send an HTTP request to SharePoint action.

  • The Content-Type header must use the Batch ID like:
multipart/mixed; boundary=@{outputs('Batch_ID')};
  • The Body will be the Output of the Compose function Request.
Send the batch request to SharePoint - Batch delete items in SharePoint with Power Automate
Send the batch request to SharePoint

After the flow delete the items, it needs to check if are there any items in the list yet. We just configure the Get items action from SharePoint to return only one item and then update the ItemCount variable with the length of what was found, something like this:

@{length(body('Check_if_any_item_exist')?['value'])}
Final steps on the flow after deleting - Batch delete items in SharePoint with Power Automate
Final steps on the flow after deleting

Complete flow

The complete flow - Batch delete items in SharePoint with Power Automate
The complete flow

That’s it! In short words, I hope you found this post useful when working with SharePoint lists and a lot of data. Sometimes this will come in hand when you need batch delete items in SharePoint with Power Automate. Also you may have intereste on checking my post about Batch insert items in SharePoint with Power Automate.

References:
Microsoft documentation

21 Comments

  1. GF

    Hi
    I think this is something to keep in the piggy bank and become very valuable.
    I am trying to implement this by replicating what is being posted
    Flow runs say successful. No deletion is happening.
    if you go t a minute lend me a hand please!!!!
    [ like to bit more explanation on what is going inside inside”Compose Change request” ]
    Thanks

    • Wiliam Rocha

      Hey GF,
      Basically the idea here is to compose each one of delete requests, one at a time, in a HUUUUGE string that is going to be posted to SharePoint.
      You can try to open the result of the compose action and paste it here, so we can read and check if the syntax is correct, if the URLs and the list guids are well formatted and also what does the Send an HTTP Request to SharePoint returns to you… is it a success message? Is it an error message?
      Those are my thoughts on troubleshooting it.

  2. Francis Logan

    Hi,

    On

    –@{outputs(‘Batch_ID’)}
    Content-Type: multipart/mixed; boundary=”@{outputs(‘Changeset_ID’)}”
    Content-Transfer-Encoding: binary

    I’m getting an error saying this is a string, and array can’t be updated with it, is the syntax correct?

    Thanks

    Francis

    • Wiliam Rocha

      Hello Francis,
      Can you share with me a printscreen of the two steps showing the error, please?
      I would suggest double checking the Compose function being referenced in the Append array and if the Append array is properly initialized. But I must admit this is pretty odd because I believe you checked that and the problem still persists.

    • Nick

      Francis, I just came across this blog and am running into the same issue you did. Did you ever figure this out? If so, what was your solution? thanks
      Nick

  3. Padmaja Madala

    Hi WIlliam,

    I am getting below error:

    {
    “status”: 400,
    “message”: “The header ‘Content-Type’ was specified multiple times. Each header must appear only once in a batch part.\r\nclientRequestId: 3a497727-ba60-40f3-9f84-9dd759674399\r\nserviceRequestId: 81dbcb9f-a0c6-b000-efda-ddc30486772c”,
    “source”: “https://bakerhughes.sharepoint.com/sites/TPSDJLOperationalToolsDevelopment/_api/$batch”,
    “errors”: [
    “-1”,
    “Microsoft.Data.OData.ODataException”
    ]
    }

    • Wiliam Rocha

      Hello Padmaja Madala!
      It looks like we have multiple headers out of the order. Try to debug the flow and check how the batch request is created. Inside each changeset you need to have one Content-Type request header only.

      The Batch should start with:
      –batch_GUID1

      Then the changeset with a new GUID:
      –changeset_GUID2

      After comes the second request, with the same changeset ID:
      –changeset_GUID2

      When all requests are combined, you need to enter a new changeset with two hyphens:
      –changeset_GUID2–

      Finally the batch again with the two hyphens.
      –batch_GUID1–

      Best,
      Wiliam

  4. Padmaja Madala

    Seems some restrictions in this field. I am not able to send the Compose Request output.

    • Wiliam Rocha

      Let`s try to discuss that via LinkedIn then.

  5. Edison

    What is the difference between this and applying to the max the concurrency control?

  6. Gergely

    test (previous comment didn’t appear)

  7. Matey

    Hi,
    I have an error on Set variable after Compose beginning of the batch request.
    Error message: BadRequest. The variable ‘BatchRequestArray’ of type ‘Array’ cannot be initialized or updated with value of type ‘String’. The variable ‘BatchRequestArray’ only supports values of types ‘Array’.
    Could you please correct what can be the issue? I have everything set up as on the screens.

    • Martin

      I have the same error

  8. Dhamotharan

    Not able to copy to my clipboard as per your video.

    • Dhamotharan

      Kindly ignore my comments. Thanks

      • Wiliam Rocha

        No issues Dhamotharan 🙂

  9. Bear

    looks like nobody said it but, what about the compose “Compose end of the batch”? you show it in the last image, but it isn’t explained in the guide.

    • Wiliam Rocha

      That is a valid point! I believe the print screen with the full flow is not fully updated. The “Compose end of the batch” is actually included inside the “Append to array the end of the batch”. This is covered on section “Closing the batch command before sending the request”.
      Good catch on that, thanks!
      Best,
      Wiliam

  10. Udula Sachithra Jayawardena

    Hi William,

    Just the post I was looking for. I’m trying to implement this for a SharePoint list containing odd 50,000 records. The SharePoint site content doesn’t show any reduction in item count and the flow is running continuously. Do you think I’ve missed anything?

    • Wiliam Rocha

      Hi Udula, how are things going?
      Did it work after waiting a few hours? When the list is really big this behaviour is mentioned is expected. Let`s see if we are lucky enough to have your list cleared.
      Best,
      Wiliam

  11. Nick

    Hi Wiliam, I just came across this blog and am running into the same issue Francis and others seemed to be getting with the Array variable. “The variable ‘BatchRequestArray’ of type ‘Array’ cannot be initialized or updated with value of type ‘String’. The variable ‘BatchRequestArray’ only supports values of types ‘Array'”. Was this ever resolved? I replied to Frank above to see if he ever got this working.

    Thanks
    Nick

Leave a Reply

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

© 2024 wiliammbr's blog

Theme by Anders NorenUp ↑