This blog post will cover how to batch insert items in SharePoint with Power Automate. Our Flow will explore the SharePoint REST API calling the Batch endpoint and inserting 1000 items per time. For every Batch request made, you can add multiple Changesets up to 1000 Changeset requests. Likewise, for each Changeset, you can only add up to 1000 requests. That’s why our example will try to insert 1000 items per request just to make things simple.
In the tests I have made, using this Flow we were able to insert 5000 items in 16 minutes. All of my tests were executed in a Free Microsoft Flow account. In the other hand you probably may experience a better performance if you have a licensed account. Consequently, using a licensed account, you will be able to read more than 5000 rows.
In addition to that, it’s important to say that our test is going to read data from an Excel file. Everything will be static like the filename and the table name from the file. But this doesn’t mean that you cannot do the same by passing dynamic values. If you create a flow that runs tied to a file creation event in SharePoint, you will get the data needed to make it dynamic and save your time with this automation.
First steps on the Flow
In this example I chose to manually trigger a flow, just for it being easy to test. Needless to say tat you can change that for whatever you want.
After that we added four Initialize variable actions for different purposes:
- NewLine: simple string where I pressed enter to create a New line ‘\n’;
- BatchRequestArray: array variable initialized with an empty array ‘[]’;
- MustStop: boolean variable initialized with false;
- SkipItems: integer variable initialized with ‘0‘.
Right after that you can add a List rows present in a table action that reads data from a Excel table. In our example this file contains more than 5000 rows with three columns containing text and numeric data:
- NUM: column holding some numeric data;
- VALOR: column holding some other random numeric data;
- TIPO: column holding some text data;
In the settings of the Excel action you must enable pagination and define a threshold. This is limited to 5000 rows in free Microsoft Power Automate accounts, but you can go up to 100.000 rows in licensed Power Automate accounts.
Once the Excel action is configured you can go ahead and add a Scope where we are going to nest the main part of our logic. The logic will be inside the Do until loop like in the image below:
The condition for our Do until action will be the variable MustStop to be equal to true.
At the beginning you must set the variables again, defining MustStop as true and the BatchRequestArray to an empty array.
Adding the header of the Batch Request
After that we compose some data as Batch ID and Changeset ID. Just to make it clear, these two compose action results are going to be referenced a lot in the next steps. Also we will compose the beginning of our batch request using the Batch ID and the Changeset ID:
--@{outputs('Batch_ID')}
Content-Type: multipart/mixed; boundary="@{outputs('Changeset_ID')}"
Content-Transfer-Encoding: binary
Once you have composed the beginning of the batch request, you can append it to the BatchRequestArray:
Building the batch command for inserting each item in SharePoint
Now that we have started the batch request, we are going to prepare every one thousand requests using an Apply to each action. The rows that are being iterated and grouped in the requests are paginated. This is done by a combination of functions that skip a certain number of elements based on the SkipItems variable and take the next 1000 :
take(skip(body('List_rows_present_in_a_table')?['value'], variables('SkipItems')), 1000)
The Compose Changeset request is the main body of our command that performs Batch insert items in SharePoint with Power Automate. This is where we are going to compose the request using the Changeset ID and by calling the AddValidateUpdateItemUsingPath function from SharePoint REST API combined with the name of a specific list (example is BatchImportList).
The item field values will be defined in form values following a pattern of FieldName/FieldValue and using the data coming from our Apply to each function who references data from the Excel file. Each field name must exist in the target list and the field value must match the data type expected by that SharePoint List. Looking at our example you will understand that we are trying to write a JSON object using this data.
--@{outputs('Changeset_ID')}
Content-Type: application/http
Content-Transfer-Encoding: binary
POST https://wiliammbr.sharepoint.com/_api/web/lists/getByTitle(%27BatchImportList%27)/AddValidateUpdateItemUsingPath() HTTP/1.1
Content-Type: application/json;odata=nometadata
Accept: application/json;odata=nometadata
{"formValues": [{"FieldName":"Title","FieldValue":"@{items('Apply_to_each_1000_rows_from_Data_Source')?['NUM']}"},{"FieldName":"Email","FieldValue":"@{items('Apply_to_each_1000_rows_from_Data_Source')?['VALOR']}"},{"FieldName":"UUID","FieldValue":"@{items('Apply_to_each_1000_rows_from_Data_Source')?['TIPO']}"}],"bNewDocumentUpdate":true}
Important notes:
- the POST, the URL and the HTTP version should be in the same line;
- trim any white space in the end of each line or you will face an error message like “The request line ‘POST’ is invalid. The request line at the start of each operation must be of the format”;
- the blank lines should be respected as per the sample below;
- the Site Collection and Web should be present on the URL to the list;
- the list field names should be internal names, not the titles or SharePoint will say those columns do not exist;
- the list title should be used, not the list name. Pay attention to those differences or SharePoint will say this list doesn’t exist.
After composing that request, we are going to append it to the BatchRequestArray and then set the MustStop variable to false. This last step is very important! This is because the Flow uses that variable as a condition for checking if it missed any rows inside the Excel file that were not imported. If it finds, them it will try to loop again in the Do while loop.
Closing the batch command before sending the request
After the Apply to each command ends for one thousand rows, it will append the values that closes our Batch request:
--@{outputs('Changeset_ID')}--
--@{outputs('Batch_ID')}--
Finally we will join every row in our BatchRequestArray, so we can compose the entire request by separating them by a New Line character.
Request:
@{join(variables('BatchRequestArray'), variables('NewLine'))}
Making Batch request to SharePoint in Power Automate
Once the Request is ready we can go ahead and call the Batch endpoint from SharePoint using these 1000 rows we just prepared:
- The Content-Type header must use the Batch ID:
multipart/mixed; boundary=@{outputs('Batch_ID')};
- The Body will be the Output of the Compose function Request:
At the end of the loop we are going to increment the SkipItems variable to more 1000 rows. So the next time that part of the Flow runs, the loop will iterate and it will skip these last 1000 elements and it will try to import and create the next 1000 items inside the Excel file until every row is processed.
The complete flow after it executes successfully will look like this:
Complete flow
That’s it! In short words, I hope you found this post useful when working with SharePoint lists and a lot of data. Sometimes this will come in hand when you need to batch insert items in SharePoint with Power Automate. Also you may have interest on checking my post about Batch delete items in SharePoint with Power Automate.
References:
Microsoft documentation
We are doing a bulk update to SharePoint from PowerAutomate. We are sending a batch of 1000 records. The batch is running in parallel. We are trying to create around 2500 records. It works fine for the first run and gets completed in less than 15 mins. But for second or third run the flow is taking more than 2 hrs to complete. Sometimes it takes around 5-6 hrs to compete. Do you know what could be happening?
This is a good question because Power Automate has some performance problems indeed. Well, something that I can suggest you try to reduce as much as you can any variable operation, trying to work with Compose whenever it’s possible. Those variable operations usually generate performance issues in concurrent scenarios.
Hi William,
I am getting below error:
{
“status”: 400,
“message”: “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’.\r\nclientRequestId: f7c91eca-85b3-4d5e-99d8-5be7fa5dc119\r\nserviceRequestId: 9f27cd9f-9056-c000-257e-0ef62d0631a8”,
“source”: “https://bakerhughes.sharepoint.com/sites/TPSDJLOperationalToolsDevelopment/_api/$batch”,
“errors”: [
“-1”,
“Microsoft.Data.OData.ODataContentTypeException”
]
}
Hello Padmaja Madala!
Sometimes it happens by some unknown reasons, but we need to check carefully the final request. I would suggest checking the Field names of the list and compare with the ones you are using in your request. If they don’t match, SharePoint will throw an error that will be interpreted as JSON.
Would you mind sharing a quick sample of the request result that is being built by your flow? Using that we can trace any issue, maybe an invalid JSON or malformed message.
Best,
Wiliam
Hi William,
Field names of the list and with the ones in the request are matching, still I am getting the same error.
I am unable to post the comment when I paste the Compose request output here.
Looks like there are some restrictions.
Is there any other way to share?
Best regards,
Padmaja
Let`s try to discuss that via LinkedIn then.
Can you please share the flow rather than pictures? It would be very helpful.
Hi William,
I have one question. Will this flow work for more than 2 lac items in a excel to sharepoint?
Hey Rohit!
Not sure what are lac items? Could you clarify it better for me please?
What is the code written in “compose end of the batch”
@Wiliam Rocha, same concern. You skipped “compose end of the batch” part. Please let us know.
And also am getting the below error:
The header ‘Content-Type’ was specified multiple times. Each header must appear only once in a batch part.
clientRequestId: bc8a595f-aff3-407c-8a61-1ea12af5912d
serviceRequestId: 72f16ba0-2077-1000-c157-793b8a028a47
Hello William,
I was wondering if you would be willing to export your flow so I can import it into my Microsoft Power Automate account.
If you also want to batch update a SharePoint list with external data, see this video:
https://youtu.be/l0NuYtXdcrQ
I followed same instructions but getting an error:
The message header ‘–changeset_2628f7a9-409a-4eab-a981-47f007e12a0c’ is invalid. The header value must be of the format ‘: ‘.
clientRequestId: 40186755-b55f-4f2e-bf15-076332db4045
serviceRequestId: 642fa6a0-8005-6000-6b79-0267d09d2390
Have you got the solution, even I am facing the same issue.
Hello William,
I have followed all the steps but the flow is working till the Until block just goes on & on. Nothing happens. can you suggest what would be the reason.