The new nightly release (0.3.31) of the new cross platform PnP.PowerShell has brought the ability to use batching. Creating hundreds of list items in a few seconds is now possible rather than having to execute each command individually to your SharePoint online site.
PnP.PowerShell is the new PnP PowerShell that is currently being built and will be released in Q1 2021. It is still being developed and is not production ready yet. It will replace the old SharePointPnPPowerShellOnline PowerShell module that only runs on Windows.
PnP.PowerShell is cross platform (Windows, Unix, Mac, Linux etc) and runs on PowerShell 7. Todd klindt has some good instructions on how to install it in a blog post here.
Currently batching only works for the commands Add-PnPListItem and Set-PnPListItem but I imagine it will be extended to other PnP.PowerShell commands.
I will show below two examples using Add-PnPListItem below, one using the new batching functionality and one not using batching.
Add 100 List Items (Using Batching)
You can see below the script I used to test the new batching functionality. I created a Batch variable using New-PnPBatch. Then added 100 List items using Add-PnPListItem and instead of committing them directly to the SharePoint online site I sent them (offline) to the $batch variable using the -Batch parameter. Then finally the batch is sent to SharePoint online altogether at the end of the script by using Invoke-PnPBatch.
1 2 3 4 5 6 7 8 9 10 11 12 13 | $list = Get-PnPList -Identity "Contact List" #Initialize Batch $batch = New-PnPBatch for ($i = 0; $i -lt 100; $i++) { #Add List Item To The Batch - 100 times Add-PnPListItem -List $list -Values @{"Title" = "Person $i"; "StaffID" = "STAFF-$i"; "TelephoneExtension" = "+4415308167$i"; } -Batch $batch } # Execute the batch all at once Invoke-PnPBatch -Batch $batch |
In the screenshot below I have run the script and we can see that using the batch functionality that it took 4.19 Seconds to add 100 list items.
The PnP.PowerShell PowerShell commandlets make use of REST API behind the scenes to communicate with SharePoint online and add the list items. I used Fiddler to do a network trace and view all the requests. You can see in the screenshot below that PnP.PowerShell groups all the commands into a handful of /_api/$batch requests and its takes seconds with very small amount of total requests.
Add 100 List Items (Without Batching)
I will now perform the comparison test which is adding 100 list items using Add-PnpListItem without using batching. The Add-PnPListItem command is run 100 times directly to the SharePoint list and the script has to wait for each Add-PnPListItem command to run before it can execute another.
1 2 3 4 5 6 7 | $list = Get-PnPList -Identity "Contact List" for ($i = 0; $i -lt 100; $i++) { #Execute 100 Add List Item Actions Seperately Without Using Batch Add-PnPListItem -List $list -Values @{"Title" = "Person $i"; "StaffID" = "STAFF-$i"; "TelephoneExtension" = "+4415308167$i"; } } |
Below I have added 100 list items normally (without batching and one at a time) which are instantly sent to the SharePoint list. This takes 42.77 seconds to add 100 list items.
You can now see below in Fiddler that 400+ REST API Requests were sent from PowerShell to SharePoint online and there were too many requests to include in one screenshot! There was lots of network traffic and each request took a notable amount of time which all added up.
Summary
So we can see there is quite a time difference between the two methods and batching is considerably quicker!
| Test | Seconds | Requests |
| Add 100 List Items with Add-PnPListItem USING batching | 4.19 Secs | 5 |
| Add 100 List Items with Add-PnPListItem NOT using batching | 42.77 Secs | 400+ |
I can imagine that this new batching functionality in PnP.PowerShell will help you considerably with time and traffic savings from batching. Imagine the time savings adding 1000+ items altogether as one in a batch in comparison to waiting for 1000 Add-PnPListItem commands to write directly to SharePoint online and execute one by one.
I believe batching will help with avoiding SharePoint Online throttling due to it cutting down the number & frequency of requests . The guidance for avoiding throttling mentions “Reduce the number of operations per request” so all the operations in your batch may trigger this but the guidance also mentions “Reducing the frequency of calls” so batching will definitely help with this.
I’m excited for the release of PnP.PowerShell and seeing the other innovations like this that have taken place.
You should include -Details parameter to the Invoke-PnPBatch command as this will let you know the status of each batch item.
For example, your list might have a column that enforces unique values and some items might fails to create but others will succeed.
Hi Ian
Hope you are well back in NZ :-)! Send on my regards to all at Information Leadership!
Lots changing with the nightly releases of PnP.PowerShell. Today there have been some improvements to Invoke-PnPBatch
* Updated Invoke-PnPBatch to fully execute a batch by default if one of the requests in the large batch throws an exception. Specify the -StopOnException switch to immediately stop after an exception occurs. The rest of the batch will be skipped where possible. See https://pnp.github.io/powershell/articles/batching for more information.
Perhaps another blog when it’s all stable (hopefully GA by the end of the week) with the new parameters!
Hey, yep, we are all well here, more concerned about all the people we know in the UK. Hope you are keeping safe.
I was using the batch item to also add users to a list. You can now create B2B users with Graph (beta) in Batch mode which is where I found the -Details is valuable to confirm the users were actually created or if there was an issue.
I’m trying to use this to add a large number of items (~1600) from a csv file to a SharePoint list. I typically do this by putting the csv contents into a variable ($students) and a ForEach-Object command mapping columns in the csv to list columns. I can add them to a batch, but when I invoke that batch, I get an output ErrorMessages of “Invalid file name. …” for each row. I’ve also tried hard-coding values. I’m not sure what file name is invalid. Any ideas?
Great Article!