Bulk Delete SharePoint List Items Using Power Automate and REST API

Introduction

Deleting items one by one from a SharePoint list can be time-consuming and inefficient, especially when dealing with large volumes of data. Using Power Automate in combination with SharePoint’s $batch REST API offers a much faster and more scalable solution. In this article, we’ll walk through how to build a Flow that can delete multiple SharePoint list items in a single batch request - ideal for cleanups, data resets, or bulk removals.

Why Use Batch Operations?

• Performance: Fewer API calls improve speed and reduce throttling risks.
• Scalability: Handles thousands of items effortlessly.
• Automation: No more manual deletions.

Batch Delete SharePoint List Items - Use Case

You’re managing a SharePoint list, and need to delete all existing items to start fresh. Let’s build the Flow step-by-step. Manually deleting items or making individual API calls for a large SharePoint list is slow and tedious. With Power Automate, you can batch delete items efficiently. Here’s how.

Batch Delete Flow

Setting Up the Batch Delete Flow

The goal is to delete all items in batches, looping until the list is empty.

1. Initialize Variables

Add a Set Variable action:

• Name: ItemCount 

• Type: Integer 

• Value: -1 (to initiate the loop) 

This variable track remaining items and drives the deletion loop. 

2. Add Scope Action for Perform Batch Delete Functionality

1. Add Compose:  

Compose for Defines the SharePoint list details, including the site address and list name. 

{
     "siteAddress": "https://Tenant-Name.sharepoint.com/sites/Site-Name",
     "listName": "List Name"
}

Replace the placeholders with your tenant, site, and list names. 

2.  Add Compose for Batch Delete Template: -  

Prepares a template for batch deletion, specifying the structure of requests to delete multiple items. The Batch Delete Template in Power Automate allows us to delete multiple items from a SharePoint list in a single request. This is done using the _api/web/lists/getByTitle endpoint, where items are deleted based on their IDs. 

--changeset_@{actions('settings_Sharepoint_List')?['trackedProperties']['changeSetGUID']}
Content-Type: application/http
Content-Transfer-Encoding: binary

DELETE @{outputs('settings_Sharepoint_List')['siteAddress']}/_api/web/lists/getByTitle('@{outputs('settings_Sharepoint_List')['listName']}')/items(|ID|)
HTTP/1.1
Content-Type: application/json;odata=verbose
Accept: application/json;odata=verbose
IF-MATCH: *

Note on Formatting: Ensure that you maintain the empty line between the headers (ending with IF-MATCH: *) and the DELETE command. The SharePoint API requires this specific whitespace to identify where the headers end and the operation begins.

Step 2: Looping Through the Deletion Process 

Add a Do Until loop that runs until ItemCount equals 0. Inside the loop: 

1. Get SharePoint List Items 

Add a Get Items action: 

• Site Address: @{outputs('settings_Sharepoint_List')['siteAddress']} 

• List Name: @{outputs('settings_Sharepoint_List')['listName']} 

• Top Count: 4999 (SharePoint’s retrieval limit). 

2. Update ItemCount 

Add a Set Variable action: 

@{length(body('Get_items')['value'])}

3. Select Item IDs 

Add a Select action: 

From: @{body('Get_items')['value']}
Map: "@replace(outputs('BatchDelete_Template'), '|ID|', string(item()['Id']))"

4. Combine Requests 

Add a Compose action (named BatchDelete): 

@{join(body('Select_Item_ID'), decodeUriComponent('%0A'))}

5. Send the Batch Delete Request 

Send an HTTP Request to SharePoint

Add a Send an HTTP Request to SharePoint action: 

Method: POST 
URI:

/_api/$batch

Headers:

X-RequestDigest: digest
Content-Type: multipart/mixed; boundary=batch_@{actions('settings_Sharepoint_List')?['trackedProperties']['batchGUID']}

Body:

--batch_@{actions('settings_Sharepoint_List')?['trackedProperties']['batchGUID']}
Content-Type: multipart/mixed; boundary="changeset_@{actions('settings_Sharepoint_List')?['trackedProperties']['changeSetGUID']}"

@{outputs('BatchDelete')}
--changeset_@{actions('settings_Sharepoint_List')?['trackedProperties']['changeSetGUID']}--
--batch_@{actions('settings_Sharepoint_List')?['trackedProperties']['batchGUID']}--

CRITICAL: When pasting the body into the "Send an HTTP Request to SharePoint" action, you must include the blank lines before the @{outputs('BatchDelete')} expression. These line break act as delimiters for the batch request; omitting them will result in a "Bad Request" error. 

6. Check Results 

Add a Compose action: 

 @{base64ToString(body('Send_an_HTTP_request_to_SharePoint')['$content'])}

How It Works?

• The loop fetches up to 4999 items per batch, prepares a batch DELETE request, and repeats until ItemCount is 0.
• Once complete, your list is empty!

Why Choose Batch Deletion?

• Speeds Things Up: Cuts down API calls for a smoother run.
• Tackles Big Loads: Clears up to 4999 items in one go.
• Ditches the Grind: Turns a chore into a hands-off win.

Conclusion

Using Power Automate with the SharePoint REST API offers a practical and efficient way to delete list items in bulk. Whether you're performing a routine cleanup or resetting data, this approach helps streamline the process and save time.

If you have any questions you can reach out our SharePoint Consulting team here.

Bulk Create List Items in SharePoint using Power Automate and REST API

Introduction

Managing large datasets in SharePoint can be a challenge, especially when you need to populate a list with hundreds or thousands of items. Manually creating items or using individual API calls is slow and inefficient. Fortunately, Power Automate, combined with SharePoint’s $batch REST API endpoint, allows you to batch create items efficiently. In this blog, I’ll Walk you through building a Power Automate Flow to populate a SharePoint list with new items in batches - perfect for data imports or refreshes.

Why Use Batch Creation?

Performance: Fewer API calls reduce execution time and throttling risks.
Scalability: Easily handle thousands of items.
Automation: Eliminate manual entry.

Use Case

You have a SharePoint list and want to populate it with new items sourced from Excel File (for an instance, however it can be any source of data). Let’s build the Flow step-by-step.

Step 1: Setting Up the Batch Create Flow 

The goal is to process a dataset in batches until all items are created. 

Batch Create Flow

Initialize Loop Control 

Add a Set Variable action: 

• Name: LoopControl 

• Type: Integer 

• Value: -1 

Add a Scope 

Use a Scope action to organize the logic. Inside it: 

Define SharePoint List Details 

Add a Compose action (named "settings"): 

 {   
        "siteAddress": "https://tenant-name.sharepoint.com/sites/site-name/",   
        "listName": "List Name",   
        "batchSize": 1000   
 }   

Generate Sample Data 

Add a Select action (named GenerateSPData) to prepare data from source of data (you will need to fetch data from actual source of data - e.g. Excel File with relevant action, and get it into this select action): 

• From: Your dataset array. 

• Map: Format fields (e.g., {"Title": item()['Title']}). 

Create a Batch Template 

Add a Compose action (named batchTemplate): 

 --changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}   
 Content-Type: application/http   
 Content-Transfer-Encoding: binary   
 POST @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items HTTP/1.1   
 Content-Type: application/json;odata=verbose   
 |RowData|  

Step 2: Looping Through the Creation Process 

Add a Do Until loop (until LoopControl equals 0). Inside: 

1. Select a Batch 

Add a Select action: 

•  From: @{take(skip(body('GenerateSPData'), mul(outputs('settings')['batchSize'], iterationIndexes('Do_until'))), outputs('settings')['batchSize'])}    
   Map: @replace(outputs('batchTemplate'), '|RowData|', string(item()))   
  

2. Update LoopControl 

Add a Set Variable action: 

 @{length(body('Select'))}  

3. Prepare Batch Data 

Add a Compose action (named batchData): 

 @{join(body('Select'), decodeUriComponent('%0A'))}  

4. Send the Batch Create Request 

Send an HTTP Request to SharePoint

Add a Send an HTTP Request to SharePoint action: 

• Method: POST 

• URI: /_api/$batch 

• Headers:

•  X-RequestDigest: digest   
   Content-Type: multipart/mixed; boundary=batch_@{actions('settings')?['trackedProperties']['batchGUID']}
  

• Body:

•  --batch_@{actions('settings')?['trackedProperties']['batchGUID']}   
   Content-Type: multipart/mixed; boundary="changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}"   
   Content-Length: @{length(outputs('batchData'))}   
   Content-Transfer-Encoding: binary   
   @{outputs('batchData')}
   --changeset_@{actions('settings')?['trackedProperties']['changeSetGUID']}--   
   --batch_@{actions('settings')?['trackedProperties']['batchGUID']}--

5. Check Results 

Add a Compose action: 

 @{base64ToString(body('sendBatch')['$content'])}  

Do until Action

How It Works?

The loop processes up to 1000 items per batch, sends a batch POST request, and repeats until all data is added.

Why Go with Batch Creation?

• • Quick Wins: Fewer API hits mean rapid results.
  • Grows with You: Handles thousands of items like a champ.
  • No More Manual Mess: Frees you from tedious data entry.

Conclusion

This Power Automate solution simplifies the process of populating SharePoint lists, making it faster and more scalable with the GenerateSPData action. Whether you're adapting it to fit your own data or troubleshooting a specific use case!

 

If you have any questions you can reach out our SharePoint Consulting team here.