Create SharePoint List Items with PowerShell
Many Petri IT Knolwedgebase readers know that one of the reasons PowerShell is so useful is its versatility, seamlessly switching between working with files on a hard drive, computers across a domain, and registry keys in remote servers all in a single bound!
But have you tried using PowerShell to connect up to SharePoint to save your output as list items? That’s what we’ll tackle in today’s article.
How to Create SharePoint List Items Using PowerShell
The great thing about doing this is that you can work with your local workstation without having to remote into the SharePoint server to create your list items with PowerShell. In fact, you aren’t even really using SharePoint as a system administrator. Instead, think of this as using SharePoint as a user to maintain your system administration information.
Passwords Haven’t Disappeared Yet
123456. Qwerty. Iloveyou. No, these are not exercises for people who are brand new to typing. Shockingly, they are among the most common passwords that end users choose in 2021. Research has found that the average business user must manually type out, or copy/paste, the credentials to 154 websites per month. We repeatedly got one question that surprised us: “Why would I ever trust a third party with control of my network?
To begin, you’re going to need SharePoint set up in your environment, of course. You’ll also need a list to connect to, and the list columns need to already be in place.
I am going to use a standard list from SharePoint — a list of contacts — and I’ll show you the code that you’ll use to create a new user with PowerShell. These same techniques can be used to populate a SharePoint list as your PowerShell script iterates through a list of servers, desktops, databases, and web applications in a farm or service accounts in use throughout the domain.
Do I need SharePoint 2013? You aren’t required to use any specific version of SharePoint. This works with SharePoint 2010 and 2013 and even older versions, though at the time of this writing, the use of the Lists web service class was not yet supported in Office 365.
Adding Items into a SharePoint List
Adding items into a SharePoint List requires that you know something about the list to which you’re uploading the items. For instance, you would know that a Contact has a full name (first and last name), address, and phone number. However, you may not know the column names that store that data in your list. Be sure you know what column names you’ll be adding from your script as well as any columns in the list that are mandatory.
To successfully add SharePoint items to a list through PowerShell you will follow these steps:
- Make the connection to the SharePoint list.
- Formulate the XML data for the new item.
- Update the list with the formatted XML.
Piece of cake, right? Actually, it’s uglier than you’d wish but less complicated than it looks. Let’s take them one by one.
Making the connection to the SharePoint List
The first thing we need to do is make the connection. I’ll use a variable for storing the connection and $spLists, and I’ll use the New-WebServiceProxy cmdlet to create it.
$TeamSiteURL = “HTTP://TeamSite.MySharePoint.Test”
$SPLists = New-WebServiceProxy –Uri “$TeamSiteURL/_vti_bin/lists.asmx?WSDL” ` –UseDefaultCredential –Namespace SpWs$List = “Contacts”
You now have a connection into the Lists Web Service for your team site. Make a separate service connection variable for each SharePoint site to which you are going to add list items.
Creating the XML data for the new item
The data to add to the list is going to be added by an XML document. PowerShell is really good at handling XML data, so this is probably even easier that you might think if you’ve tried it with C# or VB.
The web service that we’re using accepts commands in an XML that looks like this:
<Batch> <Method ID=”Number” Cmd=”New”> <Field name=””>Field Value</Field> </Method> </Batch>
Batch is the collection of list items that we’re adding, Methods are the individual list items we’re creating, and Fields are the properties of the list item. There can be multiple Fields in a Method and multiple Methods in a Batch.
$xmlObj = New-Object [System.Xml.XmlDocument] <#Beware: The “Batch” in the method of the next line is case sensitive. I named the variable likewise as a reminder#> $Batch = $xmlObj.CreateElement(“Batch”) $Batch.SetAttribute(“OnError”,”Continue”) $Batch.SetAttribute(“ListVersion”,”1”)
This creates the Batch XML. Now we just need to add in the data for the new list item.
$Batch.InnerXml= @” <method id=’$methodID’ cmd=’New’> <field name=’fullname’>Neo</field> <field name=’company’>Matrix</field> </method> “@
Things you should know about this:
- I used a Here-String to make adding in the XML data easy to read. Here-strings are way easier than doing ticks and long strings that encompass many lines.
- Use one <method></method> for each list item you’re adding.
- The $MethodID is not the same thing as the row identifier for a list item. It is fine to put ID of “1” for your first new list item, even if there is already an item that exists. This ID field identifies the number of the update within this batch, not the number of items in your SharePoint list.
- Use an actual number for the ID field (you can’t just pass in $ID if it is null)
- You can pass in the values of the fields as variables so you can actually get the benefits of scripting it.
Update the list with the formatted XML
So the last part should be easy, right? All the heavy lifting is done, as we’ve already got a connection to the list’s web service stored as a PowerShell variable, and the XML for the new item is created.
Here’s the code that inserts the new item to the SharePoint list:
$ReturnXml = $SPLists.UpdateListItems($List,$Batch)
Things to know about this:
- Earlier, we set the batch to continue on error; however, there is still a return code. Any problems will be listed in the $ReturnXml variable.
- You can create up to 160 SharePoint list items in a single batch.
Using SharePoint lists can be thought of as more of a user function than a system administrator function, and there may be some IT pros out there wondering why we need to create SharePoint lists with a script.
Hopefully, these IT pros know we should be doing most everything in a script, right? But the bigger point is that SharePoint can be a great tool to leverage. For those of you that have already begun scripting your IT functions, this can be an effective tool that you can use as an alternative to logging and inventory collecting.