Working with SharePoint list data using OData, REST and JavaScript

Recently I’ve been doing a fair amount of work with SharePoint list data using OData and the SharePoint 2013 REST APIs; so I thought I would share some of my experiences with you.

As you are aware, SharePoint lists are far from a new thing. However, they do offer a really flexible method to store a lot of data. Creating data is simple through the OOB user interface and you can find many different ways of filtering, grouping and ordering your data using views.

However, what if you have a completely customised user interface, and need to both create and surface list based data into it? Using the client object model, the new OData and REST functionality in SharePoint 2013 is definitely one way, and this is the focus of this blog post.

Future posts may include different CRUD operations and different entities.

Working with OData and REST in SharePoint 2013

I'm going to assume that you have some understanding of OData and REST. If you are really new to it as a concept then there are plenty of informative articles out there, so we are purely focusing on SharePoint 2013's implementation here.

My first impressions about the way that you construct your RESTful HTTP requests very closely mimics the client object model; so it doesn't take long for you to familiarise yourself with; lets take a look at a few examples:

Accessing a list with the client object model:#

List.GetByTitle(Sales)

And with the corresponding REST endpoint:

http://contoso/sites/sales/_api/lists/getbytitle('Sales')

In the example above, you can see that we are requesting a list titled 'Sales', which is what will be returned to us. If we wanted to have the items within the list returned then we would create the following:

http://contoso/sites/sales/_api/lists/getbytitle('Sales')/items

We can go a lot further than this. I have included a few examples below:

We have briefly covered querying data, so what does this look like in action? In the screenshot below, you can see that we are querying 'all suppliers' in a custom list:

Creating list items in SharePoint 2013 using JavaScript

We are going to move on to creating list items using a custom UI and the CSOM:

Our form is nothing too scary really, we are going to create a simple ‘Supplier Management’ form which will allow our users to enter some basic information about suppliers. Which is a great point really to insert a disclaimer! All of the code and examples provided here are exactly that.
There is no support provided, and if you want to use any of this content then I recommend that it is purely for learning purposes only. None of this has been tested on any of your environments.

Now we have that out of the way; lets take a look at the form:

Next, we will create the JavaScript that will create a new list item when our users click on the 'create' button:

function newSupplier(siteUrl) {

   
    var sName = $('#supplierName').val();
    var sRegion = $('#supplierRegion').val();
    var sProduct = $('#supplierProduct').val();
    var sAcctMgr = $('#supplierAcctMgr').val();
 
    var clientContext = new SP.ClientContext(siteUrl);

    var oList = clientContext.get_web().get_lists().getByTitle('Suppliers');   
    var itemCreateInfo = new SP.ListItemCreationInformation();

    this.oListItem = oList.addItem(itemCreateInfo);   
    oListItem.set_item('Title', sName);   
    oListItem.set_item('Region', sRegion);
    oListItem.set_item('Product', sProduct);
    oListItem.set_item('AccountManager', sAcctMgr);
    oListItem.update();

    clientContext.load(oListItem);

    clientContext.executeQueryAsync( 
        Function.createDelegate(this, this.onQuerySucceeded),
        Function.createDelegate(this, this.onQueryFailed)
    );  

Walkthrough of the code: 

If we take a quick walk through the code above, you can see that we are creating several variable:

    var sName = $('#supplierName').val();
   var sRegion = $('#supplierRegion').val();
   var sProduct = $('#supplierProduct').val();
   var sAcctMgr = $('#supplierAcctMgr').val();

These simply contain the data that our users enter into the <input> elements; you can see that we are using jQuery to reference the various input boxes, and the .val(); function to capture the data.

Moving on, we now load our current site into context, and then get the list that we want to create a list item in:

    var clientContext = new SP.ClientContext(siteUrl);
  var oList = clientContext.get_web().get_lists().getByTitle('Suppliers');

We next need to use the ListItemCreationInformation() function and state which columns we want to populate:

    var itemCreateInfo = new SP.ListItemCreationInformation();
    this.oListItem = oList.addItem(itemCreateInfo);
    oListItem.set_item('Title', sName);
    oListItem.set_item('Region', sRegion);
    oListItem.set_item('Product', sProduct);
    oListItem.set_item('AccountManager', sAcctMgr);

We are passing the data in our variable into the various columns in our supplier list.

Next we need to commit the changes; by using the oListItem.Update(); function.

Now that we have created the list item, we have to consider what happens when the operation succeeds, or fails:

function onQuerySucceeded() {
    alert('Item created: ' + oListItem.get_id());
}

function onQueryFailed(sender, args) {
    alert('Request failed. ' + args.get_message() +
        '\n' + args.get_stackTrace());
}

If you want to learn more about CRUD operations in SharePoint 2013, using the JavaScript OM, then here is an excellent place to start:

http://msdn.microsoft.com/en-us/library/jj163201.aspx

Lets take a look at our form working with a couple of new entries:

Retrieving and rendering SharePoint 2013 list items

Earlier on in this article, we briefly covered querying list data with OData; we saw how to retrieve list items, select specific properties and filter results. In this section we are going to render our Supplier list into a custom UI. We will use jSON and jQuery to do this, along with our RESTful HTTP request.

The first step for us is to ensure that our HTTP request is returning the data that we are expecting:

Our URL: http://contoso/_api/web/lists/GetByTitle('Suppliers')/items

This returns all of the list items in ATOM/XML format:

This is great, but we want to work with OData/jSON and not XML. If we start fiddler, we can take a look at the same data, but returned in jSON format instead. In fiddler, you will need to set a filter:

Now when you refresh the page, you will be seeing the jSON returned.

In order to render our content, we will be using jQuery, specifically jQuery.ajax(). If you check out the details for jQuery.ajax(); (http://api.jquery.com/jQuery.ajax/) then you can see that it 'Performs an asynchronous HTTP (ajax) request', which is exactly what we want to do :)

So, in order for us to retrieve our supplier information, we need to create the following code:

<script>
    $,ajax({

        url: "http://contoso/_api/web/lists/GetByTitle('Suppliers')/items",
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        success: function(data){
            $.each(data.d.results, function(index, item){
                $('#oDataSuppliers').append("<li>" + "<h1>" + item.Title + "</h1>" + "<h2>" + item.Region + "</h2>" + "</li>");
            });
        },
        error: function(error){
            alert(JSON.stringify(error));
        }

});

</script>

Lets take a walk through the code above.

There is a really good description of the settings for $.ajax() on the jQuery site (http://api.jquery.com/jQuery.ajax), but to save time, I have included the information here:

url: "http://contoso/_api/web/lists/GetByTitle('Supplier')/items", this is the URL to where we want the send the request; note this is our OData/REST HTTP endpoint.

type: "GET", I am using a HTTP GET request to retrieve the data from the list.

headers: {
                    "accept": "application/json;odata=verbose",
    },

Here we are saying in the request header that we want the server to return jSON data. Remember that by default, SharePoint is going to return XML data..

success: function(data){
            $.each(data.d.results, function(index, item){
                $('#oDataSuppliers').append("<li>" + "<h1>" + item.Title + "</h1>" + "<h2>" + item.Region + "</h2>" + "</li>");
            });
        },

In this block of code, we are stating what we want to do with the data that is being returned. I want to render this content into some HTML that I can then style using CSS. Lets take a closer look at this:

$.each(data.d.results

Here we are using a foreach loop; so for each object that is returned, we want to append the specific properties into HTML; into an existing element with the ID of 'oDataSuppliers'. The HTML that we want to render will basically look like this:

<ul>
    <li>
        <h1> item.Title </h1>
        <h2> item.Region </h2>
    </li>
</ul>

The end result will retrieve the Supplier name and their region, and will append it to an unordered list:

Hopefully, through this article you have seen how we can use the client object model to create list items; and by using OData and RESTful HTTP requests, along with jQuery we were able to render our list data into a custom UI.

For more details on REST and OData in SharePoint 2013: http://msdn.microsoft.com/en-us/library/office/fp142380.aspx
For more information about jSON: http://json.org/
For more information about jQuery: http://jquery.com/


Cheers,
Steve.

Update 12/04/14 - I have added an example that demonstrates how to export User Profile Properties to a CSV file at the end of this post.

The example PowerShell script below can be used to retrieve all user profile properties for all users within a Site Collection. It doesn't appear to be possible to connect to the User Profile Service Application and retrieve profile properties for all users with the tenant using CSOM, the only approach is to perform this at a Site Collection level, you could of course add a ForEach loop to iterate through all Site Collections however additional effort would be required to remove duplicate profile data from the output as it's likely that each user will have permission to multiple Site Collections therefore would be retrieved multiple times using this approach.

It requires two variables to be updated - $User which is the tenant admin and $SiteURL which is the URL for the Site Collection that you wish to retrieve User Profile data from.

#Please install the SharePoint client components SDK - http://www.microsoft.com/en-us/download/details.aspx?id=35585 prior to running this script.

#Specify tenant admin and URL
$User = "admin@tenant.onmicrosoft.com"

#Configure Site URL and User
$SiteURL = "https://tenant.sharepoint.com/sites/site"

#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"
$Password = Read-Host -Prompt "Please enter your password" -AsSecureString
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)

#Bind to Site Collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Context.Credentials = $Creds

#Identify users in the Site Collection
$Users = $Context.Web.SiteUsers
$Context.Load($Users)
$Context.ExecuteQuery()

#Create People Manager object to retrieve profile data
$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)
Foreach ($User in $Users)
    {
    $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
    $Context.Load($UserProfile)
    $Context.ExecuteQuery()
    If ($UserProfile.Email -ne $null)
        {
        Write-Host "User:" $User.LoginName -ForegroundColor Green
        $UserProfile.UserProfileProperties
        Write-Host ""
        } 
    }

Here is an example of the output.

If you only need to retrieve a selection of user profile properties rather than everything, the script can be easily updated - simply replace $UserProfile.UserProfileProperties with $UserProfile | Select (Property Names). The following properties can be retrieved using this approach:

• AccountName
• DirectReports
• DisplayName
• Email
• PersonalURL
• PictureURL
• Title
• UserURL

To retrieve AccountName,Email and PictureURL the following command can be used - $UserProfile | Select AccountName,Email,PictureURL

If you need to retrieve other profile properties you will need to use the following approach - $UserProfile.UserProfileProperties.PropertyName, for example to retrieve the department for a user the following can be used $UserProfile.UserProfileProperties.Department.

Below is an example of how to export User Profile Properties to a CSV file - which you may find a little more useful than outputting to the console. Simply replace the last section of the script above with the script below, you need to update the $Output variable to specify the location to output the CSV file to.

If you need to add any additional properties, two lines in the script need to be updated (highlighted). The first line specifies the headings of the CSV file, the order of which need to match exactly the properties specified in the second line that is highlighted.

The second line that is highlighted specifies the actual properties to output for each user. The $UserProfile and $UPP variables can be used to select the properties, $UPP is $UserProfile.UserProfileProperties. My example contains both types of properties.

#Create People Manager object to retrieve profile data
$Output = "D:\Output.csv"
$Headings = "Name","Email","OneDrive URL","Phone","Job Title","Department"
$Headings -join "," | Out-File -Encoding default -FilePath $Output

$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)
Foreach ($User in $Users)
    {
    $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
    $Context.Load($UserProfile)
    $Context.ExecuteQuery()
    If ($UserProfile.Email -ne $null)
        {
        $UPP = $UserProfile.UserProfileProperties
        $Properties = $UserProfile.DisplayName,$UserProfile.Email,$UserProfile.PersonalUrl, $UPP.WorkPhone,$UPP.'SPS-JobTitle',$UPP.Department
        $Properties -join "," | Out-File -Encoding default -Append -FilePath $Output
        } 
    }

 The example outputs the following properties:

• Name
• Email
• OneDrive URL
• Phone
• Job Title
• Department

Here is the output file that is created:

Hopefully this gives you enough basic information to make a start using PowerShell to retrieve user profile information.

Brendan Griffin - @brendankarl

UPDATE: The script now supports uploading files larger than 2MB.

Another PowerShell sample script for you. This one uploads all files within a specified local directory to a Document Library within a Site in an O365 tenant.

All you need to run this script is an O365 tenant, the SharePoint client components SDK installed on the machine running the script - http://www.microsoft.com/en-us/download/details.aspx?id=35585 and to update the $User, $SiteURL, $DocLibName (name of the destination Document library) and $Folder (path to the local folder containing the files to upload) variables. When the script is executed it will prompt for the password of the user specific in the $User variable.

One thing to point out is that CSOM has a maximum upload size of 2MB.

#Specify tenant admin and site URL
$User = "admin@tenant.onmicrosoft.com"
$SiteURL = "https://tenant.sharepoint.com/sites/site"
$Folder = "C:\FilesToUpload"
$DocLibName = "DocLib"

#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$Password = Read-Host -Prompt "Please enter your password" -AsSecureString

#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
$Context.Credentials = $Creds

#Retrieve list
$List = $Context.Web.Lists.GetByTitle($DocLibName)
$Context.Load($List)
$Context.ExecuteQuery()

#Upload file
Foreach ($File in (dir $Folder))
{
$FileStream = New-Object IO.FileStream($File.FullName,[System.IO.FileMode]::Open)
$FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
$FileCreationInfo.Overwrite = $true
$FileCreationInfo.ContentStream = $FileStream
$FileCreationInfo.URL = $File
$Upload = $List.RootFolder.Files.Add($FileCreationInfo)
$Context.Load($Upload)
$Context.ExecuteQuery()
}

Brendan Griffin

Windows PowerShell is a fantastic tool; SharePoint 2010 has literally hundreds of different PowerShell Cmdlets that are available out of the box, if you don’t believe me check this out - http://technet.microsoft.com/en-us/library/ff678226.aspx. What about MOSS 2007? Whilst there aren’t any native Cmdlets for MOSS 2007, PowerShell can be used to access the SharePoint object model directly instead and in most cases achieve the same objectives, this isn’t as daunting as it sounds; I’m not a developer but even I have been able to find my way around the object model and put together some useful scripts (at least to me anyway!)

• URL
• Owner login
• Owner e-mail address
• Last time that the root web was modified
• The size of the quota assigned
• The total storage used
• The percentage of the quota being used

I've included the script below, all you need to do is to copy this into Notepad (or your text editor of choice), edit the two highlighted variables to match your requirements - $Output specifies the location to output the results to in csv format, $SiteURL specifies the URL of the root site collection, this will then be used to discover other site collections within the Web application. Once you have done this save the file with a .PS1 extension, for example ScriptName.PS1. The script can be run on any server in the SharePoint farm from within a Windows PowerShell window using .\ScriptName.PS1.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
#Configure the location for the output file
$Output="C:\Output.csv";
"Site URL"+","+"Owner Login"+","+"Owner Email"+","+"Root Site Last Modified"+","+"Quota Limit (MB)"+","+"Total Storage Used (MB)"+","+"Site Quota Percentage Used" | Out-File -Encoding Default -FilePath $Output;
#Specify the root site collection within the Web app
$Siteurl="http://intranet.contoso.com";
$Rootweb=New-Object Microsoft.Sharepoint.Spsite($Siteurl);
$Webapp=$Rootweb.Webapplication;
#Loops through each site collection within the Web app, if the owner has an e-mail address this is written to the output file
Foreach ($Site in $Webapp.Sites)
{if ($Site.Quota.Storagemaximumlevel -gt 0) {[int]$MaxStorage=$Site.Quota.StorageMaximumLevel /1MB} else {$MaxStorage="0"};
if ($Site.Usage.Storage -gt 0) {[int]$StorageUsed=$Site.Usage.Storage /1MB};
if ($Storageused-gt 0 -and $Maxstorage-gt 0){[int]$SiteQuotaUsed=$Storageused/$Maxstorage* 100} else {$SiteQuotaUsed="0"};
$Web=$Site.Rootweb; $Site.Url + "," + $Site.Owner.Name + "," + $Site.Owner.Email + "," +$Web.LastItemModifiedDate.ToShortDateString() + "," +$MaxStorage+","+$StorageUsed + "," + $SiteQuotaUsed | Out-File -Encoding Default -Append -FilePath $Output;$Site.Dispose()};

 

 

Below is an example of the script output.

 

 

 Brendan Griffin

I'm my continued quest to get to grips with the Client Side Object Model (CSOM) in SharePoint 2013, I have put together a sample script below that connects to a Site Collection within an O365 tenant and does the following:

• Creates a list using the "Custom" list template
• Adds two Site Columns to the list (City and Company)
• Adds these fields to the default view
• Adds an item to the list

You may find this useful as a reference! The usual disclaimers apply :)

All you need to run this script is an O365 tenant, the SharePoint client components SDK installed on the machine running the script - http://www.microsoft.com/en-us/download/details.aspx?id=35585 and to update the $User, $SiteURL and $ListTitle variables. When the script is executed it will prompt for the password of the user specific in the $User variable.

#Specify tenant admin and site URL
$User = "admin@tenant.onmicrosoft.com"
$SiteURL = https://tenant.sharepoint.com/sites/site
$ListTitle = "List Title"

#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
$Password = Read-Host -Prompt "Please enter your password" -AsSecureString
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)

#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
$Context.Credentials = $Creds

#Retrieve lists
$Lists = $Context.Web.Lists
$Context.Load($Lists)
$Context.ExecuteQuery()

#Create list with "custom" list template
$ListInfo = New-Object Microsoft.SharePoint.Client.ListCreationInformation
$ListInfo.Title = $ListTitle
$ListInfo.TemplateType = "100"
$List = $Context.Web.Lists.Add($ListInfo)
$List.Description = $ListTitle
$List.Update()
$Context.ExecuteQuery()

#Retrieve site columns (fields)
$SiteColumns = $Context.Web.AvailableFields
$Context.Load($SiteColumns)
$Context.ExecuteQuery()

#Grab city and company fields
$City = $SiteColumns = $Context.Web.AvailableFields | Where {$_.Title -eq "City"}
$Company = $SiteColumns = $Context.Web.AvailableFields | Where {$_.Title -eq "Company"}
$Context.Load($City)
$Context.Load($Company)
$Context.ExecuteQuery()

#Add fields to the list
$List.Fields.Add($City)
$List.Fields.Add($Company)
$List.Update()
$Context.ExecuteQuery()

#Add fields to the default view
$DefaultView = $List.DefaultView
$DefaultView.ViewFields.Add("City")
$DefaultView.ViewFields.Add("Company")
$DefaultView.Update()
$Context.ExecuteQuery()

#Adds an item to the list
$ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
$Item = $List.AddItem($ListItemInfo)
$Item["Title"] = "New Item"
$Item["Company"] = "Contoso"
$Item["WorkCity"] = "London"
$Item.Update()
$Context.ExecuteQuery()

Brendan Griffin