How to use Power Automate and the SharePoint Batch API to create thousands of list items quickly 

Thanks Matthew - I have added my Twitter link!

Fantastic! Well done! It is a powerful solution

Thank you Adela, I really appreciate the kind words. Makes creating the videos so much more worthwhile getting feedback like this.

Hi Juan, I’ve looked around and haven’t found a bulk update SharePoint set-up yet. However, I recently implemented a work-around that will keep over 100k records up to date. I first use Sharepoint Get items with 100,000 pagination to get all the rows in the table as they currently are. I get the many items to update from an external site, I use a Filter array to get just the ones I need to update in SharePoint. I use Paulie’s batch create method to create new rows for all the record updates (yes, there are now duplicate rows with the updated data). Then if the batch create is successful, I use a Select action to generate a unique key from a single or a combination of columns from the Filter array action. Let’s say the unique key is just email addresses. Then I use another filter array with the SharePoint Get items value in the From and the filter set to if String(Select) contains item()?[‘Email’]. That filters the SharePoint Get items to just the old version of the records just updated/duplicated in the batch create. Now I can use the results of that second Filter array in Paulie’s batch delete set-up. And by creating new versions of the records & deleting old versions, it replicates an update.

Glad it helped!

Hi i have same issue did you find any solution to create person or group columns please guide expective your response please !!

@@manjunathpatil6785 for updating person or group column you will need the ID of the user (this is specific to site collection). Email ID is not enough.

Yes, but it would be more complex as you’d have to consider the file sizes of the documents in the library - personally I’d be inclined to use a power shell script.

Yes, you can examine the output from SharePoint batch operation. But it’s not straightforward to do. I’ve got an enhanced method coming

Yes, I’ve done it (to be honest I thought I had done a video already, but seems not). It’s very similar and easy to do. I will try to do one soon.

Those are just the outputs of the settings Compose action. So the expression would be something like outputs('settings')['siteAddress'] or outputs('settings')['listName'].

@@PaulieM thanks! I'll give it another look at shortly. Appreciate the reply!

Update: I increased the threshold limit to 5000 and it loaded 5120 items but it still didn't go through the entire excel file of 15000 records.

I could to solve it... the problem was column name. But my excel has 2188 items, and API had copy only 1032 items (API no threads) and only 1023 items (API with Threads)... I don't know what it happened.

Any response would be appreciated thank you 🙏

I'm having this issue as well. My limit is 1048576 bytes, so 1 mb. I need to know how to up this limit.

I am also having this issue. Can you please give us a tip on how to solve it, Paulie?

pretty sure this is flow's internal size limit for variables, i've run into something similar when storing base64 images in strings, only way around it i found was smaller batches

I ran into the same issue. I wasn't able to get it to work by setting the for example Approver Field to the person I am wanting. However, I did found out that all people fields have a hidden field that you can use. You just have to add "Id" to the end of the field name. In my example I would use ApproverId and set that field the user sharepoint user Id. The Id will be an integer.

@@reggieoverton121 You Sir, are a hero! Been trying for hours to get this going and this fixed it. Thank you!

If you go into the settings of your Get Rows action you should be able to switch "Pagination" on, which will give you more than 5,000 rows.

@@PaulieM Thanks Paul. If you don't mind, I've another question: I tested your flow to upload 10,000 rows and 9,996 rows have been uploaded. There are 4 remaining rows that have not been uploaded and I don't know why. How can I find out? Thanks again.

The error messages are contained in the response from SharePoint. You can extract them from there (there is an error for each request in the batch)

You could break it down further (I have done this in my get more than 5,000 items video). I split it two ways just for simplicity - but it could be done for sure

I'm going to see if I can make a simple 8 threaded version. I think that would be a nice balance between performance and API actions.

I just tried it with 8 threads, instead of 2, it was marginally faster, 43 seconds to create 1,000 rows instead of 58. I think this is because SharePoint queues batch requests.

I've got it down to 35 seconds now to create 1,000 records. So that is a good improvement

@@PaulieM Hi, I am now increasing the amount of items being uploaded to 11,000, it worked fine with 4,000. But in the Get Items from Excel, it keeps stopping at 5120 items. I'm guessing I now now need to incorporate your 'get more than 5,000 items' video solution into this - is the correct?

That is actually step called scope. All need to do is copy the scope code, add a new step, go to my clipboard, and ctlr-v (paste) what you have copied. It will not show up so you have to use ctlr-v to do it.

Have you tried using a smaller batch size? If you have a lot of columns you could be going over the maximum request size. Try dropping the batch size down to 500.

I'm trying your threaded approach now on a different list, I reduced the batch size to 250 while keeping the pagination limit to 1000, I feel like I'm so close to cracking this. But still ends up with 1024 rows. Any idea what I'm doing wrong? I got the error "Request to Azure Resource Manager failed with error: '{"error":{"code":"ActionRepetitionNotFound","message":"The specified repetition '000005-000000' of workflow action 'Results' is not found."}}'."

Hi Paulie, I think I have answered my own question. I used an "if" expression along with 'null' in expression and it work. I'm sure I tried it before and it didn't work before, but there you go. Maybe it was the use of the Select that helped.

@@ajambo2010 well done!

@@ajambo2010 Hi, would you mind sharing which part did you use the if-null formula? is it on the 'GenerateSPData' part and use it for all columns?

@@arjaybernardo9501 Sorry for the belated reply. I had find were I did that. I put the IF statement in the right hand column of the GenerateSPData action. In the GenerateSPData the left column is the column name/title e.g. '"Material" and right hand column is the value for that column, e.g. "if(empty(item()?['Material']), null, item()?['Material'])". I did this for each row in the GenerateSPData action.

same concern. I noticed that batch template from the video is different. Cannot replicate because he did not show the syntax of the expressions

Hi. I got the same error, did you find the solution?

I intend to do a video/blog post on performing updates, I just haven’t had time to produce it yet. Coming soon!

@@PaulieM have you done an updating video yet?

Hi Rachita, The batch operation only accepts 1,000 records at a time. But you could process 50,000 records by looping around 50 times. Have you tried that?

hello Rachita, I've this same problem here! (10K items) did you manage to make the loop run more times?

@@GuilhermeAlves-zq2gc I just ran into this, but the default loop until count is 60. I bumped mine up to 1000 and should handle my 90K list.

I do - is it a required field or not?

@@PaulieM It is not a required field. It is a Closed_Dt field, so it will often be null.

@@adelawilliams2260 I do know a workaround but it’s tricky to explain - do you want to do a quick sharing session? If so can I record and put on RU-vid? 😆

@@PaulieM Would it be ok if I sent you screenshots of all my steps? I cannot share my work laptop screen because it is on a vpn. I do not have a personal power platform subscription I can use on my personal laptop.

@Adela Williams basically this is what I think you should do. In the select action that generates the data, wrap the date field name and field value around an if expression. So that if the date is empty, then the field won't even be included in the JSON. Do you know what I mean. If you want to screenshare - I can share *my* screen with you to show you what I mean.

Have you been able to fix this, if so, how did you do it?

I have the same problem Wolemberg, did you manage to solve this? And if so how did you do that? Thanks.

@@Johanvanderschelling I resolve just change the name of my list !! some characters like underscore, or a blank space force the metadata to rename a list with a valid caracters, so i change the name of the list without using spaces or "_" and the batch running ok. Hope help you !

@@wolenbergsilva it worked, thanks!

@@wolenbergsilva This works, thank you!

I found the issue while reading through the comments in your blog .. - it were the underscores in my list names.

@@kulski666 I had the same issue. Fixed by changing to a list without any special characters or spaces in the name.

Thanks for that, I thought I made a mistake in the original version, but see that it is fine. Thanks for the info.