Thanks to Dan Parish for writing this blog post.
In todayâs post, Iâm going to delve into some of the finer details of collaboration using Excel Web App. If you havenât already read Collaborative Editing Using Excel Web App however, please start there. It goes over what is supported, how it works, our presence UI, as well as some interesting collaborative scenarios that this feature opens up. Todayâs post is really a âPart IIâ to that post.
Multiple Browser Windows
Excel Web App allows you to collaborate with yourself, so if you forget to close a browser window while editing a workbook at work, you can still continue editing that workbook at home. To let you know if a user has multiple windows open and editing the same workbook, we display a notification icon in the presence UI with a tooltip that appears when you hover over it:
Collaborating with Excel Servicesâ Web Service API
Excel Services (which ships as part of SharePoint Server) allows you to build programmatic solutions using its Web Service API that can also edit workbooks. When Excel Services and Excel Web App are installed together, users will be able to edit a workbook through Excel Web App at the same time as that workbook is being editing with Excel Services programmatic solutions. If you are editing a workbook in Excel Web App that is also being edited by a programmatic solution, you will see the name of the person that is running the programmatic solution along with a notification icon in the presence UI with a tooltip that appears when you hover over it:
Collaborative editing, saving, and SharePoint audit logs
Whenever you edit a workbook in Excel Web App, your changes are automatically saved back to SharePoint while you edit. When you are editing alone, your changes are always saved as âyouâ, so you will show up as the person that modified that workbook in SharePoint. When multiple people are editing a workbook simultaneously however, the workbook needs to be saved as someone. So, when editing collaboratively Excel Web App will always save the changes as the last user to join the editing session.
However, SharePoint can also be setup to keep audit logs of all of the changes on the SharePoint system. This allows administrators to track when files were created, updated, deleted, etc. and by whom. The âby whomâ piece is very important since if an important workbook is modified in a way not allowed by corporate compliance strategies the person that made that change will need to be questioned.
Having workbooks that are being modified by multiple people at the same time then presents an interesting challenge: how does the administrator find out who made a given change if multiple people were editing at the same time, and if the workbook was saved out as only the last person that joined the editing session? To solve this problem Excel Web App writes all of the users in an editing session to the audit log whenever it saves a workbook with multiple users in it. If a workbook subsequently needs its changes audited, administrators can check the audit log to see who was actually editing the workbook at any given time.
Limitations of collaborative editing
Editing in the Excel client
The Excel client does not support collaborative editing with Excel Web App. Excel does support something similar, called Shared Workbooks, which allows multiple users using Excel to edit a shared workbook simultaneously, but it does not support the real-time collaboration of workbooks in SharePoint or Windows Live that Excel Web App supports. As a result, if you must edit a workbook simultaneously in Excel and Excel Web App, the user in Excel will be âlocked outâ and will have to work on a read-only copy and merge in their changes at a later time.
Because of this limitation, the âOpen in Excelâ button on the ribbon in Excel Web App will show an error if it is clicked while you are collaborating:
Excel Web App supports undo and redo just like you would expect it to work from your experience in the Excel client. However, in order to prevent confusion across a collaborative editing session, Undo/Redo is disabled while you are collaborating. If you click Undo or Redo while collaborating, you will see the following error:
Editing workbooks with external data or User Defined Functions (UDFs) that return personal information
For security and privacy reasons, workbooks with external data connections and workbooks with UDFs that return personal information (that have [UdfMethod(ReturnsPersonalInformation = true)] as a property) must be checked out in SharePoint before they can be edited in Excel Web App. If a user tries to edit one of these workbooks without first checking it out, they will see the following error:
The concern is that through either of these mechanisms a user could expose private data to other users unknowingly or unwillingly. When a workbook is checked out, it cannot be collaborated on and its editing session is therefore private, which ensures the security and privacy of a userâs data.
I hope that this post gives you a little bit more insight into how collaboration works in Excel Web App, and shows you some of the types of scenarios it can enable. If you have any questions and/or comments, please post them in the comments section.