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.
Mezza says
A very thorough and detailed post! Thanks!
Pratibha says
for me this flow is running fine without any error but it isn’t writing anything in the list means it isnt creating items in the list, I can see values coming up from database. what could be the possible reason for it?
Paulie says
This means that the HTTP request was a valid format, but the contents of the batch request were not. So you need to look at the results compose action and see what the error returned from SharePoint was. My guess is that you have a string in a number column or vice versa. Have a look at the results and see what the error is.
Lluis says
Hi ,thanks for all ,really goes to much faster, but just a question, i have trayed with a excel and only imports 1024 records from 65000… Flow takes 6 minutes to end and with error, but not all records have been imported, any idea?
Thanks again!
tachytelic says
Lluis, you need to check the settings on “List rows present in a Table”. The threshold value is probably set to 1,000
Steven says
Hi Paulie, I got this error,
Request to Azure Resource Manager failed with error: ‘{“error”:{“code”:”ActionRepetitionNotFound”,”message”:”The specified repetition ‘000002-000000’ of workflow action ‘Results’ is not found.”}}’.
Steven says
Hi Paulie, thank you so much, please post Batch Update multi items?
Donavan Marais says
Great post! I followed it to the T, however, I couldn’t work out the tracked properties for the scope settings. When I ran my flow, I encountered this error:
Unable to process template language expressions in action ‘batchTemplate’ inputs at line ‘1’ and column ‘23313’: ‘The template language expression ‘actions(‘settings’)?[‘trackedProperties’][‘changeSetGUID’]’ cannot be evaluated because property ‘changeSetGUID’ cannot be selected.
What needs to be stored in the tracked properties?
Thanks
Paulie says
If you copy the scope from the post and paste it into your own flow you will be able to examine what is in the tracked properties of the settings action, did you recreate the flow manually?
Donavan Marais says
I copied it over as per your instructions.
Donavan Marais says
Did you perhaps set the changeSetGUID in a variable?
Donavan Marais says
I managed to get past that issue by adding a compose for both batchGUID and changeSetGUID. Next hurdle was the sendBatch action. Got this error:
{
“status”: 400,
“message”: “The ‘Content-Type’ header value ‘application/http\\nContent-Transfer-Encoding: binary’ is invalid. When this is the start of the change set, the value must be ‘multipart/mixed’; otherwise it must be ‘application/http’.\r\nclientRequestId: 11a6b56d-e054-43a0-befe-c09bdd5a4a14\r\nserviceRequestId: bad8e29f-904e-c000-579d-aed473ac947b”,
“source”: “https://test.sharepoint.com/sites/test/_api/$batch”,
“errors”: [
“-1”,
“Microsoft.Data.OData.ODataException”
]
}
My Headers are configured as per yours:
Content-Type: multipart/mixed;[email protected]{actions(‘settings’)?[‘trackedProperties’][‘batchGUID’]}
Donavan Marais says
Apologies, I found the issue. Corrected and it’s working great now. Thank you so much.
Paulie says
Well done! Glad you got it working. Lot’s of people getting on quite well with it now.
Donavan Marais says
It was definitely user error on my part! Again, thanks for an excellent article. You saved me on my project. Super happy with the results. Took 6 minutes to create 3,200 list items.
Paulie says
It does provide a massive speed boost! How long did it take the normal way to create 3,200 items?
Donavan Marais says
Unfortunately it timed out each time I tried to do it the normal way
Shatha says
Thank you so much
But does this method works if I need to update records in share point list?
Paulie says
This will not update existing records. I have not yet written up how to perform an update using this method.
Derek Crawford says
Hey Paul, thank you for your videos, they are helping me grow tremendously. Is there a way to circumvent the returned 5K record limit during the ‘List rows present in a table’? Perhaps a nested Do Until, like you implemented in your “Solutions to the SharePoint 5000 item limit using Power Automate” April 2020 post?
https://www.tachytelic.net/2020/04/many-ways-get-sharepoint-items-power-automate/
BK says
Hi Paulie, this was very helpful and I have incorporated your way to assign tasks to users (The task lists and number of users are dynamic based on user selection in Power Apps). I made slight modifications to your approach and incorporated it into my flow. The way you have organized and developed the flow was also very helpful. I learnt some new things. Thanks for great help.
On the other note, have you ever done uploading of document (PDF and Office documents, it works for ascii texts, but never got it work for PDF and office documents) via SharePoint HTTP action? If you have, any help would be appreciated. The reason for me to use HTTP action is because the document library (there are 50 document libraries and it is not feasible to write a switch statement in Power Automate) changes based on user selection (currently I am using non-SharePoint HTTP action which is a premium connector. This works fine. I am looking to see if I can eliminate the premium connector). Any help or suggestions would be greatly appreciated.
Paulie says
Hi BK,
Pleased to hear that you found it helpful. Yes, I have uploaded documents via SharePoint HTTP Action, and it is possible to do any file type.
You cannot create a switch statement, but you can easily create an array of document libraries and then filter that list based on the user selection and then dynamically select the correct library. You could do this within a couple of actions and avoid the need for a switch or a condition entirely.
Paul
BK says
Paulie, Thanks for reply, For me the problem lies with how the File content is being passed to SP HTTP action. Every time I do this, the file gets created but the file (PDF and Office documents specifically) is corrupted.
Tyler says
Hello Paulie,
Thanks for the demos and great tool with both the batch delete and batch create. I’m using batch delete to reduce the actions in a lot of flows that delete old data on a schedule, but I have a flow where I actually want to use both batch create and batch delete because I need to archive data before deleting it.
Unfortunately I keep getting the following error:
HTTP/1.1 400 Bad Request
CONTENT-TYPE: application/json;odata=verbose;charset=utf-8
{“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”A type named ‘SP.Data.OlderRecords_Archive_and_MMListItem’ could not be resolved by the model. When a model is available, each type name must resolve to a valid type.”}}}
–batchresponse_8a3589a4-81ec-46a9-a2d8-060b2a09a147–
I suspect one of two things, either there’s something wrong with the list name or there’s something wrong with the mapping of values in the select action.
I checked the name of the site and list and they are both good. Is it possible for SharePoint to have a different name for a list if I recently changed the name?
I checked the value mapping in the select action. I made sure all the values are the correct type and this shouldn’t be difficult because I’m creating in an archive list that is a copy of the original list where the Get items action is pulling from. I also tried a couple variations on the choice column names, like making sure the Status Value value is labeled with the Status/Value label it’s given in the original Get items action.
But I’m still getting the same error. Do you have a better idea what may be wrong?
Thank You,
Paulie says
I have no idea what the error means really. If you exclude the status column completely does it then work?
Tyler says
I figured out what the specific issue is.
I was able to get this to work on a test list on a different SharePoint site in the collection with a siteAddress set to “https://metropolitanministries.sharepoint.com/sites/ProcessImprovementTeam/”. The flow just won’t work when I try to use the site that is the homepage for the SharePoint collection where the siteAddress is “https://metropolitanministries.sharepoint.com/”.
I don’t know yet if there is a way to alter the flow to get it to work with the different siteAddress structure of the homepage.
GG says
Works great, but underscores in SP list names should be replaced as well else you get the type resolution error from comments above. This worked for me:
json(concat(‘{“type”:”SP.Data.’, replace(replace(outputs(‘settings_2’)[‘listName’], ‘ ‘, ‘_x0020_’), ‘_’, ‘_x005f_’), ‘ListItem”}’))
AK says
Hi Paul,
This is awesome. One query I have is “Can we set the item permission when bulk creating items. Like give edit access to a particular email I’d.”
Or maybe is there a similar way to bulk update the list item permissions.
Thank you so much as always.
Regards,
AK
AK says
Hi Paul, This is awesome. One query I have is “Can we set the item permission when bulk creating items. Like give edit access to a particular email I’d.”
Or maybe is there a similar way to bulk update the list item permissions.
Thank you so much as always.
Regards,
AK
AK says
Hi Paul,
This is awesome. One query I have is “Can we set the item permission when bulk creating items. Like give edit access to a particular email I’d.”
Or maybe is there a similar way to bulk update the list item permissions.
Thank you so much as always.
Regards, AK
Paul Graham says
You have changed my life and the way I use Power Automate. I am very very grateful x
Paulie says
Paul – Glad to be providing a life changing service!
john good says
Thanks for these post! I was able to use both delete list and create list with sql server connections. went from 3 hours to 8 minutes.
Paulie says
Wow – 3 hours to 8 minutes is a seriously good result!
Shahar says
Hey Paulie,
Thanks for this great recourse, I got a question:
What if I want to let the user to upload a file via power app (or form) and from there to dynamically “translate” the excel file to SharePoint list, would it be possible with this method?
Amit Lohogaonkar says
great! can this be used for updating files in different libraries in same site?
X.S says
I am trying o import 9725 items from SQL to SharePoint but I am having to issues, one the final count of items is 8632 and the flow apparently runs but on the SendBatch step I am getting this error:
{
“status”: 400,
“message”: “The maximum number of bytes allowed to be read from the stream has been exceeded. After the last read operation, a total of 1055925 bytes has been read from the stream; however a maximum of 1048576 bytes is allowed.\r\nclientRequestId: ee1b1838-7a91-47fe-bbf5-3c683d9038f9\r\nserviceRequestId: 32200ba0-4073-1000-5c68-3c147bfbadd6”,
“source”: “https://xxsite.sharepoint.com/sites/xxxx/_api/$batch”,
“errors”: [
“-1”,
“Microsoft.Data.OData.ODataException”
]
}
Any suggestions?
Ha Le says
got it to work. went from ~1h 50min to around 16 minutes…. biggest time saver ever!
Jesslyn says
Thank you so much for both the Delete list and Update list flows. We are migrating from SharePoint server where our large lists worked, but not so much in SharePoint Online. These flows are lifesavers.
zo says
Running into the same issue X.S. is, anyone have a solution?
tahsin says
Hello,
I’m trying to import example List with 3 Rows.
ListName is = testBatchImport
__metadata= json(concat(‘{“type”:”SP.Data.’, replace(outputs(‘settings’)[‘listName’], ‘ ‘, ‘_x0020_’), ‘ListItem”}’))
I got following Bad Request:
–batchresponse_46e39e77-1f85-42de-8096-5c0fbfd87563
Content-Type: application/http
Content-Transfer-Encoding: binary
HTTP/1.1 400 Bad Request
CONTENT-TYPE: application/json;odata=verbose;charset=utf-8
{“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”de-DE”,”value”:”Ein Typ mit dem Namen \”SP.Data.testBatchImportListItem\” konnte vom Modell nicht aufgel\u00f6st werden. Wenn ein Modell verf\u00fcgbar ist, muss jeder Typname in einen g\u00fcltigen Typ aufgel\u00f6st werden k\u00f6nnen.”}}}
–batchresponse_46e39e77-1f85-42de-8096-5c0fbfd87563
Content-Type: application/http
Content-Transfer-Encoding: binary
Any suggestions?
zo says
@tahsin, capitalize the ‘t’ in your ListEntityType path to read SP.Data.TestBatchImportListItem
tahsin says
@zo, I capitalze the ‘t’ in my ListEntityType path but still having bad request.
–batch_e10aee85-3362-44e0-9fb1-7ecd3d917e16
Content-Type: multipart/mixed; boundary=”changeset_29189f5f-ccfc-41ce-9161-f4d0d3ae7798″
Content-Length:
Content-Transfer-Encoding: binary
–changeset_29189f5f-ccfc-41ce-9161-f4d0d3ae7798
Content-Type: application/http
Content-Transfer-Encoding: binary
POST https://arfmann.sharepoint.com/sites/IFARotorionDigitalerSCMKollege/_api/web/lists/getByTitle('TestBatchImport‘)/items HTTP/1.1
Content-Type: application/json;odata=verbose
{“__metadata”:{“type”:”SP.Data.TestBatchImportListItem”},”A”:”1″,”B”:”1″,”C”:”1000″}
#.2 row
#.3 row
–batchresponse_22ed63a5-7aa4-4e37-8deb-87fba8e17e0b
Content-Type: application/http
Content-Transfer-Encoding: binary
HTTP/1.1 400 Bad Request
CONTENT-TYPE: application/json;odata=verbose;charset=utf-8
{“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”de-DE”,”value”:”Die Eigenschaft \”A\” ist f\u00fcr den Typ \”SP.Data.TestBatchImportListItem\” nicht vorhanden. Stellen Sie sicher, dass nur Eigenschaftennamen verwendet werden, die im Typ definiert sind.”}}}
–batchresponse_22ed63a5-7aa4-4e37-8deb-87fba8e17e0b
Content-Type: application/http
Content-Transfer-Encoding: binary
Max1981 says
Hello together, hello Paul,
first: Paul, thanks a lot for this post. I saw it last year but I didn’t had the time to rebuild my process. So today I finally used your “template” and the time saving is create.
I’m using the PowerPlatform/PowerAppsForAdmin-Management connectors to save information in SharePoint. Unfortunately, the batch runs into in error if I’m using batches in size 1000:
Content-Type: application/http\r\nContent-Transfer-Encoding: binary\r\n\r\nHTTP/1.1 500 Internal Server Error\r\nCONTENT-TYPE: application/json;odata=verbose;charset=utf-8\r\n\r\n{\”error\”:{\”code\”:\”-2146233083, Microsoft.SharePoint.Client.ClientServiceTimeoutException\”,\”message\”:{\”lang\”:\”en-US\”,\”value\”:\”The operation has timed out.\”}}}\r\n–batchresponse_d404c9a9-b2c5-48d8-ae29-41ba2a92c4b9–\r\n”
So I reduced it to the size 200 and it works fine now. So I will play around with it and also try to get information if the response has an error in it.
Maybe this information is helpful for someone.
Jesslyn says
Thank you again for this flow. I am having success on one list, but get this error:
A type named ‘SP.Data.IPT_x0020_PE/VC_x0020_ListListItem’ could not be resolved by the model. When a model is available, each type name must resolve to a valid type.\
on another list. I can’t for the life of me figure out what is going on.
??????? ???????? says
Hello Paul, I am working in a project as a junior and I wanted to try your method, but it didn’t work out. Flow ran successfully, but there’s no change in my list, the records never show up, I’ve already checked everything and I can’t setting (( please help me, I’d appreciate it. In my excel file I have 15000 record, but ‘Do until’ have 3 steps (variable LoopControl – 1000 first step, 24 – second step and 0 ) I changed Setting and GenerateSPData (besides __metadata / json(concat(‘{“type”:”SP.Data.’, replace(outputs(‘settings’)[‘listName’], ‘ ‘, ‘_x0020_’), ‘ListItem”}’))
Jesslyn says
I thought I would follow up on the errors that I got in case it would be of some help to others. It seems that the code does not like certain symbols in the column names. I had to replace both ‘/’ and ‘:’. I was having inconsistent results, either those columns were returning null values or the code threw errors. Since the SharePoint list displayed those symbols, it really didn’t matter what the Excel file shows (for us) so we’re good
Vitali Varabyeu says
I don’t have any spaces and so on in column naming
Josh Butcher says
That’s a brilliant solution! If I have a source Excel file with uo to 10k rows, does this solution handle it as-is or should I loop the List Rows action + this batch create (e.g. 1k items)? How did you get to this answer; really?
Paulie says
The example flow in the post should do exactly what you want. Bring in all 10k rows from Excel in one action and then use a do until loop to create the items in batches of 1,000. It does depend how many columns there are in your source Excel file though. If it is very wide you might need to reduce the batch size a little bit to prevent timeouts.
Alex L. says
At the SendBatch action, I got this error message”: “The maximum number of bytes allowed to be read from the stream has been exceeded. After the last read operation, a total of 1055877 bytes has been read from the stream; however a maximum of 1048576 bytes is allowed.(…)”
Any idea of how to fix this? My excel file only has about 7K rows.
Paulie says
It must be the number of columns pushing you over the size limit then. If you look in the settings action there is a batch size setting. Try reducing it to 750 and see if it works then.
Alex L. says
@Paulie, thank you so much for writing this article and for your advice about reducing the batch size. I don’t get the over-the-limit error message anymore, and my flow takes 8 mn and 20 s for ~ 7.6k rows.
I also want to thank @Jesslyn for recommending to remove symbols from the column titles.
Once again, thank you very much!
Valdeir Barbosa says
Regards Paul, thank you for deliver us this content really appreciated that!!
When I try to run my flow I got the message “The ‘from’ property value in the ‘select’ action inputs is of type ‘Object’. The value must be an array.” I’m start in to learn a batch ways a few days, please can you help me to figure out where I missed, please ?
Avik says
Hi Paul,
Thank you as always.
I wanted to ask how can we batch create items in a sharepont list where one column is a “people” column and in the excel I have a column with email ids.
Can you guide to bulk update this “people” column.
Regards,
Avik
Gianluca M. says
Very cool. Should also reduce the number of API calls, right? Is it 1 API call for each 1’000 items batch? That would be an ENORMOUS advantage, apart from being fast!
Richard Davis says
Great post. I am trying to apply this method to another upload process but am getting this error message:
{“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”A type named ‘SP.Data.UserApplicationAccess_TestListItem’ could not be resolved by the model. When a model is available, each type name must resolve to a valid type.”}}}
I am a newb so not exactly sure where I went wrong, Any help or direction would be greatly appreciated. Thanks
Richard Davis says
Followup.. I found the issue, replacing the underscore.. something so simple!
David says
Hi Paul,
This is really a great time-saver, thank you so much. I attempted to use the “My Clipboard” option to paste your code, but for some reason, it doesn’t provide an action/operation choice to paste to. Anyway, I was able to get it to work by reviewing the “peek code” with your code. The Flow only added two records to my Sharepoint list. I looked at the “SendBatch” operation output and it shows the following errors for all the other records to be loaded (998): Invalid JSON. More than one value was found at the root of the JSON content. JSON content can only have one value at the root level, which is an array, an object or a primitive value.” Any ideas on what is causing this? Thank you,
David says
Hi Paul,
I realized my error…it was adding an “O” instead of “0” in the %0A batchData input. What was taking 1 to 2 hours is now down to 10 minutes…amazing!
Paulie says
Well done David – yes, it’s a great way to boost performance.
Will says
Thank You So Much! This was very helpful and what I needed for a list I need to delete and reupload daily but the calls were too high.
I’m not an IT person and just learning these flows for work. It took me hours trying to get a batch create to work so I gave up. Then I saw the batch update and the flow had some extra details that allowed me to figure out the issue of how this works, and got the flow working and I think it could help make it easier for others to use.
Issue: Sharepoint List Display Name vs Web Address Name
The list name from Settings is used in multiple places. However, the formatting has to be adjusted based on the location it is used. In the batchTemplate the SPList name needs to be regular display name. In the GenerateSPData _metadata the needs to be the web address name. OP tried to help the _metadatafield by adding some replace values for underscores, but that only works if the name never changed.
If a Sharepoint list is created with the initial name “InitialSharePointName” and then the display name is changed to “New Display Name”, then it will never call the correct webaddress since the webaddress doesn’t change from initial name just like column names.
Solution to first issue: Add a second row in the Settings and differentiate the sharepoint display name and the sharepoint webaddress name.
Original Settings Format:
{
“siteAddress”: “https://OrgName.sharepoint.com/sites/SiteName/”,
“listName”: “ExampleListName”,
“batchSize”: 1000
}
New Settings Format:
{
“siteAddress”: “https://OrgName.sharepoint.com/sites/SiteName/”,
“SPListDisplayTitleName”: “(Copy Sharepoint Name from List Settings)”,
“SPListWebAddressName”: “(Copy list name section from the Web Address section in List Settings”,
“batchSize”: 1000
}
After Changing the settings you have to go into where they are used and update the expressions.
1. Batch Template – change the output of getByTitle in the PATCH statement to: PATCH @{outputs(‘settings’)[‘siteAddress’]}_api/web/lists/getByTitle(‘@{outputs(‘settings’)[‘SPListDisplayTitleName’]}’)/items(|ID|) HTTP/1.1
2. Generate SP Data – change the _metadata value field to contain json(concat(‘{“type”:”SP.Data.’, replace(replace(replace(outputs(‘settings’)[‘SPListWebAddressName’], ‘_’, ‘_x005f_’), ‘ ‘, ‘_x0020_’),’-‘,’_x002d_’), ‘ListItem”}’))
I hope this helps some people because this flow really helped me get what I needed and saved a massive amount of calls.
Paulie says
Nice one William – thank you for the useful comments too.
Mark says
Hi Paul,
I love your blog; you have helped me (without knowing) so many times before and now I’m back, to try my hand at your batch create flow.
I have chosen the threaded version of the flow for performance reasons.
When I replace the excel fields using the dynamic pick list at the Select Action called ‘GenerateSPData’, and despite having a unique key/value pair in the dataset.
Power Automate is ‘helpfully’ creating an Apply to Each loop. I could create a ‘first’ expression to get around this but wonder if there is something that I am missing?
Spoorthy says
Hey Paul! Thanks for the blog! Its great! I ran the Power automate flow and it worked perfectly fine. But i have done the same in the Logic apps and i get {“code”: “-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,
“message”: {
“lang”: “en-US”,
“value”: “Invalid request.”
} error.
Output of Batch:
–batchresponse_14847879-408c-4477-a6f7-279c15af55ae
Content-Type: application/http
Content-Transfer-Encoding: binary
HTTP/1.1 400 Bad Request
CONTENT-TYPE: application/json;odata=verbose;charset=utf-8
{“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”Invalid request.”}}}
–batchresponse_14847879-408c-4477-a6f7-279c15af55ae
Content-Type: application/http
Content-Transfer-Encoding: binary
Any Idea why i get this issue?
Oliver says
Hello! This is fantastic, and i’ve been able to implement the ‘delete batch’ function with great results.
I have a question about the create though – will this work if you pass in JSON as the data source? I tried and ran into some errors when it came to the creating list items.
My data source pathway is: .CSV file > parse to JSON > currently I have it writing line by line to a Sharepoint list (roughly 3000 entries, takes 45-75 minutes), I would love to do this in a batch but can’t get it to work!
Paulie says
You coming to Scottish summit this weekend? If so I will help you in person?
Oliver says
I’m not coming unfortunately!
Yi says
i don’t know why it is not generate output, flow run succssfully. not it does not create items in the list as output.
GOP says
Hi, many thanks for this article.
It is working on my side, except for lookup column.
I tried many ways to construct the JSON sent to HTTP and none accept my formatting for a lookup column
{ “formValues”:
[{“FieldName”: “TheLookupFieldName”,”FieldValue”:”TheValue”}],”bNewDocumentUpdate”: true}
If you have an idea….
(Batch insert using rest with lookup columns)
Thx
Robin Roy says
Hi,
Can you tell me how we can populate a person or group column using batch update?
Thanks.
Leonardo says
Thanks for the content.
Here the flow has no errors. But when executing no lines are added in sharepoint. When I go to check the steps after running, the “results” step gives the error:
Unable to process template language expressions in action ‘Results’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘base64ToString(body(‘sendBatch’)[‘$content’])’ cannot be evaluated because property ‘$content’ cannot be selected. Property selection is not supported on values of type ‘String’. Please see https://aka.ms/logicexpressions for usage details.’.
How can I solve this problem?
Edward says
Does it work for lookup and choice fields?
Pam says
Hi Paulie,
Thank you for this article, it has been very helpful.
I have tried to implement this script for a flow I am building for my organization, but I keep encountering a problem at the Generate SPdata item. Whenever I add try mapping the columns, I keep getting a “apply to each”.
The only difference is I am using a SQL server data source, and all my columns are string data type.
I am new to Power Automate and trying to learn as I progress, but this has been a thorn in my flesh a few days now, if anyone can help with a solution as I can’t find any single case where SQL server is used for a bulk update of SharePoint list.
Paulie says
Happy to help you out, can you use the get in touch form or connect on LinkedIn please?
Kate says
Hi Paul,
Thanks for your article. It’s really helpfup for new developer of automate like me.
I was trying to post multiple items to sharepoint list, and the flow run successfully.
But the items were not created in sharepoint list.
I run the send batch action with the body in another flow, but get the error message said :
“A supported MIME type could not be found that matches the content type of the response. None of the supported type(s) ‘multipart/mixed’ matches the content type ‘application/json; charset=utf-8’.”
Is it possible to give me some hint?
Fernando says
Hi Paul,
I tested the flow, and it works, but only create 5000 items (from 7k).
What could be the problem?
Thanks for your help.
Dalian says
Hello,
I tried both methods (threaded and non) I get all steps greens but inside the ‘Results’ step I get
HTTP/1.1 400 Bad Request
{“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:{“lang”:”en-US”,”value”:”A type named ‘SP.Data.Duplicate_Reference_just_testListItem’ could not be resolved by the model. When a model is available, each type name must resolve to a valid type.”}}}
Dalian says
Underscore was the issue in the list name for me.
json(concat(‘{“type”:”SP.Data.’, replace(outputs(‘settings’)[‘listName’], ‘_’, ‘_x005f_’), ‘ListItem”}’))
Aaron says
Has anyone managed to get past the issue when changing the Select action GenerateSPData to draw from your own data source, Automate is adding an ‘Apply to each’ that cannot be deleted?
Running the Flow with the Apply to each (I think) is causing an error ‘The maximum number of bytes allowed to be read from the stream has been exceeded.’
Ali says
Hi Paul,
First of all thank you for providing this amazing workaround, cut my time from 3 hors to 15 minutes.
When preparing excel data in GenerateSPData, how can we send a hyperlink? what should we put in @item…..?
Peter says
Done! After a few hours looking for a stupid detail in my target list it finally works! My monthly import is now down from 24h+ (because of to many api-calls) to a few minutes (still optimizing).
Thank you very much!
PS: I had error-code 400 (i think because of a “t” instead of a “T”) and 500 “SharePoint.SPListDataValidationException” because of a column with conditions, which i could remove
Terri says
This will be really helpful if I managed to get it to work. I downloaded and imported your solution to my workflow. However, I keep getting this message below. The workflow runs successfully but it doesn’t create the item.
“body”: {
“error”: {
“code”: “-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,
“message”: {
“lang”: “en-US”,
“value”: “An unexpected ‘PrimitiveValue’ node was found when reading from the JSON reader. A ‘StartObject’ node was expected.”
}
}
},
“statusCode”: 400
}
}
},
Can you help me out?
Ivo says
Hello! I’m having this issue when trying to send batch to the list:
“body: {
error: {
code: “-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,
message: {
lang: “es-ES”,
value: “El patrón no pudo resolver un tipo con el nombre ‘SP.Data.Proyectos_x0020_AsignacionesListItem’. Cuando hay un patrón disponible, cada nombre de tipo se debe resolver en un tipo válido.”
}
}
}”
Could you please help me?
Thanks!
Seweryn says
Hi ,thanks for all ,really goes to much faster, but just a question, i have trayed with a excel and only imports 568 records from 2000… Flow takes 6 minutes to end and with error, but not all records have been imported, any idea?
Thanks again!
Udula Sachithra Jayawardena says
I’m utterly amazed by how speedy it is. Few seconds, and I have 1000 records in a SharePoint Lists. I encountered multiple errors and managed to resolve them all. Now I’m trying to a run a load test by uploading 600,000 records to see how it fares. But to my dismay, the flow succeeds, and only uploads 791 records. What could possibly be the reason?
Mariette says
Hi Paulie,
Thank you for this, very helpful. I am not receiving any errors, but I only retrieve 1024 rows (4 columns). The pagination for List Rows present in a table is switched on with value 1000. Do I have to implement a Loop Until or do you have any other suggestions? Thank you
Matt says
Is anyone else having an issue with it not loading all of the rows. For instance, I have run it several times on different lists of data and on the last one, it loaded only 46 of the 50 rows but not reason as to why it left off the others.
vitss says
Excellent post!!!
Like some others received an error:
{“error”:{“code”:”-1, Microsoft.SharePoint.Client.InvalidClientQueryException”,”message”:
{“lang”:”nl-NL”,”value”:”A type named ‘SP.Data.ListItem’ could not be resolved by the model. When a model is available,
each type name must resolve to a valid type.”}}}
Solved that by making a new list without special characters like underscore,
suddenly it worked like a charm.
Paulie says
Thanks for the kind words, glad you found it useful.
Michael says
Hi Paulie,
Many thanks for this excellent post!
I have an error when I want to create items with lookup columns.
How can we populate a lookup column with this method ?
Thanks in advance !