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.