How to Dynamically Disable Datasheet Adds and Updates

Wow, okay, so I think I’ve finally recovered from SPC and unburied myself from a majority of my workload. What’s next on my list? Ah yes, a blog post. I remember I used to do those once upon a time. So, I thought I’d do a quick blog post on how to stop users from adding and updating items in the datasheet view if they match some given criteria. Maybe you only want certain SharePoint Groups to have access to the datasheet View? Or maybe the datasheet view should not be allowed if certain fields are changing? Why would you want to do this?? I’m so glad you asked!

The Problem

Client side scripting has lead to a huge improvements in usability of SharePoint applications and more specifically SharePoint forms. Things like dynamic drop down lists that used to be a pain to develop can now be created in a matter of minutes. Before you know it, you can have a great looking, usable form with various dynamic content including hidden fields, dynamic lookups, and fields that are required only if other fields contain data. It’s a great time for SharePoint usability. 

However, what happens when a user decides to go to the Datasheet view for the list that you worked so hard to create these awesome forms for? No longer do your rules apply for dynamic required fields. Users can willy-nilly select whatever value they want for those once pristine dynamic drop down lists. Before you know it, your list is riddled with a host of forms with invalid data combinations. Users yell and blame the developers. The developers come up with new colorful nicknames for users who can’t follow directions. Mass chaos ensues. Tempers flair. Unions walk. Rumor has it this is what really caused the demise of Hostess.

If only you had some way of controlling who had access to the datasheet view or to stop certain fields from being changed in the datasheet view!!!

At this point you are probably saying.. uh…. why don’t you just disable the Datasheet view in the List Settings? Well, yeah, you COULD do that, but the datasheet is such a cool and powerful tool! Why would you want to disable it completely? Wouldn’t you just rather stop some of the pesky users from using it? Or maybe you just want to make sure that it’s not one of your dynamic fields getting updated? Hmmm… starting to see the possibilities?? 

However, if all you want to do is disable the Datasheet View completely, you can accomplish this under “List Settings->Advanced Settings->Specify whether the datasheet can be used to bulk edit data on this list.”  (Thanks @TashasEv for making sure I mention this).

A Solution

So, the solution I came up with is pretty simple and straightforward (like myself). Please, I beg of you, don’t just go and try to reproduce this solution on your environments without knowing what you are doing. If you implement this solution incorrectly it could disable adding and updating of all list items in your Site Collection (until you deactivate the feature anyway). Alright, now that you are going to ignore my warning and do it anyway. Let me explain the steps needed (no I’m not supplying a final pretty solution that you can deploy. Sorry, I’m not THAT unburied from my work).

1. Create a Content Type and Site Column

The first thing we want to do is create a Content Type. Give this Content Type a unique name like “DatasheetBlocker”. Then create a Site Column for this Content Type called “AllowUpdate”.  This site column should be a “Single line of text” field. Set the “Maximum Number of Characters” to 2.  You can also set the default value to “No” if you want to. You can do this with Visual Studio or through SharePoint, whichever you prefer, but if you plan to deploy this to multiple sites I recommend you put this all in a solution.

Add this Site Column and Content Type to any list that you want to disable Datasheet adds and updates.

2. Create an Event Receiver

Create an Event Receiver for the List Item Events “ItemAdding” and “ItemUpdating”. This very simple Event Receiver Simply checks to see if the List with the item being updated or added has the Content Type “DatasheetBlocker”. If it DOES have the Content Type “DatasheetBlocker” it checks to see if the  value of “AllowUpdate” is “Yes”. If the value is NOT yes, then it means the update is coming from the Datasheet and additional logic can be added to determine if the add/update should be allowed. The code for that might look something like:

    public class BlockDatasheetAddAndEdit : SPItemEventReceiver
    {
       /// 

       /// An item is being added.
       /// 
       public override void ItemAdding(SPItemEventProperties properties)
       {
           if (!IsAddUpdateAllowed(properties))
           {
               properties.Cancel = true;
               properties.ErrorMessage = "This form cannot be created from this view.";
           }
           else
           {
               base.ItemUpdating(properties);
           }
       }

       /// 

       /// An item is being updated.
       /// 
       public override void ItemUpdating(SPItemEventProperties properties)
       {
           if (!IsAddUpdateAllowed(properties))
           {
               properties.Cancel = true;
               properties.ErrorMessage = "This form cannot be updated from this view.";
           }
           else
           {
               base.ItemUpdating(properties);
           }
       }

       private bool IsAddUpdateAllowed(SPItemEventProperties properties)
       {
           //check to see if the list has the ContentType used for blocking datasheet adds and updates.
           if (properties.List.ContentTypes["DatasheetBlocker"] != null)
           {
               //if the value of "AllowUpdate" is null or if the value is not "Yes" then do not allow the add or update
               if ((properties.AfterProperties["AllowUpdate"] == null || !properties.AfterProperties["AllowUpdate"].ToString().Equals("Yes")))
               {
                   //the data is being updated or added from the datasheet view
                   //add additional logic to determine if the add or update should be allowed
                   // return false if not allowed
                   // return true if allowed
                   return false;
               }
               else
               {
                   //the add or udpate came from our form, so we will allow it. 

                   //clear out the value of "AllowUpdate" so that it is no longer "Yes"
                   properties.AfterProperties["AllowUpdate"] = null;
                   return true;
               }
           }
           return true;
       }

    }

Now, could I have just checked for the field “AllowUpdate” instead of first checking for the Content Type? Absolutely. Why didn’t I? Well, a user might have created a field called “AllowUpdate” for their list, but had no idea that this solution was deployed on their Site Collection. By first checking to see if the Content Type has been applied to the list, we can be sure that this list is supposed to be governed by our Event Receiver.

Now after you deploy this Event Receiver, go and try to add or update an item to your lists that you added the Content Type to.  Notice something something?  Yeah, you can’t add or update ANY items at all. Also, you can’t type the word “Yes” into the AllowUpdate field on the forms or the DataSheet view. It only allows you to type two characters.  That’s where this final critical step comes into play.

3. Add a script to the add and update forms which sets the value of “AllowUpdate” to “Yes”

Okay, sorry if you were expecting some magic, but it’s simple brute force to be sure. And since you are probably using some sort of script on your add and update forms anyway to achieve your dynamic drop downs and conditional fields then it’s not that big a deal to add one more line of script to those forms.  So, go in and write a quick line of script that sets the value of “AllowUpdate” to “Yes” and then hides the field “AllowUpdate”. That script might look something like:

   1:  

   2:   

   3:  

Now add this script to your New and Update forms. If you were REALLY lazy, you COULD deploy this script globally using a Custom Action on your Site Collection and it would set the value of AllowUpdate to “Yes” on ALL the forms in the Site Collection that used this field.  However, if a user happened to have a field called “AllowUpdate” on their forms that had a different meaning, you’d be making them upset.  Plus, I REALLY don’t like the thought of executing unnecessary script on every page even if it is only one line of script.

4. Ta and Da

There you have it.  Now, when you go to the New and Edit forms for your list items, the value of “AllowUpdate” is getting set to “Yes” under the hood and the Add and Update is allowed to happen.  If the user opens up the list in the Datasheet view they cannot make any adds or updates unless it meets your predetermined criteria (using the event receiver above as-is will not allow any datasheet adds or updates). 

Okay.. okay.. before you get all huffy and leave a comment, Yes… it’s very easy to get around this Datasheet Blocker by simply editing the Site Column to allow for 3 characters and then a user could type the word “Yes” in the Datasheet view.  Just don’t give users access to modify the field and you won’t have to worry about it. If a user has the permissions and the knowledge, then they can get around this feature. However, that’s not the case for my scenario, but something you should be aware of.

What I like about this solution

So yeah, this solution has its plusses and minuses, but here’s why I really like it:

• Sandbox baby! This entire solution (including the Event Receiver) can be written as a Sandboxed Solution, making it easily deployable and maintainable.
• There may be a several pieces to this solution, but it is very simple and maintainable. The custom code is kept to an absolute minimum and there should be very little upgrade issues with this solution. If you decide to go with this solution, please make sure to clearly document how it works for those coming in after you, but even the most junior developer should be able to maintain it.
• Time and effort – It literally took me 30 minutes to design, develop, and test this solution. That’s a great value for the client and allows me to get on to other work.

What I don’t like about this solution

So, as much as I like it, there are things I’m not enamored about.

The Event Receiver is executed for every List item add and update. So, depending on your site, this may not be good. You could modify this Event Receiver to attach to specific Lists and then do away with the Content Type completely. This would be a good idea for some deployments, however it breaks the ability to add the functionality to additional lists by just adding the Site Column and Content Type to the list. Might be something for you to consider though. (This is actually how I have it deployed for my client).

Have a better solution?

Of course there are so many ways to skin a cat in SharePoint. This is not “the” solution. It is “a” solution that my specific requirements for functionally, time, and budget. At the end of the day, it performs well, meets the clients needs, and didn’t impact schedule or budget at all.

I imagine I could improve upon this solution by creating a Custom Field Type for the “AllowUpdate” field. This custom field could have logic built in to do what the script was doing from my solution and then you have fewer parts to deploy and maintain.  However, a Custom Field Type can’t be deployed as a Sandboxed Solution… so, pick your poison I guess. Also, the more custom code you add, the more potential you have for upgrade issues in the future.

Whatever you do, make sure you test the heck out of it, document what you did for those who come after you, and think about any upgrade impacts your solution may have.  I know, basic stuff, but still good to put it out there.

But hey, if you have a better solution, be a fountain and not a drain. Share with the class!