Bulk Deletion: Sharepoint Items

Recently, I encountered a daunting challenge: clearing thousands of records from an existing Sharepoint list. The typical UI deletion method was impractical for such a massive task, sparking my quest for a more efficient solution. In this blog, I'll share the three methods I experimented with in my test environment, offering an insight into the pros and cons of each. Whether you're facing a similar hurdle or just looking to enhance your Sharepoint management skills, these strategies might pave the way for smoother, more effective list handling. Let's explore these solutions together.

Power Automate: Delete Item

This method is very simple and staright forward.

This method should work just fine, but it's important to be aware of its limitations and potential impacts:

1. Limited Retrieval Capacity: The Get Items action can only get 5000 items at a time. So then you might want to run the flow multiple times or place the Get Items in a loop to get more items.
2. API Call Consumption: As stated in the documentation, this will consume a lot of API calls and might hamper your other processes.
3. Speed: It deletes items individually, and the flow takes a long time to run.

Power Automate: Sharepoint Rest API

This method is a bit complex and involves a lot of steps. We are going to make use of Sharepoint Rest API. With the help of google I was able to find an informative blog on batch calls. There you will find loads of information about CRUD operations using $Batch

From the docs, we can get code snippet of how a batch DELETE request could look like:

POST https://fabrikam.sharepoint.com/_api/$batch HTTP/1.1
Authorization: Bearer <access token omitted>
Content-Type: multipart/mixed; boundary=batch_7ba8d60b-efce-4a2f-b719-60c27cc0e70e
Host: fabrikam.sharepoint.com
Content-Length: 647
Expect: 100-continue

--batch_7ba8d60b-efce-4a2f-b719-60c27cc0e70e
Content-Type: multipart/mixed; boundary=changeset_efb6b37c-a5cd-45cb-8f5f-4d648006e65d

--changeset_efb6b37c-a5cd-45cb-8f5f-4d648006e65d
Content-Type: application/http
Content-Transfer-Encoding: binary

DELETE https://fabrikam.sharepoint.com/_api/Web/lists/getbytitle('OldList') HTTP/1.1
If-Match: "1"

--changeset_efb6b37c-a5cd-45cb-8f5f-4d648006e65d--
--batch_7ba8d60b-efce-4a2f-b719-60c27cc0e70e
Content-Type: application/http
Content-Transfer-Encoding: binary

GET https://fabrikam.sharepoint.com/_api/Web/lists HTTP/1.1

--batch_7ba8d60b-efce-4a2f-b719-60c27cc0e70e--

Now let's try to replicate this in our flow.

I first want to store the values of the site address and listname in a variable. And I also intialize varItemCount which we will use later in the flow to loop through the lists and perform the delete action till the list is empty.

Now we want to split the code snippet into chunks to formulate our request to the sharepoint API. Firstly, let's make a changset request.

After which we will use the Get items action to retrieve the top 1000 items from our list. Now we will replace the Id in the changeset request to the Id from the Get items action.

Now we could use the Send an HTTP request to Sharepoint to perform our batch DELETE operation using the POST method

I tested this method with a sharepoint list which had around 2350 records and the time it took finish the operation was around 3 minutes.

Pros

1. Efficiency: We can handle larger batches of deletions more effeciently than individual item deletion.
2. Control: We have more control and flexibility.

Cons

1. Complexity: Requires the understanding of Sharepoint REST API and JSON. It costs me some digging to understand the logic.

Power Automate: Graph API

I explored this method more out of curiosity. Recently I came across multiple GraphAPI contents, and I wanted to see if I could also do some POST operations with Graph. The simple answer is YES.

To perform an operation using Graph we need to understand how to work with Graph API. Microsoft documentation was a good starting point and also this blog from Lewis.

To get your own hands with Graph, go to Graph Explorer. Here we are able to play with sample data provided by Microsoft or authenticate with our tenant(provided we have rights) to run queries.

In our case, we need to have permission to access the tenant, the sharepoint site and the list, and also perform delete operation via the Graph API.

1. Register an application with azure AD:
   First we create a new app registration from Azure portal. In the
   API permissions tab, we add delegated permissions for the Microsoft Graph API(Sites.ReadWrite.All).

2. Flow Steps

   • Now we create an instant flow with manual trigger.
   • Initialize Variable for the token URL. (https://login.microsoftonline.com/{TenantId}/oauth2/v2.0/token)
   • HTTP action to receive the token. For the body part of the HTTP action. client_id=your_app_client_id&scope=https%3A%2F%2Fgraph.microsoft.com%2F.default&client_secret=your_app_secret&grant_type=client_credentials A secure way to use the app credentials would be to store them in a key vault and use the Azure key vault action in the flow to retrieve them.
   • After getting the token we add another HTTP action to retrieve the items from the list that we want to delete. For the URL (getting the first 500 items) https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items?top=500 For the headers we need to fill the key as Authorization and the value as Bearer {your_access_token}. You can retrieve the token from the output of the previous step.
   • We now add Parse JSON action to parse the outputs from our previous step to get all the retrieved items.
   • Now we want to run through every individual item to do the delete operation, for which we create an apply to each action and pass the id output from the Parse Json step.
   • Within the apply to each we add a HTTP action to perform the DELETE method to our retrieved items from the list. URL: https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items/@{items('Apply_to_each')?['id']}

Even though this method works we still have to keep certain things in mind:

1. Premium: We are using the HTTP connector which is a premium connector in Power Platform.
2. Graph API access: We need to get access to our tenant to perform queries and operations with Graph API.
3. App Registration: We need appropriate rights in azure to create app registrations and setting the right API permissions for Graph.
4. Rate limits: Graph API has rate limiting, that might slow down or interrupt deletion for large batches.

In this method I haven't tried batching using graph API, but this is something I would like to explore.

Conclusion

In conclusion, managing large-scale deletions in Sharepoint can be a complex task, but with the right tools and knowledge, it can be simplified and made more efficient. In this blog, we explored three distinct power automate methods: Power Automate Delete Item, Sharepoint REST API, and Power Automate Graph API. Each method has its strengths and limitations.

The Power Automate Delete Item is straightforward and user-friendly but is limited by its retrieval capacity and speed. The Sharepoint REST API offers more efficiency and control, especially for larger batches, but requires a deeper understanding of Sharepoint's inner workings. Lastly, the Power Automate Graph API provides a modern approach, albeit with considerations such as premium connector usage and API rate limits.

Your choice of method will depend on your specific needs, the size of the data, and your comfort level with Sharepoint and API interactions. Remember, efficient Sharepoint management is not just about knowing what tools are available but also understanding the nuances of each to make an informed decision.

Feel free to experiment with these methods in your environment and see which works best for your situation.