Bulk Deletion: Sharepoint Items
Published: 2024-01-10 11:52:12

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. 
![Delete Item](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0gft03eia5ff0z0xlswl.jpeg)
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](https://learn.microsoft.com/en-us/power-platform/admin/api-request-limits-allocations), 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](https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service?tabs=csom). With the help of google I was able to find an [informative blog on batch calls](https://www.codesharepoint.com/sharepoint-tutorial/batch-crud-operation-using-sharepoint-rest-api#delete-list-items-using-$batch). There you will find loads of information about **CRUD** operations using **$Batch**


From the [docs](https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/make-batch-requests-with-the-rest-apis), 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.
![Essential Variables](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/toh9v1qd8z6oxayr0s9x.png)

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.


![Changeset Request](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g76iwl8hbrvvryijel2a.png)

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


![HTTP Request](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/czm5584xevy95ny70mi4.png)

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](https://learn.microsoft.com/en-us/graph/api/resources/sharepoint?view=graph-rest-1.0) was a good starting point and also this [blog](https://www.lewisdoes.dev/blog/taking-a-look-at-graph-explorer/) from [Lewis](https://www.linkedin.com/in/lewis-baybutt-8628171bb/).

To get your own hands with Graph, go to [Graph Explorer](https://developer.microsoft.com/en-us/graph/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`
  ![HTTP - Get Token](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m8ft5497a5zcoeuvm06s.png)
  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.
  ![Retrieve items](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kwzl5k2bxvowly6j5oc6.png)
   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.
  ![Parse Json](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vru05dxtmdv4dgi8nizu.png)
 * 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.
  ![HTTP Delete](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4d6pxja887a13wqah5gr.png)
   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](https://learn.microsoft.com/en-us/graph/throttling-limits) 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.