Table of contents

• Power Apps (Canvas)
• Create items in batches
• Update items in batches
• Power Automate
• Create items in batches
• Initialize a variable and get the lists entity type
• Prepare your data
• Create a batch template
• Combine data and batch template
• Set variable to the length of the current batch
• Send the batch request to SharePoint
• Picture of complete main part of the flow
• Closing thoughts about batching in Power Automate

Power Apps (Canvas)

Prerequisites: Build a collection in your Canvas app where the field data is an exact match to your list columns you want to patch. The column names need to match with the names of your SharePoint list.

Example: Your SharePoint list looks like this:

Create items in batches

The collection can be created using various method and will depend on your situation. This is what is could look like.

//a collection with the name colItemsToPatch
ClearCollect(
colItemsToPatch,
			[
					{
			        Title: "Test Title",
							myChoice: {Value: "Choice 1"},
							myText: "This is my text."
			    },
			    {
			        Title: "Test Title 2",
							myChoice: {Value: "Choice 2"},
							myText: "This is my second text."
			    }
			    
			]
)

Then you use that collection to patch it as is to SharePoint. No need for a ForAll().

Patch(
    LIST,
    colItemsToPatch
)

Update items in batches

To update items in batches we will use the same collection with one difference. The ID field is the actual ID of the item in the list you want to update.

//a collection with the name colItemsToPatch
ClearCollect(
colItemsToPatch,
		[
				{
				    ID: 1
		        Title: "Test Title 2",
						myChoice: {Value: "Choice 2"},
						myText: "This is my second text."
		    },
		    {
				    ID: 2
		        Title: "Test Title 25",
						myChoice: {Value: "Choice 25"},
						myText: "This is my 25th text."
		    }
		    
		]
)

Then you patch the same collection, now including the items IDs to your list. SharePoint will recognize the ID and update the values accordingly.

Patch(
    LIST,
    colItemsToPatch
)

Power Automate

Create items in batches

Initialize a variable and get the lists entity type

We will use the variable later to determine if the loop is done.

We will use the entity type for the data we are gonna send to SharePoint later. This is mandatory. The result of this will be use in “Prepare your data”.

Site Address: Your SharePoint Site
Method: POST
Uri: _api/web/lists(guid'LIST ID')/ListItemEntityTypeFullName
Headers: {
  "Accept": "application/json; odata=verbose",
  "Content-Type": "application/json; odata=verbose"
}

Prepare your data

The From in this select is the data your want to put into the list. This can be a list rows in an Excel action or an array of data that you built yourself before.

Your data has to be in a format that you would also use to create a single item with a HTTP request to SharePoint. In my example I only have text columns, but you can check the other field types here. Two important things: You will need the entity name of your list (from above) and the column names need to be the internal names of the column.

You will need two unique GUIDs for your batch request to SharePoint. You can use the settings > tracked properties of the select action to create those guids:

//overall guid that describes the batch as unique
batchGuid @guid()
//overall guid that describes each data in your batch, you can use the same GUID for all within that batch
changeSetGuid @guid()

Create a batch template

The batch template will be later filled with the prepared data and sets the necessary format. I put the template into a simple compose action. Notice the placeholder for data shown as |RowData|. This is not a fixed placeholder, you can name it yourself, as long as its special enough so it doesn't repeat somewhere else in the code.

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

POST SHAREPOINT-URL/_api/web/lists(guid'LIST GUID')/items HTTP/1.1
Content-Type: application/json;odata=verbose

|RowData|

Combine data and batch template

Create a Do-Until-Loop to be able to create more than 1000 items. This is necessary because each batch request can only take up to 1000 items. If you do create more than 1000 items, each pack of 1000 is one request. Make sure to stay within the limits of the SharePoint Connector.

The values for count and timeout might vary in your situation. The count value determines the maximum iterations the do until does and the timeout sets a fixed end to the loop. In my example the loop runs up to 60 times within a timeframe of 12 hours. After 60 times or 12 hours, the loop will timeout and stop.

Use the select as shown below to combine the data with the template by replacing the placeholder.

//here I am using the take and skip expressions to take exactly 1000 items per loop. Do-until has an internal iteration index that you can use here. It takes 1000 items from the data array and depening on the iteration skips the first, second, third, ... thousand.
take(skip(body('Select_-_Prepare_data'), mul(1000, iterationIndexes('Do_until_-_all_batches_are_sent'))), 1000)

//then within the select we actually put the data into the placeholder we entered before.
replace(outputs('Compose_-_Batch_template'), '|RowData|', string(item()))

Set variable to the length of the current batch

This will help us determine if the loop can end or if there are still items to process.

length(body('Select_-_prepare_current_batch'))

Send the batch request to SharePoint

Still within you loop, set up an HTTP SharePoint action. You can send up to 1000 items per batch request to SharePoint. This is the call you make to the SharePoint API.

Method: POST
URI: /_api/$batch
Headers: 
{
  "X-RequestDigest": "digest",
  "Content-Type": "multipart/mixed;boundary=batch_@{actions('Select_-_Prepare_data')?['trackedProperties/batchGuid']}"
}

--batch_@{actions('Select_-_Prepare_data')?['trackedProperties/batchGuid']}
Content-Type: multipart/mixed; boundary="changeset_@{actions('Select_-_Prepare_data')?['trackedProperties/changeSetGuid']}"
Content-Length: @{length(body('Select_-_prepare_current_batch'))}
Content-Transfer-Encoding: binary
 
@{join(body('Select_-_prepare_current_batch'), decodeUriComponent('%0A'))}
--changeset_@{actions('Select_-_Prepare_data')?['trackedProperties/changeSetGuid']}--

--batch_@{actions('Select_-_Prepare_data')?['trackedProperties/batchGuid']}--

Closing thoughts about batching in Power Automate

As I went over it, I realized that the do until loop runs one more time than necessary. You could add a condition to check within the loop, if the current batch contains data. And if not, don’t send another request to SharePoint. BUT, in my test runs this wasn’t an issue.

Anyway, this is a quick way of adding multiple thousand items to a list within the limitations of SharePoint Online. Quite handy in certain scenarios.

If you want to send other HTTP request to SharePoint in batches, change the URL and method in the template and prepare the data accordingly so it fits the body expected by the endpoint. If you have a delete batch request, you wouldn’t need a body.

Deleting in batches example below. In that case the RowData placeholder just holds the items ID.

Thanks for reading! 💕