Using PowerShell to play with SharePoint Items

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.

PowerShell 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 Web

Get the list you want to play with

$list = $web.Lists["List Name"]

From here you can get the title, or item count

List Title and 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

Add new item

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

My New Task

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()

getting fields

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

update item errors

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

getting user id

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.

add assign to

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.

assigned to value

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

lookup field value

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

get calendar item

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!

About these ads

10 thoughts on “Using PowerShell to play with SharePoint Items

  1. Pingback: Loading SharePoint lists from Excel using PowerShell | David Lozzi's Blog

  2. nicolas

    Hello David, I would like to change month, week and day view titles for a list calendar display using powershell. Do you know how to do that ? And if you do, how did you get it ? Because I probably gonna have to change some more settings like that. Thank you so much if you could help me !

    Reply
    1. nicolas

      I found by myself.
      If it can be usefull to someone, you have to use field “ViewData” e.g :

      newView.ViewData = @”

      “;

      “;

      Reply
      1. nicolas

        I found by myself.
        If it can be usefull to someone, you have to use field “ViewData” e.g :

        $Calendar.viewdata = ”;

  3. Pingback: Modificación del campo "Creado por" de una lista de Sharepoint 2010 - Blog de P@blo - Sharepoint Server

  4. Knoots

    This is great. I am want to populate a choice field in a document library from a SQL table. I realize I could do this with site columns or external data sources but we have a 3rd party app that does not support those field choices at the moment. My temporary fix would be to dynamically update the choice items programatically. Any thoughts on how to complete that?

    Reply
  5. Pingback: Use PowerShell to manipulate the values of a SharePoint choice field. | David Lozzi's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s