SharePoint jQuery: Managing Scripts

In the two previous posts, I've shown some jQuery scripts for manipulating the SharePoint user interface by adding the scripts directly as source text in a Content Editor Web Part (CEWP). This is, however, not a good way of adding the scripts to pages considering maintenance and sharing of the provided functionality across multiple pages. Just think of making a correction to the generic scrolling view with frozen header script to improve its performance, after adding it directly in CEWPs on tens or hundreds of view pages.

A better approach is to store the scripts as files that are included in CEWP just like the jQuery library itself. You can use a single document library to store the script files; but as some of your jQuery scripts will be more generic (reusable) than others, I would recommend using several managed document libraries based on script classification.

I like to classify the scripts like this:

• Unique: the script is unique to the web page
• Shared: the script is shared between multiple related web pages, typically for all view pages of a specific list
• Common: the script is common for unrelated web pages, typically for view pages for more than one list

Store common scripts as include files in a ”MasterScriptLibrary” in the root site-collection to make them common for all site-collections within the same SharePoint web application. This includes the jQuery script itself. Store shared scripts as include files in a "ScriptLibrary" per managed site-collection to make them shared between all sites and subsites within the site-collection. Use a document library in the top-level site of each site-collection as the script library to store your jQuery JavaScript files.

In addition, any custom CSS style sheets you use in your scripts should also be stored in the script libraries. Remember to assign ”Read” permissions on the script libraries to the group ”Authenticated Users”.

Unique scripts need no script library as they are included directly in CEWPs. Really large companies should consider having a global script library that stores jQuery files used across all SharePoint web applications.

Add the include files in this order in a CEWP: jQuery, styles, common and then shared. Add any unique script after all included styles and script.


Try to avoid using more JavaScript files than strictly necessary, as many include files will impact the load time of the page.

Always use page relative (../path/file.js) or server relative (/path/file.js) URLs in the <script src= > attribute. See this post for how to get site relative URLs. Avoid using absolute URLs at all costs. The web application root site-collection is the "server" in SharePoint terms.

If you don't want to expose and manage the scripts in document libraries, you can always provision the script files to the [12] hive using a feature: SharePoint jQuery deployment feature. See this EndUserSharePoint post for more alternatives for managing include files.

Related slidedeck

SharePoint Site Relative CEWP Included Script

This post is about injecting site relative JavaScript include files in SharePoint using CEWP. It is not about injecting JavaScript files using the server-side ASP.NET script manager or any other server-side mechanism.

We all know that the src attribute of the <script> element is either page relative or server relative; or God forbid, absolute. You cannot use the tilde (~) to make the URL site relative, but you can achieve this using the SharePoint page L_Menu_BaseUrl variable and inject the include file into the page dynamically:

<script type="text/javascript">
document.write('<script',
' src="' + L_Menu_BaseUrl +'/ScriptLibrary/SiteSpecificScript.js"',
' type="text/javascript"><\/script>');
</script>

This actually makes the include file URL server relative, but without hardcoding the site path into the URL - making it equivalent to being site relative.

Excel Solutions in SharePoint

In my last post, I talked about the situational solutions that users themselves has created in Excel - and how these isolated workbook islands counters knowledge management and the ability to leverage the information and people ecosystem of your company. To subject the Excel workbook sprawl to shared knowledge management, you must provide Excel-style services as components of your Enterprise 2.0 (E2) platform.

The nice thing about Excel workbooks is that it gives the users great flexibility and information processing capabilities in a tool that is quite simple to learn and use. The flexibility of Excel must be preserved if you are to gain the promised E2 benefits: innovation by harvesting your company's collective intelligence through sharing and collaboration.

This post is about the state of most existing Excel 2003 and 2007 solutions I've seen, and how to incorporate them into the SharePoint 2007 platform. I will also talk about the future of Office automation (VBA, macros) and Excel Services/Excel Web Access in the upcoming SharePoint 2010 (SP2010).

Excel has good integration with SharePoint lists, and storing data in SharePoint lists has the benefit of shared data entry and collaboration on the list and collateral information. In Office 2003 a linked SharePoint list can be edited in both Excel and SharePoint and the changes then synchronized back and forth. The list can even be edited offline. This two-way link synchronization mechanism was removed in Excel 2007 - data can still be linked and the link updated, but there is no ootb way to edit that data in Excel 2007 and push it back into Excel. A list sync add-in exists, but this will require installation on client PCs, possibly an issue in most large companies.
Still, data can still be edited in SharePoint lists and linked into Excel workbooks to use e.g. the impressive charting capabilities of Excel. The simplest way to do this is to switch the list into datasheet view and open the task pane using the action menu (click to enlarge figure). Export the data as a linked list to Excel, create the chart or pivot table from the linked list, enable auto-update of the linked list, then save the workbook back into a document library. Your users can then open the workbook to do reporting based on the live SharePoint list data.

So, what if you want to have those charts and pivot tables as parts of your SharePoint sites? Wouldn't it be nice to have a unified, shared Excel-style workspace?

Excel 2003 users can try this: Publish a graph/chart created in MS Excel onto your SharePoint Site. Note that Excel 2003 uses the Office Web Components (OWC) that are deprecated. Excel 2007 users can also publish charts as web-pages as shown here. In both cases, the Page Viewer web-part is used to show the published workbook elements.

If you have Excel 2007 and MOSS enterprise edition, then the workbook can be published to Excel Services and instead be made directly available as part of SharePoint through Excel Web Access!?? You think, but there are pitfalls...

Excel Services (MOSS enterprise edition only) is a natural choice for enabling shared access to workbook solutions, while allowing the users to create the workbooks themselves. This combines the flexibility of Excel while providing shared management and publishing of the solutions. There are, however, some limitations like no data editing, no VBA macros or add-ins and, the most painful in MOSS, SharePoint Excel Services cannot consume live data stored in linked SharePoint lists! The latter limitation is a very common topic on the Excel Services forum. According to information on the forum, this problem is very likely to be fixed in SP2010. In the meantime, have a look at consuming SharePoint lists using Excel UDF.

The same problem applies to SQL Server Reporting Services (SSRS), there is no simple ootb way to report on data stored in SharePoint lists. Again, this problem is very likely to be fixed in SP2010. In the meantime, have a look at consuming SharePoint lists using SSRS. Another issue with using SSRS is that there is no self-service provisiong of new report types, not exactly enabling E2 freeform, emergent social collaboration on solving business problems.

Also keep an eye on the coming Office 14 web applications and the integration with SP2010. This might give the best of both Excel and SharePoint Excel Services, with a more seamless edit-publish mechanism than today. Maybe even working editable shared web-enabled workbooks will become a reality.

So if you somehow enable your Excel Services workbook to consume data from SharePoint lists (I won't even mention the unsupported database view approach), then you will most likely want to provide a richer user experience by allowing filtering of the information shown in the Excel Web Access web-parts (EWA). And provided that there are more EWA web-parts on your page, you may also want to connect them, e.g. when clicking a cell in one EWA web-part triggers an event that affects other EWA web-parts.

The EWA web-parts are filter connections consumers. The MOSS filter web-parts can be used to build a powerful filtering UI that connects to Excel Services parameters that do the actual data filtering. Note that the Filter Web Parts are in the MOSS enterprise edition only, so if you're on standard MOSS then you're left with the rather crude HTML Form Web Part or 3rd party filtering web-parts.

You can also use the ExcelServicesAjax.js script in a Content Editor Web Part to connect EWA web-parts using JavaScript and to use AJAX to access the Excel Services API. Refer to Shahar Prish's blog or his book Professional Excel Services for more details. To avoid scripting connections, whole worksheets can be published instead of multiple single elements. The latter requires less of non-technical users.

If Excel Services is not an option for you, then have a look at Dundas Charts for SharePoint or FusionCharts. Dundas can consume data from a variety for data sources, and Dundas has ootb support for SharePoint lists. This gives the users the E2 flexibility to create mashups by connecting data to charts themselves, within the collaboration platform. The Fusion Flash components also include maps, but there seems to be no ootb "for SharePoint" integration.

The chart creation user experience with Dundas is quite good when all that is needed is configuration, but there is a disconnect to SharePoint terms that will put non-technical users off (e.g. Dundas use "int32" instead of "number", and what happened to "choice"?). In addition, any customization needed beyond configuration, will require VB.NET/C# knowledge, again a duh! for user self-service solutions.

These charting web-parts are also subject to the same filtering and connections aspects as the EWA web-parts. Dundas uses parameter filtering and supports standard web-part connections. Note the lack of wildcards or (all) or (blank) choices in the filter parameters - this makes Excel style chart filters on text values impossible to recreate.

All in all, if you have data in SharePoint lists, then using SharePoint linked workbook lists in the Excel client may be the best approach while waiting for SP2010. If your data is in a store supported by Excel Services, then I would recommend using Excel Services as this combines user flexibility, self-service, sharing and knowledge management.

I had intended to write about what to do with Excel VBA macros and linked workbooks, and the future of VBA with relation to Excel Services, OOXML, VSTA/VSTO/.NET and Office 14-16 in this post, but due to the length that will be the topic of another post.