Limitations of SharePoint ListData.svc

The SharePoint RESTful API (ListData.svc) is a great and effective way to access data in SharePoint lists and document libraries for use in mobile/desktop web apps. However, you should be aware of some of the limitations of ListData.svc in SharePoint 2010.

  1. Anonymous access not allowed.

    Only authenticated users can use ListData.svc, even for lists that allow anonymous access. This means you can’t use ListData.svc in public sites.

  2. Property names based on display names instead of internal names

    In contrast to SharePoint Web Services and CSOM, which use internal names of the item fields, ListData.svc uses the display names (with whitespace trimmed). This can cause issues when your user changes the display names of columns or if your user can switch languages in a multilingual SharePoint site (the display names of fields may change).

  3. No access to External List

    You cannot access (read or write) data in SharePoint External Lists.

  4. No access to discussion list

    ListData returns HTTP 500 error when accessing a Discussion list. This is confirmed as a bug and has been escalated to the development team. More details here.

  5. Cannot use $filter beyond resource throttling threshold

    You cannot filter on a list with more items than the throttling threshold. ListData.svc does not take into account the use of folders. Full details in this blog post.

Did you encounter any other limitations? Let me know and I’ll update the post.

API access to SharePoint external lists

Suppose you want to programmatically access data in a SharePoint external list (e.g.  data from SQL Server). You may think that you can just use SharePoint’s ListData.svc. Think again…

I did some experimenting with an external list connected to SQL Server, with the following results:

SharePoint API Read Create/Update/Delete
ListData.svc (REST) Not supported Not supported
Web services (SOAP) Supported (GetListItems) Not supported
Client side object model Supported Supported

In short: if you want CRUD access to an external list, your SharePoint API choice is reduced to the client side object model.

I hope this is useful to others working with external lists, as there is little information about this topic on the internet. Let me know if you have different findings.

Using SharePoint CSOM in Node.js

My post “Using SharePoint CSOM in HTML5 apps” describes the minimum set of js files you need to use the SharePoint JavaScript Client Side Object Model (CSOM) in the browser.

I was curious if you can also use SharePoint CSOM in Node.js. I found out, you can!  In this post I’ll show you how.

Warning: the SharePoint CSOM files are designed to run in the browser, so consider everything that follows as highly experimental!

The CSOM files

To use CSOM in the browser, you need the following 5 files from SharePoint:

  • /_layouts/1033/init.js
  • /_layouts/MicrosoftAjax.js
  • /_layouts/sp.core.js
  • /_layouts/sp.runtime.js
  • /_layouts/sp.js

If you want to learn more about the code in these files, you can use the DEBUG versions which you can find here:

  • /_layouts/1033/init.DEBUG.js
  • /_layouts/sp.core.DEBUG.js
  • /_layouts/sp.runtime.DEBUG.js
  • /_layouts/sp.DEBUG.js

A debug version of MicrosoftAjax.js is included in VisualStudio 2010.

Concatenated, the debug versions of these 5 files contain approximately 33.000 lines of JavaScript code, which I will refer to as the ‘CSOM code’ in the rest of this article. Our goal is to use the CSOM code on Node instead of the browser, without modifying the source files.

I started out by just concatenating the 5 source file in one big file and trying to run it with Node. Node will terminate when an error is thrown and point to the error. You will quickly realize that all the errors raised are related to the difference in execution context between a browser and Node. Node doesn’t have objects like document, window, XmlHttpRequest and navigator, which the CSOM code expects.

To summarize my findings, the CSOM code expects and requires the following:

  • a window object as global context
  • a DOM element named “__REQUESTDIGEST” which contains a digest value that will be included in HTTP request header X-RequestDigest.
  • a navigator object, identifying the user agent
  • a document object, with a URL property, used to identify the domain of the SP server
  • XMLHttpRequest to make server requests
  • authentication cookies

The trick is to emulate these objects, just enough to make the CSOM code run as intended and without errors. I ended up with the following code which mocks the browser environment expected by the CSOM code.

var window = global;
    
var navigator = {
    userAgent: "Node"
};
    
var formdigest = {
    value: 'DUMMY VALUE',
    tagName: 'INPUT',
    type: 'hidden'
};

var document = {
    documentElement: {},
    URL: 'http://yourdomain.sharepoint.com/',
    getElementsByName: function (name) {
        //console.log('getElementByName: ', name);
        if (name == '__REQUESTDIGEST') {
            return [formdigest]
        }
    },
    getElementsByTagName: function (name) {
        //console.log('getElementByTagName: ', name);
        return []
    }
};

Let’s have a closer look on the digest. Remember that our minimal aspx page contained a form digest control? This control will include a hidden form input element named “__REQUESTDIGEST”. The CSOM code will retrieve the value from the DOM, using document.getElementsByName(“__REQUESTDIGEST”)[0].value. As you can see, the code above emulates the document object and input element just enough to make this code work in the Node environment. We only need to set a valid digest value in the formdigest object. We’ll come to that later.

Remote authentication

In order to access the SharePoint server our Node SharePoint client needs to authenticate. Please see my post on remote authentication to SharePoint Online for more details. We will reuse the authentication code described in Node.js, meet SharePoint.

Emulating XMLHttpRequest

The CSOM code makes server requests using XMLHttpRequest. Luckily, there is already an implementation of XMLHttpRequest in Node and it works great. There’s only one workaround required in order to include the authentication cookies in the request. We will assign the authentication cookies to the XMLHttpRequest constructor and include them as part of the default headers. I made the following modification to the source of XMLHttpRequest:

var defaultHeaders = {
    "User-Agent": "node.js",
    "Accept": "*/*",
    "Cookie": this.constructor.authcookies
};

Now we can set the authentication cookies by using:

    // make sure the XMLHtpRequest includes the authentication cookies
    XMLHttpRequest.authcookies = 'FedAuth=' + client.FedAuth + '; rtFa=' + client.rtFa;

Obtaining a digest value

After authentication, but before the CSOM is used we need to obtain a valid digest value. The CSOM code expects the value to be available in a hidden input element which we emulated through the formdigest object. To get a digest value, we could fetch an aspx page with an embedded formdigest control and parse out the value. Another option is to use the sites.asmx SharePoint Web Service to request a digest value.

We will use the web service in the following code :

function requestDigest(params, callback) {

    var payload =
        '<?xml version="1.0" encoding="utf-8"?>' +
        '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' +
              ' xmlns:xsd="http://www.w3.org/2001/XMLSchema"' + 
              ' xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">' +
        '  <soap:Body>' +
        '    <GetUpdatedFormDigest xmlns="http://schemas.microsoft.com/sharepoint/soap/" />' +
        '  </soap:Body>' +
        '</soap:Envelope>';

    var options = {
        method: 'POST',
        host: 'yourdomain.sharepoint.com',
        path: '/_vti_bin/sites.asmx',
        headers: {
            'Content-type': 'text/xml',
            'Cookie': 'FedAuth=' + params.FedAuth + '; rtFa=' + params.rtFa,
            'Content-length': payload.length
        }
    }

    var req = http.request(options, function (res) {
        var responseText = '';
        res.setEncoding('utf8');
        res.on('data', function (chunk) {
            responseText += chunk;
        })

        res.on('end', function () {
            //console.log('XML: ', responseText);

            if ((responseText == null) || (responseText.length <= 0)) {
                return;
            }
            var startTag = '<GetUpdatedFormDigestResult>';
            var endTag = '</GetUpdatedFormDigestResult>';
            var startTagIndex = responseText.indexOf(startTag);
            var endTagIndex = responseText.indexOf(endTag, startTagIndex + startTag.length);
            var newFormDigest = null;
            if ((startTagIndex >= 0) && (endTagIndex > startTagIndex)) {
                var newFormDigest = responseText.substring(startTagIndex + startTag.length, endTagIndex);
            }
            if ((newFormDigest == null) || (newFormDigest.length <= 0)) {
                return;
            }
            
            formdigest.value = newFormDigest;
            callback();
        })
    })

    req.end(payload);
}

To summarize, the client app on Node must:

  • Request a security token from Security Token Service
  • Submit the security token to SharePoint Online and receive authentication cookies
  • Request a digest value and insert the value into formdigest object

Once these steps are successfully completed, you can start using CSOM code, just like you would in the browser.

Running example

Here’s the final part:

// constructor for SharePoint Online client
SPO = function (url) {
    this.url = urlparse(url);
    this.host = this.url.host;
    this.path = this.url.path;

    // External Security Token Service for SPO
    this.sts = {
        host: 'login.microsoftonline.com',
        path: '/extSTS.srf'
    };

    // Form to submit SAML token
    this.login = '/_forms/default.aspx?wa=wsignin1.0';
};

SPO.prototype = {
    signin: signin
};

var client = new SPO('http://yourdomain.sharepoint.com/yoursite');

client.signin("yourusername", "yourpassword", function () {
    
    // make sure the XMLHtpRequest includes the authentication cookies
    XMLHttpRequest.authcookies = 'FedAuth=' + client.FedAuth + '; rtFa=' + client.rtFa;

    // as of this point, you can use the CSOM service as if you are in a browser......
    // for example, we will request the properties of the site 'teamsite'.
    var ctx = new SP.ClientContext("/teamsite"),
		web = ctx.get_web();

    ctx.load(web);
    ctx.executeQueryAsync(function () {
        var properties = web.get_objectData().get_properties();
        console.log(properties);
    });
    
});

SPO represents a CSOM client (for SharePoint Online). The signin method takes care of authentication and fetches the digest value. Once signin is completed, the authentication cookies are set on XMLHttpRequest. At that point we create a SP.ClientContext and set up a query to get web properties. CSOM will fire a query to the SharePoint server and we write the properties to the console.

Full code for this example is available here: https://gist.github.com/2404924. You will need to insert the CSOM code yourself.

Let’s run the code with Node:SNAGHTML3061025

And there you have it: the SharePoint Client Side Object Model running on Node! If you want to try this yourself, I recommend using node-inspector for stepping through your code and get a better understanding of the inner workings of CSOM.

Possible scenarios

CSOM is very useful when your requirements go beyond manipulating List data. CSOM on Node may be used in scenarios like:

  • data transfer/synchronization between SharePoint with other data sources including automated creation of list/columns
  • long running processes like monitoring or content analysis
  • automated permission maintenance
  • automated creation of sites
  • upgrade of list and site definitions
  • need for CAML queries on SharePoint data
  • support for additional protocols (e.g. Web sockets)

Let me know if you have suggestions/comments.

Thanks for reading!

Using SharePoint CSOM in HTML5 apps

The SharePoint 2010 JavaScript Client Side Object Model (JavaScript CSOM, sometimes called JSOM) offers a powerful API to integrate SharePoint into your single page HTML5 application. It provides access to list data and let’s you manage sites, lists, permissions, notifications and much more.

The use of SharePoint CSOM inside SharePoint Web Part pages is well documented. In this case, your code using CSOM operates inside the context of the SharePoint UI.

But what if you want to create your own unique UI, using your favorite HTML5 UI toolkit, say jQuery UI, ExtJs, Sencha Touch, KendoUI, Twitter Bootstrap, SAPUI5, etc? I mean completely independent of the standard SharePoint UI. In this case you don’t need any of the SharePoint UI resources which SharePoint loads onto its (master) pages.

So let’s find out what JSOM actually needs and remove everything else. After some experimenting I arrived at the following minimal aspx page which includes everything you need for JSOM to connect to the SharePoint server :

 
<!DOCTYPE html>
<%@ Page language="C#" %>
<%@ Register Tagprefix="SharePoint" 
     Namespace="Microsoft.SharePoint.WebControls" 
     Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<html>
<head>
    <!-- the following 5 js files are required to use CSOM -->
    <script src="/_layouts/1033/init.js"></script>
    <script src="/_layouts/MicrosoftAjax.js"></script>
    <script src="/_layouts/sp.core.js"></script>
    <script src="/_layouts/sp.runtime.js"></script>
    <script src="/_layouts/sp.js"></script>

    <!-- include your app code -->
    <script src="app.js"></script>

</head>
<body>
	<SharePoint:FormDigest ID="FormDigest1" runat="server"></SharePoint:FormDigest>
</body>
</html>

This minimal page consists of:

  • Page language declaration
  • Define “sharepoint” tag prefix required for the FormDigest Webcontrol
  • Doctype declaration for HTML5
  • The 5 js files minimally required for CSOM
  • Include your own app code
  • FormDigest control.

When you put this aspx file on your SharePoint server (e.g. by uploading it into the Site Assets library) and request it, the server will return:

<!DOCTYPE html>
<html>
<head>
    <!-- the following 5 js files are required to use CSOM -->
    <script src="/_layouts/1033/init.js"></script>
    <script src="/_layouts/MicrosoftAjax.js"></script>
    <script src="/_layouts/sp.core.js"></script>
    <script src="/_layouts/sp.runtime.js"></script>
    <script src="/_layouts/sp.js"></script>

    <!-- include your app code -->
    <script src="app.js"></script>

</head>
<body>
    <input type="hidden" name="__REQUESTDIGEST" 
           value="0xE...[abbreviated]..35A,02 Apr 2012 08:19:47 -0000" />
</body>
</html>

RequestDigest

As you can see, the FormDigest control renders a hidden input element named “__REQUESTDIGEST” in the document body. The CSOM code will use the DOM to fetch the value of this input element and include it as an HTTP request header in the request to the SharePoint server:

X-RequestDigest: 0xE523...[abbreviated]....

Because CSOM extracts this value from a DOM element, the DOM has to be ready before you call a CSOM request.

Examples

With that, we are ready to use JSOM! Below I provide some examples which illustrate the way you can use the JSOM

You can find the full JSOM reference documentation on MSDN.

Get Web properties

The first example gets the title and description properties of a site (SP.Web class in CSOM).

// define a ClientContext for the specified SP site
var ctx = new SP.ClientContext("/teamsite");

// attach a onRequestFailed function to the client context.
ctx.add_requestFailed(function (sender, args) {
    alert('Request failed: ' + args.get_message());
});

function example () {
    var web = ctx.get_web();

    ctx.load(web);
    ctx.executeQueryAsync(function () {
        console.log("Title:       ", web.get_title());
        console.log("Description: ", web.get_description());
    });
};

window.onload = example;

Please note that:

  • ctx is defined outside of the example function, because a ClientContext is required for all examples that follow.
  • The site is explicitly passed as parameter in the ClientContext constructor, instead of using get_current() method.
  • A common request failure handler is attached to the ClientContext. This keeps the examples cleaner, by focusing on the success handler.
  • A lot of examples on the internet and even in the Microsoft documentation use a
    Function.createDelegate(this, this.onSuccessHandler) pattern by default. The goal is to ensure that the handler runs in the same context/scope as the request. This way, the handler has access to variables declared in setting up the request.
    I prefer to use the closure pattern, which I think is easier to understand.
  • The example function is called when the window.onload event fires (and the DOM is ready).

In all subsequent examples I will only provide an anonymous function which you can substitute in the boilerplate above.

Get all Web properties

You can get all properties of a ClientObject by calling get_objectData().get_properties(). Handy when you are exploring the Object Model! Here’s an example getting all properties for an SP.Web object.

function () {
    var web = ctx.get_web();

    ctx.load(web);
    ctx.executeQueryAsync(function () {
        properties = web.get_objectData().get_properties();

        console.log(properties);
    });
};

Set Web property

When this example runs, the description property of the web is changed, including a timestamp. This way you can more easily see the effect.

function () {
    var web = ctx.get_web();

    web.set_description("This web description property is updated on: " + new Date());
    web.update();

    ctx.executeQueryAsync(function () {
        console.log('Description is updated: ' + web.get_description())
    });
};

Get List items with ctx.load

If you use load method, the items will be return as a Collection. These require an enumerator to loop:

function () {
    var web = ctx.get_web(),
        list = web.get_lists().getByTitle('Contacts'),
        items = list.getItems('');

    ctx.load(items);
    ctx.executeQueryAsync(function () {
        var listEnumerator = items.getEnumerator();
        while (listEnumerator.moveNext()) {
            var item = listEnumerator.get_current();
            console.log("Contact: ", item.get_fieldValues())
        }
    })
};

Get List items with ctx.loadQuery

Alternatively, you can use loadQuery method. Please note that the return value is stored in a var for later processing in the success handler. Now you can use a forEach or for loop to run through the results.

function () {
    var web = ctx.get_web(),
        list = web.get_lists().getByTitle('Contacts'),
        items = list.getItems('');

    var contacts = ctx.loadQuery(items);

    ctx.executeQueryAsync(function () {
        contacts.forEach(function(contact) {
            console.log("Contact: ", contact.get_fieldValues())
        })

        // You can also use a for loop
        for (var i = 0, len = contacts.length; i < len; i++) {
            console.log("Contact: ", contacts[i].get_fieldValues())
        }
    })
};

Use method chaining

Instead of defining variables at intermediary steps of the object ‘path’, you can also chain methods together:

function () {
    var items = ctx
            .get_web()
            .get_lists()
            .getByTitle('Contacts')
            .getItems('');

    var contacts = ctx.loadQuery(items);

    ctx.executeQueryAsync(function () {
        contacts.forEach(function (contact) {
            console.log("Contact: ", contact.get_fieldValues())
        })
    })
};

Create a List item

Create a new item by call list.addItem. Set the field vales and call item.update. This will create the item on the server during the query.

function () {
    var list = ctx
            .get_web()
            .get_lists()
            .getByTitle('Contacts');

    // create a new item on the list
    var contact = list.addItem(new SP.ListItemCreationInformation());
    // You may even leave out the ListItemCreationInformation, 
    // if you don't set any of its properties:
    // var contact = list.addItem();
    
    contact.set_item("Title", "Peel"); // 'Title' is the internal name for 'Last Name'
    contact.set_item("FirstName", "Emma");

    // ensure that contact is saved during the query
    contact.update();

    ctx.executeQueryAsync(function () {
        console.log("Id of new contact: ", contact.get_id()); 
    })
};

Update a List item

Uses item.set_item() method.

function () {
    var id = 415,
        item = ctx
            .get_web()
            .get_lists()
            .getByTitle('Contacts')
            .getItemById(id);

    // Change business phone number.
    // Internal name is WorkPhone
    item.set_item("WorkPhone", "+31 20 123456");

    // ensure that contact is saved during the query
    item.update();

    ctx.executeQueryAsync(function () {
        console.log("New value: ", item.get_item("WorkPhone"));
    })
};

Delete a List item

Use item.deleteObject() to signal an item for deletion.

function () {
    var id = 414, 
        item = ctx
            .get_web()
            .get_lists()
            .getByTitle('Contacts')
            .getItemById(id);

    // delete the selected item
    item.deleteObject();

    ctx.executeQueryAsync(function () {
        console.log("Item deleted")
    })
};

Get List fields

Use list.get_fields() if you want to inspect the List field (columns) definitions.

function () {

    var fields = ctx.loadQuery(
            ctx
            .get_web()
            .get_lists()
            .getByTitle('Contacts')
            .get_fields()
    );

    ctx.executeQueryAsync(function () {
        fields.forEach(function (field, index) {
            var internalName = field.get_internalName(),
                title = field.get_title(),
                hidden = field.get_hidden();

            console.log('Field ', index, ':');
            console.log('Title: ', title);
            console.log('InternalName: ', internalName);
            console.log('Hidden: ', hidden);            
        })
    })
};

Get List views

This final example shows how to fetch the views for a list.

function () {
    var views = ctx.loadQuery(
        ctx.get_web().get_lists().getByTitle('Tasks').get_views()
    );

    ctx.executeQueryAsync(function () {
        views.forEach(function (view) {
            console.log(view.get_title())
        })
    })
};

Hopefully this helps you getting started with building HTML5 web applications using the SharePoint JavaScript CSOM. Let me know if you have suggestions/tips.

Thanks for reading!

Node.js, meet SharePoint

In this post I would like to introduce you to Node-SharePoint, a SharePoint client for Node.js available on GitHub and NPM. This Node module allows you to access SharePoint 2010 lists and items from Node.js. It is based on ListData.svc, the OData based REST API for SharePoint 2010. 

Getting started

Download and install Node (if you haven’t already): http://nodejs.org/#download.

This will also install NPM (Node Package Manager). Use NPM to install the SharePoint client module:

C:> npm install sharepoint

A first example

Create a JavaScript file called main.js and use the code below as a starting point:

var SP = require('sharepoint'),
    site = 'http://yourdomain.sharepoint.com/teamsite',
    username = 'yourusername',
    password = 'yourpassword';

var client = new SP.RestService(site),
    contacts = client.list('Contacts');

var showResponse = function (err, data) {
    console.log(data);	
}

client.signin(username, password, function () {
	
    // At this point, authentication is complete,
    // so we can do requests.

    // Example request: Get list items
    // showResponse is used as callback function
    contacts.get(showResponse)

});

Let’s highlight a few key topics:

  • require(‘sharepoint’) returns an ‘namespace’ object, which contains a RestService class.
  • an object of the SP.RestService class represents a client for the ListData service within the designated site.
  • ListData operates on lists, so we use client.list(listName) to create a List object.
  • client.signin implements the claims based authentication process for SharePoint Online. You can find more details about remote authentication in my  previous post.
  • once the signin is completed, the callback function is called. In the callback function you can start sending authenticated requests to SharePoint Online.
  • Please note that the SharePoint client uses the following callback convention:
function callback(err, data) {
   // err: is used to pass an error (string), if any

   // data: contains the output of the previous response or processing step
}

Provide your own credentials and site in main.js and run the file with node:

C:> node main.js

You should see the items of Contacts list in your window:

SNAGHTMLb94bb01

Here are some further example how to work with list items.

// get items ordered by FirstName		
contacts.get({$orderby:'FirstName'}, showResponse);

....

// get fist 3 items and total count of items in list	
contacts.get({$top:3, $inlinecount:'allpages'}, showResponse);

....

// get item with Id 412 from the Contacts list;
contacts.get(412, showResponse);

.....

// add a new item to the list
contacts.add({LastName: 'Peel', FirstName: 'Emma'}, showResponse)

....

// update an item	
contacts.get(412, function (err, data) {

    var changes = {
        // include the changes that need to be made
        LastName: 'Tell',
        FirstName: 'William',

        // pass the metadata from the fetched item
        // this includes the etag
        __metadata: data.__metadata
    };

    contacts.update(412, changes, function () {
        // at this point, the change is completed
        // so let's check by getting the item again
	     contacts.get(412, showResponse)
    })        
})

.....

// delete an item
contacts.del(412);

Hopefully this helps you get going in connecting Node.js to your SharePoint site! Let me know if you have questions/suggestions.