SharePoint 2013 Hosting :: How To Edit a SharePoint List in a Gridview Using jqGrid and Listdata.svc

This post shows how to use jsGrid to create an excel-like gridview for editing items in a SharePoint list. The grid rendered by jqGrid is similar to SharePoint’s Datasheet view, but it requires no ActiveX control. Additionally it can be programmed in JavaScript to give a custom edit experience, and can be used to edit lists on different site collections or even on different farms.


The first thing we need to do is get the jqGrid libraries into SharePoint.

On this page select all the options, and click the download button. For this example I’ve extracted all the files in the downloaded zip to a new folder under the layouts directory, called jqGrid. If you don’t have access to your server, you can just as easily deploy the scripts to a document library in SharePoint.

Now that we’ve got the libraries in SharePoint,, create a new TeamSite called jqGrid. Go to the sites home page and edit it in SharePoint designer. Enter the following code at the very beginning of the pages PlaceHolderAdditionalPageHead

This will load jQuery and jqGrid onto the page. At this point it’s a good idea to save your page and then load it with Fiddler running to ensure the JavaScript libraries are loaded correctly. Now that we’ve got the libraries loaded, we need to put an HTML table and div into the PlaceHolderMain as follows:

Make sure you place the code immediately after thetag and not within another tag, Now we need to insert some JavaScript to have jqGrid build the grid in taskTable and will place paging controls, and other components that interact with the grid into gridPager. Add the following scriptblock to the PlaceHolderAdditionalPageHead just after the script tags we just added.

This code uses jQuery to find the taskTable we previously added to the page, and then calls the jqGrid method on the results passing in a single object that contains the parameters used to build the grid. The url parameter tells jqGrid where to get its data. In our case it’s “/jqgrid/_vti_bin/listdata.svc/Tasks”. This is the Rest Url for the tasks list in the new site we just created. We’ll use the Tasks list in this example, but any list can just as easily be used. The datatype parameter tells jqGrid that we are getting data in the JSON format. The jsonReader parameter tells jqGrid that the data to display in the grid can be found in the d.results node of the returned JSON object, and that the elements in d.results are not repeatable. The colNames parameter is used to add a title to be displayed for each column in the grid. The value for this parameter is an array of strings which must contain one string for each column. Finally the colModel parameter defines the actual columns to be used in the grid. The value of this parmeter is an array of objects which must contain one object for each column. In this simple example the objects within the colModel parameter just define the name attribute. The name attribute tells jqGrid the fields to select from the objects that it finds in d.results. The number of elements in the colNames and colModel must be the same — one for each column in the grid.

An explanation of these, and all the other jqGrid parameters

jqGrid is designed to work more easily with some backend code that understands the query string parameters search, row, page, etc. While WCF rest service (which SharePoint rest services is built upon) supports similar constructs I haven’t yet figured out how to have jqGrid compose each of these parameters in a dialect that WCFrest understands. But this is not an issue as WCF rest simply ignores these unrecognized parameters , and returns all items in the list.

Before we move on to turn this basic html table into an editable grid, we need to install jQuery.UI. to do this, browse to On this page, check off all the components, and then choose a theme from the dropdown on the right (the Redmond theme seems to fit well with SharePoint). Select version 1.8.21 if it’s not already selected and click the download button. Open the downloaded .zip file and extract its content to a new subfolder under the layouts directory called jQueryUI. Again, if you don’t have access to your server, you can just as easily deploy the scripts to a document library in SharePoint.

Now that we’ve got jQuery.UI loaded, we can modify the page to reference it, and begin to make the grid editable. First add the followingtag to the beginning of the PlaceHolderAdditionalPageHead, before jquery-1.7.2.min.js

Then add this script tag after the grid.locale-en.js script tag

Your PlaceHolderAdditionalPageHead should now begin like this:

This added the neccessary libraries to the page. Now we need to mark each columns as editable by adding an edit:true attribute to each column in the colModel. We can also tell jsGrid that the DueDate is in ‘ISO8601Long’ format and should be converted to ‘m/d/Y’ fomat and to use the jqueryUI Datapicker to edit it. While we’re ata it, we can adjust the columns widths. Change the colModel as folows:

and we need to tell tell jqGrid to edit the row when it is selected by adding an onSelectRow function to the parameters passed to jqGrid:

The complete JavaScript block should now look like this :

If you view the page now, when you click on a row, you will be able to edit the values in the columns, but when you press enter (which is how you save a row in jqGrid) you will get an error message because we have not defined the editUrl. The editUrl parameter is the url that jqgrid will use to save items. ListData.svc passes the url and etag of each object in d.results in fields called __metadata.uri and __metadata.etag.You can see this if you run fiddler when you load the page. When updating an item using listdata.svc you need to create an HTTP PUT request to the items uri (__metadat.uri) and pass the ‘etag’ of the item in an ‘If-Match’ HTTP Header. The JSON representation of the listitem can be passed in the body of the request. First lets add these two columns (the etag and uri) to the grid so we can see what they are (we can hide them later). Be sure to mark them as editable:false. The colNames and colModel attributes of the jqgrid parameter object should now look like this:

Then we need to adjust the onSelectRow funnction to set the grids editUrl property to the url of the selected item whenever a user selects a row

Now save the page, go to your browser, reload the page, and click on a row to edit it. When you press enter to save the row you should get an error message saying that the URI is not valid for a POST operation. We need to to modify the request to add an HTTP Header that tells listdata.svc that we really want an HTTP MERGE request and an HTTP Header to specify the etag. A MERGE operation will update only the fields that are specified and changed from current version as outlined here: We’ll also need to serialize the row data in JSON format so that it can be understood by listdata.svc. Add the serializeRowData and ajaxRowOptions as shown in the following:

Again save the page, then go to your browser, reload the page and click on a row to edit it. When you press enter to save the row you should now get an error message saying that the property name “oper” is not valid. jqGrid by default sends its commands (add, change, delete) in an “oper” field , but listdata.svc uses http headers to specify the commands. We’ll just need to remove the “oper” property from the object before sending it. We’ll also need to remove the __metadata.etag , __metadata.uri, and id properties of the JSON object before sending it becuase these are not recognized by listdata.svc Modify the beforeSend function within the ajaxRowOptions as follows:

Once more, save the page, then go to your browser, reload the page and click on a row to edit it. Change the Title and Status. It should appear that your updates were saved, but if you reload the page, you’ll see that the Status is now empty,unless you typed in a valid status. This is beacause the status field is actually a choice column(in some cases the text you typed in may be added as the new status for the item, even though it is not a valid choice). We need to create a dropdown list to let the user select from the list of valid Statuses. If you browse to http://yourservername/jqgrid/listdata.svc you will see that there are collections called TasksStatus and TaskPriority that define the valid statuses and priorities for tasks. So let’s make Status and Priority dropdowns. We will need to load the valid values for statuses and priorities into two JavaScript arrays, and then tell jqGrid to build an html select control using theses arrays to let users select a value for the status and priority columns . First let’s declare two arrays to hold the statuses and priorities, and add two new functions, called loadStatus and loadPriority. Add this code just before the buildGrid function :

The loadStatus and loadPriority functions asynchronously load the statuses and priorities into the two arrays. The functions return the actual jQuery ajax object so that they can be chained together in a jQuery ‘when’ function. The when function will wait until each of the ajax requests is complete before continuing . We’ll use this functionality to retrieve the statuses and priorities asynchronously, and only show the grid after both have been retrieved. Add a new function called pageLoad just before the loadStatus fubnction as follows:

This function effectively says “when the ajax objects returned by loadStatus and loadPriority are complete, then call buildgrid. The two ajax requests are executed simultaneously, and we won’t continue until they both complete.

Now we can modify _spBodyOnLoadFunctionNames to call pageLoad rather than buildgrid so that the grid is displayed only after we have the priorities and statuses:

Now modify the colModel specify so that both the priority and status columns render as html select elements when being edited. We use the value property of the editoptions to set the contents of the select controls to the previously created arrays:

Your JavaScript should now look like this:

Go ahead and save the file in SharePoint Designer, Refresh your browser, and edit an item. The priority and status should display as dropdowns. Change the values on a row and press enter to save. If you reload the page you should see that the values were, in fact saved. So far so good. But now try to change a row, press enter to save, and then change the row again without reloading, You should see an error saying that the etags don’t match. That’s because when we updated the item the first time, a new etag value was assigned. When we attempted to update it a second time, we sent the original etag. The original etag we sent did not match the new etag assigned after our first update so listdata.svc stops the update. It assumes someone else has update the item because the etags don’t match.

If you start fiddler, and then update an item using the jqGrid, you’ll see that listdata.svc returned an HTTP 204 response, with a an HTTP header named Etag, that contains the newly assigned Etag that should be included on subsequent Updates. So we’ll need to add a success callback to our ajaxRowOptions to get that HTTP Header and put it in our grid so that our next update will use it instead of our original etag. We’ll also need to pass the selected row to the success callback so that it knows which row to update with the newly assigned etag. There is an issue Internet Explorer that causes it to drop all headers when it receives an HTTP 204 response as documented here: To get around this, we’ll need to use a second Ajax request in our success handler to get the new etag, and pass the selected row along to the success callback of this second request.

First , change the beforeSend function on the ajaxRowOptions to add the selected row to the ajax request:

And then add the success callback to the ajaxRowOptions:

Now you should be able to make multiple updates to any row in the grid , without reloading the page.

One last issue to fix is that if you click the first row to edit it , and then click the second row to edit it (without saving the first), two rows appear to be in edit mode. When the user clicks the second row to edit it, we should cancel the edit operation on the first row, and restore its contents.

First we need to add a variable to the top of the script block to keep track of the last selected row:

And then we need to adjust the onSelectRow function

And finally, here is the complete JavaScript listing, once again