This post is part of a series of blog posts which demonstrate how to use the SharePoint Batch API in Power Automate. In this post I will demonstrate how to create SharePoint items in batches of up to 1,000. The main advantages of using the batch API are:
- Drastically improved performance.
- Potentially 1,000 times reduction in the use of Power Automate API actions.
This Flow is very similar to the post I wrote on batch deleting items from a SharePoint list.
Table of contents
Example Data
In this flow I am going to import an Excel file containing 3 columns and 15,000 rows of data to a SharePoint list with the same configuration:
The data source is unimportant. Any JSON array containing the data that you want to insert into SharePoint will be fine.
The Standard Power Automate Method
The most often used method to import rows of data into SharePoint with Power Automate is to use an apply to each loop to iterate through a JSON array using a “Create Item” action within the loop. It would look something like this:
The great thing about this method is that it is very simple to do, so if you are new to Power Automate it’s a great way to get data from a variety of sources into a SharePoint list.
For small datasets, this method is perfect. But it does have two problems:
- Performance is quite slow. A large dataset will take many hours to complete.
- A large number of rows will consume many API actions from your daily allowance.
I tested this flow using the first 1,000 items from the Excel table and it took 7 minutes and 15 seconds to complete. So to do all 15,000 rows would have taken approximately 1 hour and 50 minutes.
I’m not going to go into too much detail about the method above, because if you’re reading this, you’re probably trying to find a faster method!
Batch Create SharePoint List Items
It is possible, using the SharePoint $batch API to bundle up to 1,000 actions to SharePoint in a single HTTP request. This provides drastically increased performance and reduces the number of API actions required by a factor of 1,000.
However, it is much more complicated to implement. So if you need to increase performance, it is worth the effort.
To create exactly the same 1,000 rows of data using the batch method took only 1 minute and 46 seconds. So the total time to do all 15,000 records would be approximately 26 minutes. Around 1.5 Hours faster than the standard method.
Batch API Flow Overview
As per my method for deleting items from a SharePoint list, I have created a flow which should be simple for you to modify. Here is a screenshot of the flow:
The Flow Follows this Process:
- Data is retrieved from the data source
- A variable named LoopControl is defined which is used to determine when the Do Until loop should end.
- The Scope Create SharePoint Items begins:
- A compose action called Settings defines some settings for the flow.
- A Select action is used to reformat the incoming data to shape it correctly for the list.
- A template is defined for the change actions.
- A do until loop iterates through all of the JSON data in batches until no more remains.
- A select action is used to reformat the array items.
- A compose action is used to join the output of the select.
- The output of the compose is sent to SharePoint and contains the batch of actions to be completed.
Still not fast enough?
The flow above, although much more complicated than the standard method provides a great performance benefit. What I noticed, is that the performance bottleneck has shifted from Power Automate to SharePoint.
So I modified the flow, which adds a small amount of extra complexity, but doubles the effective speed:
| Method | Time to Create 1k List Items | Difference |
| Standard Apply/Each Loop | 435 seconds | |
| Batch API | 106 seconds | -329 |
| Batch API Threaded | 54 seconds | -383 |
This is achieved by splitting each batch of 1,000 into two simultaneous batches of 500 and running them in an apply to each loop with concurrency enabled. The apply to each loop uses an expression as its source:
if
(
equals(variables('LoopControl'), 0),
json('[]'),
createArray
(
take(body('Select'), div(length(body('Select')), 2)),
skip(body('Select'), div(length(body('Select')), 2))
)
)
This expression does a couple of things:
- Checks if there are any remaining items in the dataset, and if not returns an empty array.
- If there are items remaining it determines the length of the items and splits the array into two halves.
- The two halves are then run in parallel which doubles the speed of the SharePoint batch command.
Flow Code and Implementation
Because this flow is somewhat complicated I am providing a number of options for you to download and implement it.
Sample Data and Flow Exports
Download my sample Excel Data File and Exports of the flows to import into your environment here:
- Sample Excel Data
- Create SharePoint Items Batch API Method
- Threaded Create SharePoint Items Batch API Method
To work with my sample data you will need to put the Excel file into your OneDrive and modify the List Rows Present in a Table action accordingly.
After Importing the flow you will need to update the settings compose action at the beginning of the scope with your SharePoint site address and list name:
Which version is right for you depends on how many items you have and what your performance demands are. I recommend choosing the non threaded version unless you require the added performance of the threaded version.
Paste the Scope into a Flow
If you prefer to build your own flow you can do the following:
- Create a variable called LoopControl.
- Copy the Scope code below and paste it into your flow.
- Update the settings compose action.
- Change the Select action GenerateSPData to draw from your own data source, but retain the first column.
The scope code for the non-threaded version:
{
"id": "8869cabe-b30e-400c-b0f2-3d8a-d53fb36c",
"brandColor": "#8C3900",
"connectionReferences": {
"shared_excelonlinebusiness": {
"connection": {
"id": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-917cacf6-0b56-4b3f-ae9f-e703589cabec"
}
},
"shared_sharepointonline_1": {
"connection": {
"id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
}
}
},
"connectorDisplayName": "Control",
"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
"isTrigger": false,
"operationName": "Create_SharePoint_Items",
"operationDefinition": {
"type": "Scope",
"actions": {
"settings": {
"type": "Compose",
"inputs": {
"siteAddress": "https://accendo1.sharepoint.com/sites/PowerAutomateText/",
"listName": "Invoices Development Test",
"batchSize": 1000
},
"runAfter": {},
"description": "list to batch delete items from",
"trackedProperties": {
"meta": {
"type": "[email protected]{outputs('settings')?['listName']}ListItem"
},
"batchGUID": "@{guid()}",
"changeSetGUID": "@{guid()}"
}
},
"GenerateSPData": {
"type": "Select",
"inputs": {
"from": "@body('List_rows_present_in_a_table')?['value']",
"select": {
"__metadata": "@json(concat('{\"type\":\"SP.Data.', replace(outputs('settings')['listName'], ' ', '_x0020_'), 'ListItem\"}'))",
"Customer": "@item()?['Customer']",
"InvNum": "@int(item()?['Invoice Number'])",
"Invoice_x0020_Amount": "@float(item()?['Invoice Amount'])"
}
},
"runAfter": {
"settings": [
"Succeeded"
]
}
},
"batchTemplate": {
"type": "Compose",
"inputs": "[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nPOST @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items HTTP/1.1\nContent-Type: application/json;odata=verbose\n\n|RowData|\n",
"runAfter": {
"GenerateSPData": [
"Succeeded"
]
}
},
"Do_until": {
"type": "Until",
"expression": "@equals(variables('LoopControl'), 0)",
"limit": {
"count": 60,
"timeout": "PT1H"
},
"actions": {
"Select": {
"type": "Select",
"inputs": {
"from": "@take(skip(body('GenerateSPData'), mul(outputs('settings')['batchSize'], iterationIndexes('Do_until'))), outputs('settings')['batchSize'])",
"select": "@replace(outputs('batchTemplate'), '|RowData|', string(item()))"
},
"runAfter": {}
},
"Set_variable": {
"type": "SetVariable",
"inputs": {
"name": "LoopControl",
"value": "@length(body('Select'))"
},
"runAfter": {
"Select": [
"Succeeded"
]
}
},
"batchData": {
"type": "Compose",
"inputs": "@join(body('Select'), decodeUriComponent('%0A'))",
"runAfter": {
"Set_variable": [
"Succeeded"
]
}
},
"SendBatch": {
"type": "ApiConnection",
"inputs": {
"host": {
"connection": {
"name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
}
},
"method": "post",
"body": {
"method": "POST",
"uri": "/_api/$batch",
"headers": {
"X-RequestDigest": "digest",
"Content-Type": "multipart/mixed;[email protected]{actions('settings')?['trackedProperties']['batchGUID']}"
},
"body": "[email protected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: @{length(outputs('batchData'))}\nContent-Transfer-Encoding: binary\n\[email protected]{outputs('batchData')}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[email protected]{actions('settings')?['trackedProperties']['batchGUID']}--"
},
"path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest",
"authentication": {
"type": "Raw",
"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
}
},
"runAfter": {
"batchData": [
"Succeeded"
]
},
"limit": {
"timeout": "P1D"
},
"metadata": {
"flowSystemMetadata": {
"swaggerOperationId": "HttpRequest"
}
}
},
"Results": {
"type": "Compose",
"inputs": "@base64ToString(body('sendBatch')['$content'])",
"runAfter": {
"SendBatch": [
"Succeeded"
]
}
}
},
"runAfter": {
"batchTemplate": [
"Succeeded"
]
}
}
},
"runAfter": {
"Initialize_variable": [
"Succeeded"
]
}
}
}
Here is the scope code for the threaded version:
{
"id": "79273084-e0e1-450a-a878-1dda-e7a2b8a4",
"brandColor": "#8C3900",
"connectionReferences": {
"shared_excelonlinebusiness": {
"connection": {
"id": "/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-917cacf6-0b56-4b3f-ae9f-e703589cabec"
}
},
"shared_sharepointonline_1": {
"connection": {
"id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-46297fd3-182c-4215-bd63-ab2ad4fa0c11"
}
}
},
"connectorDisplayName": "Control",
"icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
"isTrigger": false,
"operationName": "Create_SharePoint_Items",
"operationDefinition": {
"type": "Scope",
"actions": {
"settings": {
"type": "Compose",
"inputs": {
"siteAddress": "https://accendo1.sharepoint.com/sites/PowerAutomateText/",
"listName": "Invoices",
"batchSize": 1000
},
"runAfter": {},
"description": "list to batch delete items from",
"trackedProperties": {
"meta": {
"type": "[email protected]{outputs('settings')?['listName']}ListItem"
},
"batchGUID": "@{guid()}",
"changeSetGUID": "@{guid()}"
}
},
"GenerateSPData": {
"type": "Select",
"inputs": {
"from": "@body('List_rows_present_in_a_table')?['value']",
"select": {
"__metadata": "@json(concat('{\"type\":\"SP.Data.', replace(outputs('settings')['listName'], ' ', '_x0020_'), 'ListItem\"}'))",
"Customer": "@item()?['Customer']",
"InvNum": "@int(item()?['Invoice Number'])",
"Invoice_x0020_Amount": "@float(item()?['Invoice Amount'])"
}
},
"runAfter": {
"settings": [
"Succeeded"
]
}
},
"batchTemplate": {
"type": "Compose",
"inputs": "[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\nContent-Type: application/http\nContent-Transfer-Encoding: binary\n\nPOST @{outputs('settings')['siteAddress']}_api/web/lists/getByTitle('@{outputs('settings')['listName']}')/items HTTP/1.1\nContent-Type: application/json;odata=verbose\n\n|RowData|\n",
"runAfter": {
"GenerateSPData": [
"Succeeded"
]
}
},
"Do_until": {
"type": "Until",
"expression": "@equals(variables('LoopControl'), 0)",
"limit": {
"count": 60,
"timeout": "PT1H"
},
"actions": {
"Select": {
"type": "Select",
"inputs": {
"from": "@take(skip(body('GenerateSPData'), mul(outputs('settings')['batchSize'], iterationIndexes('Do_until'))), outputs('settings')['batchSize'])",
"select": "@replace(outputs('batchTemplate'), '|RowData|', string(item()))"
},
"runAfter": {}
},
"Apply_to_each": {
"type": "Foreach",
"foreach": "@if\r\n(\r\n equals(variables('LoopControl'), 0),\r\n json('[]'),\r\n createArray\r\n (\r\n take(body('Select'), div(length(body('Select')), 2)),\r\n skip(body('Select'), div(length(body('Select')), 2))\r\n )\r\n)",
"actions": {
"batchData": {
"type": "Compose",
"inputs": "@join(item(), decodeUriComponent('%0A'))",
"runAfter": {}
},
"SendBatch": {
"type": "ApiConnection",
"inputs": {
"host": {
"connection": {
"name": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_sharepointonline_1']['connectionId']"
}
},
"method": "post",
"body": {
"method": "POST",
"uri": "/_api/$batch",
"headers": {
"X-RequestDigest": "digest",
"Content-Type": "multipart/mixed;[email protected]{actions('settings')?['trackedProperties']['batchGUID']}"
},
"body": "[email protected]{actions('settings')?['trackedProperties']['batchGUID']}\nContent-Type: multipart/mixed; boundary=\"[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}\"\nContent-Length: \nContent-Transfer-Encoding: binary\n\[email protected]{outputs('batchData')}\[email protected]{actions('settings')?['trackedProperties']['changeSetGUID']}--\n\[email protected]{actions('settings')?['trackedProperties']['batchGUID']}--"
},
"path": "/datasets/@{encodeURIComponent(encodeURIComponent(outputs('settings')['siteAddress']))}/httprequest",
"authentication": {
"type": "Raw",
"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
}
},
"runAfter": {
"batchData": [
"Succeeded"
]
},
"limit": {
"timeout": "P1D"
},
"metadata": {
"flowSystemMetadata": {
"swaggerOperationId": "HttpRequest"
}
}
},
"Results": {
"type": "Compose",
"inputs": "@base64ToString(body('sendBatch')['$content'])",
"runAfter": {
"SendBatch": [
"Succeeded"
]
}
}
},
"runAfter": {
"Set_variable": [
"Succeeded"
]
},
"runtimeConfiguration": {
"concurrency": {
"repetitions": 10
}
}
},
"Set_variable": {
"type": "SetVariable",
"inputs": {
"name": "LoopControl",
"value": "@length(body('Select'))"
},
"runAfter": {
"Select": [
"Succeeded"
]
}
}
},
"runAfter": {
"batchTemplate": [
"Succeeded"
]
}
}
},
"runAfter": {
"Initialize_variable": [
"Succeeded"
]
}
}
}
To copy the Scopes above simply copy the code into your clipboard, like this:
Conclusion
This is a great way to improve the performance of creating items in a SharePoint list. I have made my best attempt to make it as simple as possible for you to duplicate and I hope you find it useful. Please do let me know in the comments if you have managed to speed up your flows using this method. I’d be interested to know how long it took before and how much time you saved.
