As a developer or administrator working with SharePoint, sometimes I need to know exactly what the data is doing, what on earth is it thinking. I may want to manipulate the data real quick without going to the website. This post may also very helpful if you’re working against SharePoint’s web services, as you can look at the exact data format you’ll get back per field.
I am going to walk through accessing SharePoint lists and their items, reading and writing data using PowerShell. Using these techniques I’ve been able to create scripts to automate importing data, decrease development time as I can look at my raw data instead of the friendly view SharePoint gives us.
You will need to be on one of the SharePoint servers to perform these actions.
First thing to do is open the SharePoint 2010 Management Shell (located in All Programs > Microsoft SharePoint 2010 Products). This will open up a nice black window.
Now let’s get talking with SharePoint. Before we can talk to a list, we need to talk to a web site.
Get the website
$web = Get-SPWeb http://address/of/website
With this command, and most others, after you press enter, you won’t get a confirmation, you will return back to the prompt. The fact that you didn’t get an error, in most cases, means it worked.
Check to make sure this is the web you want to work with
$web.Title
This should return your site’s name.
Get the list you want to play with
$list = $web.Lists["List Name"]
From here you can get the title, or item count
So my Tasks list is empty. Let’s add an item, using PowerShell of course
$newTask = $list.items.add() $newTask["Title"] = "My New Task" $newTask.update()
Now when I run the items.count I’ll get back 1
Important notes about adding and editing items through PowerShell
- Required fields won’t stop you from adding a new item, so if it’s required, make sure you include it.
- Make sure you’re adding items in the correct string format (we cover that a little further down)
- Default values will automatically load, so you don’t have to worry about those.
Here’s the result of my new item
Now let’s read some data from it. Since I already have the $newTask object loaded, I don’t have to reload it, but I want to show how to get an item from a list.
$task = $list.GetItemByID(1)
Now I can look at each field if I want
$task["Title"].tostring() $task["Status"].tostring() $task["Start Date"].tostring()
So now lets set the due date and assigned to fields.
$task["Due Date"] = "1/21/2012 8:00 AM" $task["Assigned To"] = "Administrator" $task.update()
And you should have received an error
That’s because the Assigned To field is incorrect. We cannot simply write the user’s name, we need to write their id combined with their name. This is where it can get tricky. To find the user’s ID, you can click on their name somewhere in SharePoint, which should send you to their profile page. Looking at the URL, you’ll see userdisp.aspx?ID=#. The ID=# is their id.
If clicking on a user name brings you to their site, then this won’t work. Instead we can use PowerShell to get their ID.
$user = $web.siteusers["domain\username"] $user.id $user.name
Now with the ID and name, we can save it to the Assigned To field. The name should be the users full name, in my example, my name is my login name, bad data for a test account.
We need to combine the two values like id;#name, so my example will be 3;#Administrator
$task["Due Date"] = "1/21/2012 8:00 AM" $task["Assigned To"] = "3;#Administrator" $task.update()
And when I browse back to SharePoint I’ll see this item has been updated correctly.
You can also see what the value is for an existing assigned to field
$task["Assigned To"].tostring()
and that will return the value we entered, but updated by SharePoint correctly. As long as the ID is correct, SharePoint will usually take care of the rest.
Moving forward, working with complex fields like Choice, Lookup, and Multichoice, you can use PowerShell to open an existing item and look at the existing data to understand how SharePoint saves it.
I added a lookup column to my task list, looking up the calendar. Let’s see what that data looks like. (If you modify a list or list item via the browser, you will need to close out the PowerShell session window and reconnect to the web and list).
So what if I want more about this event? I want to open the calendar, see the end date and who created it, and who last modified it. Using what we learned above, I can do
Note the Author and Editor fields, these are the Created by and Modified by fields.
As you can see, PowerShell is quite flexible and powerful. Using scripts you can automate adding items, copying items from one list to another, and modify other system settings. I hope this helps, if you want to see something specific, please leave me a comment!