Batch update via PNP Powershell

Question

I have data from a CSV that will be imported and added to column values for each of these items. I can loop over CSV, get the items, and set the values (Set-PNPListItem) - but the sequential operations take quite way too long.

Google-fu may be weak on this subject as I'm not finding much info, and I can't imagine this isn't an issue for others.

Is there a way to batch or queue up requests with the PNP CMDLETS? If not how are those of you that are doing it handling this?

Edit

---

I ended up getting CSOM library to work directly. This was what I ended up with - and it worked, so I'm good to go!

 $lastItemID = 0
    $counter = 0
    $csv = import-csv .\Downloads\slabtarget.csv | Sort-Object Id 
    foreach($item in $csv){
        if([Int32]($item.id) -le $lastItemID){
            Continue;
        }else{
            $listItem = $list.GetItemById($item.id)

            $listItem["Gallery"] = $item.Gallery
            $listItem["Branch"] = $item.Branch
            $listItem["Image"] = $item.Image
            $listItem["Product_Type"] = $item.Product_Type
            $listItem["Collection"] = $item.Collection

            $listItem.update()
            $lastItemID = $item.Id
            $counter ++
        }

        if($counter -eq 100){
            $context.ExecuteQueryAsync()
            $counter = 0
        }
    }

Answer 1

By default CSOM is batch update request so all the request are queued until execute query method is called.

Use Powershell SPO online commands to create new item but do not call execute query until you need it or until it reaches batch size. So for example

Below is sample script which runs on csv with two columns 'Title' and 'YesNo'. Tried and tested working fine. This will give you logic on how to implement this.

Below sample batches request for 3 items and then runs execute query. You can change if condition according to your requirement.

Add-Type -Path "C:\Siddharth\SPODLL\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Siddharth\SPODLL\Microsoft.SharePoint.Client.Runtime.dll"

$UserName = "[email protected]"
$Password = Read-Host -Prompt "Enter the password"    
$Url = "https://wayneenterprise.sharepoint.com/sites/justiceleague"


$context = Get-SPOContext -Url $Url -UserName $UserName -Password $Password
$list = $context.Web.Lists.GetByTitle("CustomList");
 $web = $Context.Web
$Context.Load($list)
$Context.Load($web)
$guid = $list.ID
$Context.ExecuteQuery()
#Write-Host $list.Title

$path = "C:\MYImportFolder\import.csv"
$index = 1;
Import-Csv $path -Header 'Title', 'YesNo' | Foreach-Object { 
$ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation 
$ListItem = $List.AddItem($ListItemInfo) 
#Set Column Values 
$ListItem["Title"] = $_.Title;
$ListItem["yes_x0020_no"] = $_.YesNo;
$ListItem.Update()
Write-Host $index;
if($index % 3 -eq 0) 
{
   Write-Host "divisible by 3";

   $Context.ExecuteQuery();
}
$index++;
}

$context.Dispose()

Answer 2

I don't think there is a way in PNP powershell to execute in batches currently.

You can implement techniques that can speed up your current script by implementing best practices for coding.

However , once i had this same issue and what I did was create multiple csv and run parallel threads of powershell to minimise the time .

It's not the best approach but it worked for me .

Answer 3

There is no way of batching PnP cmdlets. The Cmdlet runs and everything is "done" afterward.

Internally, PnP uses CSOM, which can be batched - up to a certain point (I am doing 200-300 delete operations or something like that but not too much).

If you really, really need batching, you can use the old SPWeb.ProcessBatchData-Method. However - it is SSOM!

I, myself, tend to write C# code, using CSOM and batch-update like so:

// open connection
// load "employees" in batches of 5000 using CAML and <RowLimit> into one single list "employeesToUpdate"
// modify all employees according to some "rules"
// now save:
var updates = 0;
foreach (var e in employeesToUpdate)
{
    e.Update();
    updates++;
    if (updates > 200)
    {
        logger.Info("Saving...");
        ctx.ExecuteQuery();
        updates = 0;
    }
}
if (updates > 0)
{
    logger.Info("Saving...");
    ctx.ExecuteQuery();
    updates = 0;
}

As you can see, above I chose to batch in packets of 200 - because 300 caused errors... I'm not sure how SharePoint determines how many "operations" can be made in one batch.

Answer 4

I recently developed a windows form which has similar Function, For this purpose, I Used C# and Developed a basic class. This class has several methods to deal with SP online list and it is written based on CSOM. You take advantage of this class and do whatever you want. Just several points: As I needed a fast solution It looks like spaghetti but it is a good starting point All the methods are tested and work well but they might need some error handling. Just use this as the starting point, it should give you a general Idea

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Net;
using Microsoft.SharePoint.Client;
using System.Globalization;
using System.Security;
using System.Threading;
using System.Configuration;
using System.Reflection;
using Microsoft.SharePoint;
using System.Collections.Specialized;
using System.Collections;

namespace SharepointListModify
{
internal class SharePointList
{
    private LogWriter createLog = new LogWriter();
    public SharePointList()
    {

    }

    public void CreateListItem(ClientContext clientContext,
                                string ListTitle,
                                Dictionary<string, dynamic> NewItem)
    {
            //Create Item In List  with values in Dictionary
        try
        {

            List oList = clientContext.Web.Lists.GetByTitle(ListTitle);
            ListItemCreationInformation listCreationInfromation = new ListItemCreationInformation();
            ListItem oListItem = oList.AddItem(listCreationInfromation);
            foreach (KeyValuePair<string, dynamic> item in NewItem)
            {
                string columnName = item.Key.Replace(" ", "_x0020_");
                oListItem[columnName] = item.Value;//oListItem["Title"] = "TestTitle";
            }

            oListItem.Update();
            clientContext.ExecuteQuery();
            createLog.CreateMainMessage("The Item Successfully Created.");
        }
        catch (Exception ex)
        {
            createLog.CreateMainMessage(ex.Message);
        }

    }

    public void CreateListItem( string SPUrl,
                                string UserName,
                                string Password,
                                string ListTitle,
                                Dictionary<string, dynamic> NewItem)
    {
        ClientContext clientContext = CreateClientContext(SPUrl, UserName, Password);

        try
        {

            List oList = clientContext.Web.Lists.GetByTitle(ListTitle);
            ListItemCreationInformation listCreationInfromation = new ListItemCreationInformation();
            ListItem oListItem = oList.AddItem(listCreationInfromation);
            foreach (KeyValuePair<string, dynamic> item in NewItem)
            {
                string columnName = item.Key.Replace(" ", "_x0020_");
                oListItem[columnName] = item.Value;//oListItem["Title"] = "TestTitle";
            }

            oListItem.Update();
            clientContext.ExecuteQuery();
            createLog.CreateMainMessage("The Item Successfully Created.");
        }
        catch (Exception ex)
        {
            createLog.CreateMainMessage(ex.Message);
        }

    }

    public void UpdateListItem( string SPUrl,
                                string UserName,
                                string Password,
                                string ListTitle,
                                string columnToFilterName,
                                string ValueToFilter,
                                Dictionary<string, dynamic> valuesToUpdate)
    {

        // Starting with ClientContext, the constructor requires a URL to the 
        //TODO to change Type to be effective For now only creates Generic List
        createLog.CreateMainMessage($"updating  a List  item in {SPUrl} with title:{ListTitle} and item name is {ValueToFilter}");
        ClientContext clientContext = CreateClientContext(SPUrl, UserName, Password);

        List oList = clientContext.Web.Lists.GetByTitle(ListTitle);

        CamlQuery query = new CamlQuery
        {
            ViewXml = string.Format("<View><Query><Where><Eq><FieldRef Name='{0}'/><Value Type='text'>{1}</Value></Eq></Where></Query><RowLimit>1</RowLimit></View>", columnToFilterName, ValueToFilter)

        };

        var itemsPh = oList.GetItems(query);
        clientContext.Load(itemsPh);
        clientContext.ExecuteQuery();

        foreach (ListItem oListItem in itemsPh)
        {
            try
            {
                foreach (KeyValuePair<string, dynamic> item in valuesToUpdate)
                {
                    string columnName = item.Key.Replace(" ", "_x0020_");
                    oListItem[columnName] = item.Value;//oListItem["Title"] = "TestTitle";
                }
                oListItem.Update();
                clientContext.ExecuteQuery();
                createLog.CreateSubMessage(string.Format("The Item Updated Successfully With All values."));
            }
            catch
            {
                //If we can not do it all at once Do it one cell by one

                createLog.CreateSubMessage(@"Unable to Update All Cell at same time Trying to Update One by one");
                foreach (KeyValuePair<string, dynamic> item in valuesToUpdate)
                {

                    try
                    {
                        string columnName = item.Key.Replace(" ", "_x0020_");
                        oListItem[columnName] = item.Value;//oListItem["Title"] = "TestTitle";
                        oListItem.Update();
                        clientContext.ExecuteQuery();
                        createLog.CreateSubMessage(string.Format("The Item {0} with {1} as value is Updated Successfully.", item.Key, item.Value));
                    }
                    catch (Exception ex)
                    {
                        createLog.CreateSubMessage(string.Format("Unable to Update {0} with {1} as value becaues;\n\r{2}", item.Key, item.Value, ex.Message));
                        //MessageBox.Show(string.Format("Unable to Update {0} with {1} as value:\n\r{2}", item.Key, item.Value, ex.Message));
                    }




                }

            }
        }




    }

    public void UpdateListItem(ClientContext clientContext,
                                string ListTitle,
                                string columnToFilterName,
                                string ValueToFilter,
                                Dictionary<string, dynamic> valuesToUpdate)
    {

        //makesure Column Name doesnt have space
        columnToFilterName = columnToFilterName.Replace(" ", "_x0020_");

        try
        {
            List oList = clientContext.Web.Lists.GetByTitle(ListTitle);

            CamlQuery query = new CamlQuery
            {
                ViewXml = string.Format("<View><Query><Where><Eq><FieldRef Name='{0}'/><Value Type='text'>{1}</Value></Eq></Where></Query><RowLimit>1</RowLimit></View>", columnToFilterName, ValueToFilter)

            };

            var itemsPh = oList.GetItems(query);
            clientContext.Load(itemsPh);
            clientContext.ExecuteQuery();
            createLog.CreateMainMessage("Updating SP From IAuditor.");
            foreach (ListItem oListItem in itemsPh)
            {
                foreach (KeyValuePair<string, dynamic> item in valuesToUpdate)
                {
                    try
                    {
                        string columnName = item.Key.Replace(" ", "_x0020_");
                        oListItem[columnName] = item.Value;//oListItem["Title"] = "TestTitle";
                        oListItem.Update();
                        clientContext.ExecuteQuery();
                        createLog.CreateSubMessage(string.Format("The Item {0} with {1} as value is Updated Successfully.", item.Key, item.Value));
                    }
                    catch (Exception ex)
                    {
                        createLog.CreateSubMessage(string.Format("Unable to Update {0} with {1} as value becaues;\n\r{2}", item.Key, item.Value, ex.Message));
                        MessageBox.Show(string.Format("Unable to Update {0} with {1} as value:\n\r{2}", item.Key, item.Value, ex.Message));
                    }



                }


            }

        }
        catch (Exception ex)
        {
            createLog.CreateMainMessage(ex.Message);
        }

        //MessageBox.Show("Updating Sharepoint From Iauditor Finished!");

    }

    public List<List<string>> ReadListToAray(   string SPUrl,
                                                string UserName,
                                                string Password,
                                                string ListTitle)
    {
        List<List<Dictionary < string,dynamic >>> test =new List<List<Dictionary<string, dynamic>>>();
        List<List<string>> SPListAray = new List<List<string>>();
        // This Method Retrives alll Sharepoint Lists in Website
        ///SPUrl is the website we want to Retrive Lists
        createLog.CreateMainMessage($"Creating List Of All List in {SPUrl}");

        List<string> SPListsFields = new List<string>();
        ClientContext clientContext = CreateClientContext(SPUrl, UserName, Password);



        List<string> Headings= RetriveSPListFields(SPUrl, UserName, Password, ListTitle);
        SPListAray.Add(Headings);





        List oList = clientContext.Web.Lists.GetByTitle(ListTitle);

        CamlQuery query = new CamlQuery
        {
            ViewXml = string.Format("<View><Query></Query></View>")

        };


        ListItemCollection itemsPh = oList.GetItems(query);
        clientContext.Load(itemsPh);
        clientContext.ExecuteQuery();


        foreach (ListItem oListItem in itemsPh)
        {
            List<string> ItemValues = new List<string>();

            foreach(string heading in Headings)
            {
                try
                {
                    ItemValues.Add(oListItem[heading].ToString());
                }
                catch { }

                //ItemValues.Add(oListItem["Title"]);

            }



            SPListAray.Add(ItemValues);

            ItemValues = null;
        }



        return SPListAray;
    }

    public List<string> RetriveSPLists( string SPUrl,
                                        string UserName,
                                        string Password)
    {
        // This Method Retrives alll Sharepoint Lists in Website
        ///SPUrl is the website we want to Retrive Lists
        createLog.CreateMainMessage($"Creating List Of All List in {SPUrl}");

        List<string> SPListsNameAray = new List<string>();
        ClientContext context = CreateClientContext(SPUrl, UserName, Password);

        // The SharePoint web at the URL.
        Web web = context.Web;

        // Retrieve all lists from the server. 
        context.Load(web.Lists,
                     lists => lists.Include(list => list.Title, // For each list, retrieve Title and Id. 
                                            list => list.Id));

        // Execute query. 
        context.ExecuteQuery();

        // Enumerate the web.Lists. 


        for(int i=0;i< web.Lists.Count; i++)
        {
            SPListsNameAray.Add(web.Lists[i].Title);

        }

        createLog.CreateSubMessage($"Creating List of Lists was successfull!");
        return SPListsNameAray;
    }

    public void CreateSpList(   string SPUrl,
                                string UserName,
                                string Password,
                                string ListTitle,
                                string Type,
                                string ListDescription="")
    {
        // Starting with ClientContext, the constructor requires a URL to the 
        //TODO to change Type to be effective For now only creates Generic List
        createLog.CreateMainMessage($"Creating  a List in {SPUrl} with title:{ListTitle}");

        List<string> SPListsNameAray = new List<string>();
        ClientContext context = CreateClientContext(SPUrl, UserName, Password);

        // The SharePoint web at the URL.
        Web web = context.Web;

        ListCreationInformation creationInfo = new ListCreationInformation();
        creationInfo.Title = ListTitle;
        try
        {
            ListTemplateType choice;
            if (Enum.TryParse(Type, out choice))
            {
                creationInfo.TemplateType = (int)choice;
            }
            List list = web.Lists.Add(creationInfo);
            list.Description = ListDescription;

            list.Update();
            context.ExecuteQuery();
            createLog.CreateSubMessage($"Creating  a List {ListTitle} was successfull!");
        }
        catch(Exception ex)
        {
            string ValidTypes = "";
            foreach (int i in Enum.GetValues(typeof(ListTemplateType)))
            {

                String name = Enum.GetName(typeof(ListTemplateType), i);
                ValidTypes = $"{ValidTypes}\"{name}\" \n ";

            }
            createLog.CreateSubMessage($"Unable to create List: {ex}\n\r Only the Folowing Values are Valid for Type: {ValidTypes}");

        }

    }

    public void DeleteSPList(   string SPUrl,
                                string UserName,
                                string Password,
                                string ListTitle)
    {
        // Starting with ClientContext, the constructor requires a URL to the 
        // server running SharePoint. 
        //createLog.CreateMainMessage($"Deleting  a List from {SPUrl} with title:{ListTitle}");


        ClientContext context = CreateClientContext(SPUrl, UserName, Password);
        try
        {
            // The SharePoint web at the URL.
            Web web = context.Web;

            List list = web.Lists.GetByTitle(ListTitle);
            //list.DeleteObject();//Never Uncommrnt, This Does not Move Object To Recycle Bin It will be removed Permanently 
            list.Recycle();

            context.ExecuteQuery();
            createLog.CreateSubMessage($"List {ListTitle} was successfully Deleted!");
        }
        catch (Exception ex)
        {
            createLog.CreateSubMessage($"Unable to delete List:\n\r{ex}");
        }
    }

    public void AddFieldSPList( ClientContext clientContext,
                                string ListTitle,
                                string FieldName,
                                string FieldType= "string")
    {
        //TODO Write this Method
    }

    public void DeleteSPListItem(   string SPUrl,
                                    string UserName,
                                    string Password,
                                    string ListTitle, 
                                    string ItemValue ,
                                    string FieldName="Title")
    {
        // Starting with ClientContext, the constructor requires a URL to the 
        // server running SharePoint. 
        createLog.CreateMainMessage($"Deleting  Item from List  {ListTitle}");
        ClientContext context = CreateClientContext(SPUrl, UserName, Password);

        //TODO Write this Method

        // Assume that the web has a list named "Announcements". 
        List announcementsList = context.Web.Lists.GetByTitle("Announcements");

        // Assume that there is a list item with ID=2. 
        ListItem listItem = announcementsList.GetItemById(2);
        listItem.DeleteObject();

        context.ExecuteQuery();



    }

    public List<string> RetriveSPListFields(    ClientContext context,
                                                string ListTitle)
    {
        // This Method Retrives alll Sharepoint Lists in Website
        ///SPUrl is the website we want to Retrive Lists
        createLog.CreateMainMessage($"Creating List Of All Fields ");

        List<string> SPListsFields = new List<string>();


        List list = context.Web.Lists.GetByTitle(ListTitle);
        context.Load(list.Fields);

        // We must call ExecuteQuery before enumerate list.Fields. 
        context.ExecuteQuery();

        foreach (Field field in list.Fields)
        {

            SPListsFields.Add(field.InternalName);

        }

        return SPListsFields;

    }

    public List<string> RetriveSPListFields(string SPUrl,
                                            string UserName,
                                            string Password,
                                            string ListTitle)
    {
        // This Method Retrives alll Sharepoint Lists in Website
        ///SPUrl is the website we want to Retrive Lists
        createLog.CreateMainMessage($"Creating List Of All fuekds in {SPUrl}");

        List<string> SPListsFields = new List<string>();
        ClientContext context = CreateClientContext(SPUrl, UserName, Password);

        List list = context.Web.Lists.GetByTitle(ListTitle);
        context.Load(list.Fields);

        // We must call ExecuteQuery before enumerate list.Fields. 
        context.ExecuteQuery();

        foreach (Field field in list.Fields)
        {

            SPListsFields.Add(field.InternalName);

        }

        return SPListsFields;

    }

    private void ChangeFolderName(  string SPUrl,
                                    string UserName,
                                    string Password,
                                    string ListTitle)
    {
        ClientContext clientContext = CreateClientContext(SPUrl, UserName, Password);

        List oList = clientContext.Web.Lists.GetByTitle(ListTitle);

        ListItemCreationInformation listCreationInfromation = new ListItemCreationInformation();
        ListItem oListItem = oList.AddItem(listCreationInfromation);


    }


    public void UpdateOnlieListFromOffline( string SPUrl,
                                            string UserName,
                                            string Password,
                                            string ListTitle,
                                            string CSVPathAddress,
                                            bool UpdateEmptyValue = false)
    {
        //This Method Gets a CSV With Same structure as Sharepoint List and Updates all the values
        ClientContext clientContext = CreateClientContext(SPUrl, UserName, Password);
        LoadFile loadFile = new LoadFile();
        string[,] ExistingDBData = loadFile.LoadCsv(CSVPathAddress);
        List<string> ListFields = RetriveSPListFields(clientContext, ListTitle);


        Dictionary<string, Dictionary<string, dynamic>> ExistingDBDataDic = new Dictionary<string, Dictionary<string, dynamic>>();
        for (int Row = 1; Row < ExistingDBData.GetLength(0); Row++)//It is Assumed Row 1 is title 
        {

            Dictionary<string, dynamic> ItemValues = new Dictionary<string, dynamic>();
            for (int Col = 0; Col < ExistingDBData.GetLength(1); Col++)
            {
                try
                {
                    if (ExistingDBData[0, Col] != null & ExistingDBData[0, Col] != "")//Make Sure Column name is not Emty
                    {
                        if (ListFields.Contains(ExistingDBData[0, Col]))//If Sharepoint Online List Have the title add to Dic
                        {
                            if ((ExistingDBData[Row, Col] != null & ExistingDBData[Row, Col] != "") | UpdateEmptyValue)//Only Update empty values when the setting requires
                            {
                                ItemValues.Add(ExistingDBData[0, Col], ExistingDBData[Row, Col]);//It is Assumed Row 1 is title 
                            }
                        }

                    }
                }
                catch { }
            }
            if (ExistingDBData[Row, 0] != null & ExistingDBData[Row, 0] != "")//To Make Sure There is Title(JNumber)
            {
                try
                {
                    ExistingDBDataDic.Add(ExistingDBData[Row, 0], ItemValues);
                    ItemValues = null;
                }
                catch { }
            }
        }


        foreach (var Item in ExistingDBDataDic)
        {
            UpdateListItem(clientContext,ListTitle, "Title", Item.Key, Item.Value);
        }


    }


    public void UpdateOnlieListFromOffline(ClientContext clientContext,
                                            string ListTitle,
                                            string CSVPathAddress,
                                            bool UpdateEmptyValue = false)
    {
        //This Method Gets a CSV With Same structure as Sharepoint List and Updates all the values

        LoadFile loadFile = new LoadFile();
        string[,] ExistingDBData = loadFile.LoadCsv(CSVPathAddress);
        List<string> ListFields = RetriveSPListFields(clientContext, ListTitle);

        Dictionary<string, Dictionary<string, dynamic>> ExistingDBDataDic = new Dictionary<string, Dictionary<string, dynamic>>();
        for (int Row = 1; Row < ExistingDBData.GetLength(0); Row++)//It is Assumed Row 1 is title 
        {

            Dictionary<string, dynamic> ItemValues = new Dictionary<string, dynamic>();
            for (int Col = 0; Col < ExistingDBData.GetLength(1); Col++)
            {
                try
                {
                    if (ExistingDBData[0, Col] != null & ExistingDBData[0, Col] != "")//Make Sure Column name is not Emty
                    {
                        if (ListFields.Contains(ExistingDBData[0, Col]))//If Sharepoint Online List Have the title add to Dic
                        {
                            if ((ExistingDBData[Row, Col] != null & ExistingDBData[Row, Col] != "") | UpdateEmptyValue)//Only Update empty values when the setting requires
                            {
                                ItemValues.Add(ExistingDBData[0, Col], ExistingDBData[Row, Col]);//It is Assumed Row 1 is title 
                            }
                        }

                    }
                }
                catch { }
            }
            if (ExistingDBData[Row, 0] != null & ExistingDBData[Row, 0] != "")//To Make Sure There is Title(JNumber)
            {
                try
                {
                    ExistingDBDataDic.Add(ExistingDBData[Row, 0], ItemValues);
                    ItemValues = null;
                }
                catch { }
            }
        }


        foreach (var Item in ExistingDBDataDic)
        {
            UpdateListItem(clientContext, ListTitle, "Title", Item.Key, Item.Value);
        }


    }

    public ClientContext CreateClientContext(   string SPUrl,
                                                string UserName,
                                                String Password)
    {
        SecureString SecurePassword = new SecureString();//to pass the password to Sharepoint

        Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");//this is good practice when communicating with sharepoint time and date

        foreach (char c in Password)
        {
            SecurePassword.AppendChar(c);
        }
        ClientContext clientContext = new ClientContext(SPUrl);
        clientContext.Credentials = new SharePointOnlineCredentials(UserName, SecurePassword);

        return clientContext;

    }


}
}

For more Information on How this class works, you can check microsoft website

Answer 5

It was tough to build this script, as SPO supports batch method REST API, but there was no direct method to batch request in PnP.

Finally I combined the two concepts (PnP and System.Net) and came with following PowerShell script.

Refer to the demo video

Explanation:

• In this we are using PnP to connect to SharePoint Online
• Followed by, setting up global Web Request auth cookies, so we can use PowerShell Invoke-WebRequest method.
• After setting the cookies we need to build the batch request. In my code line 31 to 38 are doing this job. You need to update this logic fetch content from your CSV file. In a single batch use up to 100 request.
• Now we are using SPO REST API we need Request Digest to pass page security validation. For this we created a function Update-RequestDigest from line 24 to 27. We need to call this first everytime we make a new request.
• Line 42 to 70 is just creating the new item multiple batch request. Do not update anything here.
• Last thing to mention here, do not change the number of rn, it took almost an hour to debug for correct number of new lines.

$global:webSession = New-Object Microsoft.PowerShell.Commands.WebRequestSession

# Site Collection URL
$_url = "https://tenant.sharepoint.com/sites/siteName"

# Connect to SPO PnP and use the Auth cookies for web request.
function Init-SPOConnection {
    param($Url)

    # Get credentials to connect to SPO
    $cred = Get-Credential -Message "Credentials for SPO"
    Connect-PnPOnline $Url -Credentials $cred

    # Get the Authetication Cookies
    $webCtx = (Get-PnPWeb).Context
    $authCookies = $webCtx.Credentials.GetAuthenticationCookie($Url, $true)

    #Set webSession Auth cookies
    $global:webSession.Cookies.SetCookies($Url, $authCookies)
    $global:webSession.Headers.Add("Accept", "application/json;odata=verbose")
}

# Call this function before each web request to renew page security validation
function Update-RequestDigest {
    $global:webSession.Headers.Remove("X-RequestDigest") #If header already available, remove it and add new page validation
    $global:webSession.Headers.Add("X-RequestDigest", (Get-PnPWeb).Context.GetFormDigestDirect().DigestValue)
}

# Update the logic below to read from CSV and create Object Array as per your list schema
# Keep in mind the SPO REST API can take upto 100 request in one batch.
$itemPayload = @(
    @{"__metadata" = @{"type" = "SP.Data.ListAListItem"}; "Title"="Test 1"},
    @{"__metadata" = @{"type" = "SP.Data.ListAListItem"}; "Title"="Test 2"},
    @{"__metadata" = @{"type" = "SP.Data.ListAListItem"}; "Title"="Test 3"},
    @{"__metadata" = @{"type" = "SP.Data.ListAListItem"}; "Title"="Test 4"},
    @{"__metadata" = @{"type" = "SP.Data.ListAListItem"}; "Title"="Test 5"},
    @{"__metadata" = @{"type" = "SP.Data.ListAListItem"}; "Title"="Test 6"}
)

$listUrl = "$_url/_api/web/lists/getbytitle('ListA')/Items"

#Init Batch request from here (Do not change any of the logic b/w this and #End-Init batch comment.
$batchGUID = [System.guid]::NewGuid().toString()
$changeSetGUID = [System.guid]::NewGuid().toString()

$changesetBody = "";

$itemPayload | ForEach-Object {
    $changesetBody = -join($changesetBody, "--changeset_$changeSetGUID", "`r`n")
    $changesetBody = -join($changesetBody, "Content-Type: application/http", "`r`n")
    $changesetBody = -join($changesetBody, "Content-Transfer-Encoding: binary", "`r`n`r`n")
    $changesetBody = -join($changesetBody, "POST $listUrl HTTP/1.1", "`r`n")
    $changesetBody = -join($changesetBody, "Content-Type: application/json;odata=verbose", "`r`n`r`n")
    $changesetBody = -join($changesetBody, ($_ | ConvertTo-Json -Compress), "`r`n`r`n")
}

$changesetBody = -join($changesetBody, "--changeset_$changeSetGUID--", "`r`n")

$body = "--batch_$batchGUID`r`n"
$body = -join($body, "Content-Type: multipart/mixed; boundary=changeset_$changeSetGUID", "`r`n")
$body = -join($body, "Content-Length: $($changesetBody.Length)", "`r`n")
$body = -join($body, "Content-Transfer-Encoding: binary", "`r`n`r`n")

$body = -join($body, $changesetBody, "`r`n")

$body = -join($body, "--batch_$batchGUID--", "`r`n")

$enc = [system.Text.Encoding]::ASCII
$data = $enc.GetBytes($body)
#End-init batch

Init-SPOConnection -Url $_url
Update-RequestDigest
#Single request
$jsonObj = Invoke-WebRequest -Uri "$_url/_api/`$batch" -Method Post -Headers @{"Content-Type"="multipart/mixed; boundary=batch_$batchGUID"; "Content-Length"=$data.Length; "Host"="tenant.sharepoint.com"} -Body $data -WebSession $global:webSession

Note: I tested this script against 500 requests max and it was working fine (batch of 100 request), as I am adding only Title. In case the payload is relatively large, you may need to reduce the number of batch request from 100.

Answer 6

Update from 2023. Now PnP PowerShell supports batching. Not for a lot of cmdlets, but it's much easier now to work with list items.

So, now your code could look like this:

$lastItemID = 0
$batch = New-PnPBatch
$csv = import-csv .\Downloads\slabtarget.csv | Sort-Object Id 
foreach ($item in $csv){
    Set-PnPListItem -List '<listName>' -Identity $item.id -Values @{ 'Gallery' = $item.Gallery, 'Branch' = $item.Branch, 'Image' = $item.Image, 'Product_Type' = $item.Product_Type, 'Collection' = $item.Collection } -Batch $batch
}
Invoke-PnPBatch -Batch $batch

More info: https://pnp.github.io/powershell/articles/batching.html