A Dummies Guide to SharePoint & jQuery - Reading List Data With SPServices

My most excellent and intelligent friend Lori Gowin (@lorigowin) was quite frustrated this evening because she was having issues using SharePoint and jQuery to do an image slider. After I taunted her for a while about “This is why non-developers should not be doing jQuery” I stepped down off my high-horse and thought it might be helpful to write a blog post to help her with one of the integral parts us using an image slider: reading data from a SharePoint list. 
I know there are several blogs out there that deal with examples of reading list data from SharePoint using things like Ajax, SPServices, the Client Object Model, and of course the Server Object Model, but obviously there is still an unmet need for those people who may not know where to start and just need to know the basics. Well, this blog post is for you guys. If I do my job well (which is debatable most days), after this post you will be able to read list data from a SharePoint list using jQuery and SPServices.

The Foundational Stuff

So, before we get to the meat of this blog post we need to make sure you have a few files in place and understand some of the basics.

Necessary jQuery files

First you need to download the latest jQuery and SPServices libraries. If you are going to be following this blog post exactly, make sure you put them in your “SiteAssets” document library.

Apparently there is an issue with using SPServices pre 0.7.0 and jQuery version 1.7+. More information about this can found at: http://sympmarc.com/2011/11/08/problem-with-jquery-1-7-and-spservices/ And thanks again Mr. Marc Anderson for all your efforts authoring and maintaining SPServices.

You can download SPServices here (I’m currently using version 0.7.0) and jQuery here (I’m currently using version 1.6.4). As I stated in my blog post A Dummies Guide to SharePoint and jQuery–Getting Started I like to place my scripts in a central document library that users have access to. This gives you the ability to use the awesomeness that is metadata and versioning that Document Libraries provide as well as ease of maintenance (especially during development). In addition I like to remove the version of the script from the script name and put the version in a field. For instance, I rename “jquery-1.6.4.min.js” to “jquery.min.js” and I create a field in my Document Library called “Script Version” and specify 1.6.4.  Make sense? Maybe the screenshot below will help:

This gives me the ability (after THOROUGH testing) to just upload the next version of a library and not have to modify any scripts or pages that may be referencing the old script.

SPServices –> SharePoint’s Web Services –> CAML –> Internal field names

SPServices uses SharePoint’s Web Services to retrieve data. SharePoint’s Web Services uses the oh-so-wonderful CAML query language.  It is important to understand the basics of CAML because you will need to learn to write queries that only return the data you need from your SharePoint list. Yes, I did happen to write an intro blog on CAML if you want to better understand it: Another Intro to CAML Blog.

In addition, CAML queries and responses use the Internal field names of SharePoint List fields, and NOT the Display names. So, you need to know how to find the internal field name for a field in a list item. There are several ways to do this, but I still find myself using the poor man’s method. Which is:

1) Go to the List  Settings page for your list.

2) Click on the name of the field you want to find the internal name for

3) Go to the end of the url and look for the “&Field=” parameter, what follows that is the internal field name.

Yes, many times the internal field name will match the display name. However, if someone happens to put a space or some special character in the field name, the internal field name is not at all what you would expect. Take a look at what happens when we create a field with the name “My Field & Yours”:

Did you see that? The internal field name for “My Field & Yours” is “My%5Fx0020%5FField%5Fx0020%5F%5Fx0026%5F%5Fx00”. Yes.. you read that correctly. Plus you also need to go through and replace all of the “%5F”’s with “_”’s before putting the field in your query. So your CAML query would need to use “My_x0020_Field_x0020__x0026__x00” for the query. FUN huh?  Which brings up a Best Practice. ALWAYS  create your field names WITHOUT spaces and special characters. After the field is created you can go back and add spaces and special characters. Changing the display name after the field is created does NOT affect the internal field name. Got it? Can we move on now? Do you understand what an internal field name is and how to get it? Yes, as I said, there are other ways to get internal field names, but I seem  to keep going back to this one. If you have a method you are enamored with, please feel free to share in the comments.

So… why SPServices?

Your final question before we get started (maybe your first?) may be WHY use SPServices instead of something like the Client Object Model or just calling the SharePoint Web Services directly? Excellent… you walked right into my trap. The Client Object Model only works in SharePoint 2010 and does not work for anonymous users. SPServices works the same in both SharePoint 2007 and 2010 and works just fine with anonymous users as long as anonymous users have access to the pages and the scripts. As far as just calling the Web Services directly? Well, that’s basically what SPServices does except it takes care of the heavy lifting for you and wraps the Web Service methods in easy to use functions. So, if you prefer to manually write SOAP envelopes and all that fun stuff, have at it.

Let’s Get To It!

Now that we have all that minutia out of the way, let’s dig into this script and get it working. First thing we need to do is identify which List and fields we want to retrieve data from. For the purposes of this blog lets go back to one of my previously used lists called “Speakers”. From this list, I want to retrieve the speakers Name, Picture, and Blog url.  I will then insert this data into a standard HTML table on the page. I wanted to keep this script as simple as possible for you newbies, so I am not doing any advanced processing with the row data.

Please look at the very heavily commented script below to understand some of the nuances of using SPServices and SharePoint’s Web Services:

<script type="text/javascript" src="../SiteAssets/jquery.min.js"></script>
<script type="text/javascript" src="../SiteAssets/jquery.SPServices.min.js"></script>

<script type="text/javascript">


//this is where the script starts after the page is loaded
$(document).ready(function() { 

    GetSpeakers();

});

function GetSpeakers()
{
        //The Web Service method we are calling, to read list items we use 'GetListItems'
        var method = "GetListItems";
        
        //The display name of the list we are reading data from
        var list = "Speakers";

        //We need to identify the fields we want to return. In this instance, we want the Name (Title),
        //Blog, and Picture fields from the Speakers list. You can see here that we are using the internal field names.
        //The display name field for the Speaker's name is "Name" and the internal name is "Title". You can see it can 
        //quickly become confusing if your Display Names are completely differnt from your internal names. 
        //For whatever list you want to read from, be sure to specify the fields you want returned. 
        var fieldsToRead =     "<ViewFields>" +
                                "<FieldRef Name='Title' />" +
                                "<FieldRef Name='Blog' />" +
                                "<FieldRef Name='Picture' />" +
                            "</ViewFields>";
                            
        //this is that wonderful CAML query I was talking about earlier. This simple query returns
        //ALL rows by saying "give me all the rows where the ID field is not equal to 0". I then 
        //tell the query to sort the rows by the Title field. FYI: a blank query ALSO returns
        //all rows, but I like to use the below query because it helps me know that I MEANT to 
        //return all the rows and didn't just forget to write a query :)
        var query = "<Query>" +
                        "<Where>" +
                            "<Neq>" +
                                "<FieldRef Name='ID'/><Value Type='Number'>0</Value>" + 
                            "</Neq>" +
                        "</Where>" +
                        "<OrderBy>" + 
                            "<FieldRef Name='Title'/>" +
                        "</OrderBy>" +
                    "</Query>";

        //Here is our SPServices Call where we pass in the variables that we set above
        $().SPServices({
                operation: method,
                async: false,  //if you set this to true, you may get faster performance, but your order may not be accurate.
                listName: list,
                CAMLViewFields: fieldsToRead,
                  CAMLQuery: query,
                      //this basically means "do the following code when the call is complete"
                    completefunc: function (xData, Status) { 
                        //this code iterates through every row of data returned from the web service call
                        $(xData.responseXML).SPFilterNode("z:row").each(function() { 
                            //here is where we are reading the field values and putting them in JavaScript variables
                            //notice that when we read a field value there is an "ows_" in front of the internal field name.
                            //this is a SharePoint Web Service quirk that you need to keep in mind. 
                            //so to read a field it is ALWAYS $(this).attr("ows_<internal field name>");
                            
                            //get the title field (Speaker's Name)
                            var name = ($(this).attr("ows_Title"));
                            
                            //get the blog url, SharePoint stores a url in the form of <url><comma><description>
                            //We only want the <url>. To accomplish this we use the javascript "split" function
                            //which will turn <url><comma><description> into an array where the first element [0]
                            //is the url.   Catch all that? if you didn't this is another reason you should be
                            //a developer if you are writing JavaScript and jQuery :)
                            var blog = ($(this).attr("ows_Blog")).split(",")[0];
                            
                            //same thing as the blog, a picture is stored as <url><comma><alt text>
                            var pictureUrl = ($(this).attr("ows_Picture")).split(",")[0];
                            
                            //call a function to add the data from the row to a table on the screen
                            AddRowToTable(name,blog,pictureUrl);
                            
                        });                
                    }
        });

}

// very simple function that adds a row to a table with the id of "speakerTable" 
// for every row of data returned from our SPServices call. 
// Each row of the table will display the picture of the speaker and
// below the speaker's picture will be their name that is a hyperlink
// to the speaker's blog.
function AddRowToTable(name,blog,pictureUrl)
{
    $("#speakerTable").append("<tr align='middle'>" + 
                                "<td><img src='" + pictureUrl + "'><br><a href='" + blog + "'>" + name + "</a></td>" +
                               "</tr>");
                                
}

</script>

<!-- table where our speaker rows will go -->
<table id="speakerTable"></table>

There are a LOT of quirks with the way SharePoint returns different field types. It's always helpful to throw a quick alert($(this).attr("ows_fieldName")); to see what the actual returned value is.

At this point, all you need to do now is save the script and upload it to your document library and then link to the script in a content editor web part (or whatever method you choose to get the script loaded on a page).

If you want to see this script live in action so you can be sure I’m not just making this stuff up you can go to:  http://www.sharepointhillbilly.com/demos/SitePages/GetSpeakers.aspx

And there you have it

So, hopefully I was able to break this down enough to where you can now write your own script to read specific items from a SharePoint List.

I highly recommend you bone up on your CAML and only retrieve the rows you care about as this can be a huge impact on performance. Also, keep in mind that alerts are worth their weight in gold.. well.. they don’t actually weigh anything.. but they are still important for quickly debugging issues. If you are having problems, the best place to put an alert is right before the call to GetSpeakers(); as follows:

$(document).ready(function() { 

    alert("your code is at least executing");
    GetSpeakers();

});

If you don’t get the alert, then you know your code is not executing at all. This usually means one of your scripts is not loading. If one of your scripts is not loading, it’s because the path to it is not accurate.

Ahhhh… debugging jQuery in SharePoint… that is a whole other deep black pit of despair.. are you SURE you want to do this?

Anyway, if I need to explain anything in more detail please let me know!  Thanks again for stopping by…