Creating an ExcelServices JavaScript UDF - A working Sample

Excel Services in SharePoint 2013 are offering some new functionalities. One of these are JavaScript User Defined Actions (UDFs). There is currently one a small documentation on MSDN covering this topic. The given MSDN sample contains a small example which didn't work at all. Because a customer would like to see the new features Excel Services are offering in 2013 (including Office 365). I had the choice to discover this feature by myself :)

First some technical background, JavaScript UDFs have to be hosted inside of the same website as your Excel WebPart and only in the scope of the current site your custom JavaScript UDFs will be available! Faced with this limitation there are only a few scenarios where JS UDFs make sense in my opinion. (MSDN says also something about SkyDrive embedding.... but SkyDrive is not my favorite topic.. so if you've any SkyDrive related scenario.. please leave a comment).

Okay to get started with creating our JS UDF we need to inject some JavaScript into our site. SharePoint offers different ways how to achieve this. For this example I've just created a .txt file and uploaded it to the Site Assets library. Within my WebPartPage I reference the Script using a ContentEditorWebPart. A JS UDF is nothing special it's just a JavaScript function which is registered within the current instance of an ExcelWebPart. So another pre-requirement is a configured ExcelWebPart on your WebPartPage.

The following script contains a simple JavaScript function converting an input string into UpperCases. (Yes I know that's not an actual benefit, consider calling an external HTTPs Service returning some data as a real life example). I've splitted the file to ensure a better readability.

Part 1: Defining an UDF

Part 2: Registering the UDF

Within Part 2 it's important that your script is running after ExcelWebPart has finished loading the Excel JSOM. See the following script which contains the entire JS logic.  

The entire sample code

If you've also loaded jQuery on your WebSite, you can of course use jQuery, to delay script execution until the current document is loaded as shown here.

The result

Once you've referenced the JS file by using the ContentEditor WebPart and refreshed the site, you can easily access the UDF by editing a cell and typing in a '=' sign followed by the name of your UDF.

In order to make this easier to use, I've added JS UDF Support to ShareCoffee's backlog. So check out ShareCoffee recently to see ExcelServices JS UDF's appearing there.  

Comments

comments powered by Disqus