Sometimes you may do some serious customization to a list's forms and build related business logic that precludes the use of the 'Edit in Datasheet' feature that comes with every list. Unfortunately, there isn't an 'Allow Edit in Datasheet' checkbox anywhere on the List Settings page. Fortunately, there are a couple of ways to handle this requirement that are fairly simple.

Option 1 – House of Straw with Javascript

The first way to prevent the use of this menu item is to use javascript to remove the item from the Actions menu. Here is a simple script that uses jQuery (which requires jQuery!) to remove the menu:

$(document).ready(function() { 

   $("[text='Edit in Datasheet']").remove();

});

This isn't a bad start, because it will certainly remove the menu option, but it isn't very reliable because a well informed user can simply hand-craft a URL that includes ShowInGrid=True as part of the query string.

Option 2 – House of Sticks with More Javascript and Some Permissions

You can handle the potential naughty query string by extending the javascript as follows:

var loc = window.location.toString();

if(loc.indexOf('ShowInGrid=True') != -1)

{

   loc=loc.replace('ShowInGrid=True', '');

   alert('You are not allowed to edit this list with a datasheet.');

   window.location = loc;

}

$(document).ready(function() { 

   $("[text='Edit in Datasheet']").remove();

});

There is still a potential issue here if any users have the Manage Lists permission (part of the Design permission level) to the list because they can just create a new view whose freshly generated form lacks your javascript. So, you should make sure you restrict this permission as well.

Option 3 – House of Bricks: No Script Required

The previous options will work fine if your users come at the list from the browser, but what if they have Office and can use Access or another client to link to the data? What if one of them is a secret developer with knowledge of certain magic words and arcane API's? You can protect yourself from those people by removing the following permissions from the list:

• Use Remote Interfaces (To stop the people from linking to the list with Access, etc.)
• Use Client Integration Features (To remove Edit in Datasheet. Note: also removes Export to Spreadsheet)

You can accomplish this for a single list by creating a new Permission Level that excludes these permissions. Then, simply break permission inheritance at the list and apply the new Permission Level to the groups that have access to the list.