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