6

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
        }
    }
2
+300

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 = "bruce@wayneenterprise.onmicrosoft.com"
$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()
  • 1
    Thanks - it's been years since I've broken into this stuff - this set me on the correct path and I was able to update in batches. Paired with ExecuteQueryAsync() I was able to send over all the requests - drastically reducing the time needed to complete the entire list. – Jesus Shelby Jun 20 at 23:30
  • Thanks for awarding bounty...glad it helped 😊 – Siddharth Vaghasia Jun 21 at 3:15
3

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 .

  • Thank you. I did this similarly - breaking out in multiple processes, but between 1800 - 2300 items updating, it starts returning a 404 error and all running processes fail. Not sure if this is a throttling thing or token thing, to be honest. – Jesus Shelby Jun 18 at 13:10
  • Not sure on this one . I may need to check . Last time I did this I had 4000 odd items and I did not face any issue . May be the site must have been very busy or you may have initiated 5 or more parallel threads. Try by running only this particular patch once!! – yash.kamdar Jun 19 at 11:36
3

There is no way batching PNP cmdlets. The CmdLet runs and eveything is "done" afterwards.

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..

1

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

1

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.

  • Thank you. I ended up using the CSOM calls directly and was able to overcome the initial limitations we had. This answer looks promising as well though so thank you for adding it. – Jesus Shelby Jun 20 at 23:31
  • 1
    Yeah, I read all the answers on this post as well and found they are just alternative. So I thought to give it a try and build the script to batch update content using PowerShell, and finally came up with this script. You can upvote if you think this is what you looking for. It will help others who are seeking same solution. – ThinkB4Code Jun 21 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.