Hacking the Datasheet View / Quick Edit in SharePoint 2013 to display in an IFrame

Scenario

• I created a document library list view in datasheet (quick edit) mode for display in a custom popup dialog in a SharePoint 2013 Visual Studio project "application page".
• This grid was used to make last minute changes to metadata values of selected items before continuing an existing process in a larger solution.
• I needed to display the list view using an IFrame pointing to a URL filtering the values by IDs.
• This was a large list exceeding the list view threshold
• I needed to hide all chrome, navigation, ribbon, and other elements surrounding the list view web part.
• I needed to call a JavaScript function outside the list view page from a button on the list view page.
• Note that I couldn't figure out how to convert the application page into a web part page which is why the list view web part is included via an IFrame rather than embedded directly into the page, however the same issues would apply except without the need for a hidden button or the chrome trimming.
• An alternative to using the list view web part in datasheet mode would be to use a datagrid control and manage the CRUD programatically, but this would be harder to maintain if the SharePoint schema changed down the line and would require much more coding to implement correctly.

Issues

• In Datasheet view the last record edited (or possibly the only record) will only save if you select another record (which you can't even do if there's only one record). This was an issue for 2 reasons:
• There was a custom button in the app that when clicked would continue the process without saving the record that was just edited.
• The stop editing functionality which would normally be used to save the last record resulted in a list view threshold error dialog due to a bug in the datagrid / Quick Edit mode when in a folder or using filter parameters and using the "Stop editing this list" link because it forwards to a standard view without the filter or folder.

Resolution

• Implementing the IFrame: A bug in Visual Studio .Net 4.5 framework IFrame web control requires the control to be instantiated manually in the code behind rather than automatically in the designer code file

protected global::System.Web.UI.HtmlControls.HtmlGenericControl iframe1;

• The IFrame was added to the application page like so:

 <iframe name="iframe1" id="iframe1" ClientIDMode="Static" runat="server" height="400" width="400" seamless" />

• The IFrame was instantiated dynamically using server side code that set the filter parameters in the query string like so (itemids are in a semicolon delimited format):

iframe1.Attributes["Src"] = "/sites/SiteName/LibraryName/Forms/DatasheetViewName.aspx?IsDlg=1&FilterName=ID&FilterMultiValue=" + itemids;

• I hid a button (id = btnCopy style:display = none) on the application page which ran the application code and added the following JavaScript function to the application page so that it could be called from the list view page from within the IFrame.  Note the timed delay here as I'll explain this later.

<script type="text/javascript">

    function triggerBtnCopyClick() {

        setTimeout(function () { document.getElementById('btnCopy').click(); }, 1200);   

    }

    

    ....  existing code used by the application page that handled the btnCopy click event.

</script>

• I added a script editor web part to the Datasheet List View page and configured it to remove the chrome elements still remaining after IsDlg=1 did its work.  Note that your class names may be different based on the WPQ number (find using your browser's developer tools).

<style>

#s4-ribbonrow{display: none;}

#Hero-WPQ2{display: none;}

#CSRListViewControlDivWPQ2{display:none;}

</style>

• Finally, I added the button and code to call the parent page's triggerBtnCopyClick code.  You will need to find the GUID of your list view web part (webpartid) and plug it into the code.
  This is the part that involved hacking the datasheet view by finding the function calls the "Stop editing this list" button used to save the final changes and reusing them in my own code.  The downside to this is that the grid needs to be refreshed afterwards and takes a second to finish processing so I added a 1200ms delay into the triggerBtnCopyClick function above that processes the code after the save.

<input type="button" id="SaveEdits" value="Save and Process" onclick="var gridInitInfo = g_SPGridInitInfo[('{WebPartID GUID HERE}')]; var ganttControl = window[gridInitInfo.controllerId]; var ganttControl = window[gridInitInfo.controllerId]; ganttControl.TryDispose(function(dlgReturnValue) {window.location.reload(false);}); this.style.display = 'none'; window.parent.triggerBtnCopyClick(); return false" />

#SharePoint Lookup Column DefaultValue Datasheet View Bug

Scenario

• You have set a Lookup Column's default value property via code
• There is no way to set this via the SharePoint UI with the exception of the workaround to default the first item in the lookup by making the column required
• You receive the following errors
• "The text entered for ColumnName isn't an item from the list." dialog box
• The spreadsheet will not save the row and an invalid entry window will pop up

Side Note
I have developed a SharePoint solution that will allow a user to specify column defaults from a root site list for all instances of a Site Column within a specified site. For example, a company has several offices that each have a category that may change.  Each office has it's own team site. Every document and list item in the company must also have the office category populated for filtering and searching.  The user would set the office category from an office list at the root site which includes the office team site url, at that time, my tool updates the default value in each list/library where that site column is used within that team site.  If you would like more info, please comment to this post or contact Kiefer Consulting.

Issue

•  Datasheet view does not work properly
• You will run into errors if any of the following are the case
• The client is running Office 2010 64bit with the 2007 Office System Driver: Data Connectivity Components workaround
• The name and id are swapped in this case.  If you set the defaultvalue to the backwards combo the default will work for this client configuration but will not work with the standard new item form or other client configurations.
• Ex: "Name1;#1"
• The client is running an older version of Office
• A defaulted lookup is multi-select
• You may run into errors if the following is the case
• A defaulted lookup is not visible within the Datasheet view

Resolution

• There is only one unique circumstance where Datasheet view will work with standard formatted defaults
• Office 2010 32bit is installed on the client
• The lookup columns are single-select
• All defaulted columns are visible within the Datasheet view
• To code for maximum datasheet support, you must use a repeated id format workaround
• Ex: spfieldlookup.defaultvalue = "1;#1;#2;#2"
• This will solve the "The text entered for ColumnName" issue for multi-selects and non-visible fields in Office 2010 32bit
• This will also work for Office 2007 for single-select columns, and non-visible multi-select fields.  You will still get the error for visible multi-select fields
• The side effect to this format is that the id value will be visible, instead of the display value, until the user moves off the row.