Excel Service REST API Ribbon Button

On of my favourite areas of SharePoint BI to demonstrate is the Excel Services REST API. It’s a great way to reuse content and logic embedded inside existing spread sheets but needs to be accessed via a ‘special’ URL. The Office team has got a great blog article on how to construct the required URL to access your Excel assets via the REST API.

However, constructing these URLs requires a little cutting and pasting so I’ve created a SharePoint solution that constructs the required URL for you. When a single XLSX based spread sheet is selected in a document library, a ‘Excel REST’ ribbon button is enabled:

image

Clicking on the ribbon button displays the URL required to access the select spread sheet via the REST API:

image

Here’s how the solution is structured:

image

Firstly there is a Elements file that is used to define a ScriptLink and a ribbon button. The ScriptLink is used to inject the JavaScript library that contains the logic for the ribbon button into the page:

image

The ribbon button is defined by a second CustomAction in the Elements file and most importantly defines the JavaScript functions to be called to determine if the ribbon button should be enabled (EnabledScript) and what happens when the button is clicked (CommandAction):

image

The above two functions are contained in the excelRest.js JavaScript library which is deployed to the SharePoint root via a mapped folder:

image

This JavaScript library contains the functions required to determine if the button should be enabled (based on selected item type) and then builds and displays the REST API URL for the selected item when the ribbon button is clicked:

var _site;
var _web;
var _file;
var _selectedItemType;
var _selectedItemId;


// this function is called when the ribbon button is pressed
function invokeExcelREST() {

    // construct the rest URL for our dialog
    var webUrl = _web.get_serverRelativeUrl();
    if (webUrl == '/') {
        webUrl = '';
    }
    var restUrl = _site.get_url() + webUrl + '/_vti_bin/ExcelRest.aspx' + _file.get_serverRelativeUrl().replace(webUrl, '') + '/Model';
    
    // build the html content for our dialog
    var htmlContent = document.createElement('div');
    htmlContent.setAttribute('style', 'padding-top:10px; padding-left: 10px');
    var htmlIcon = document.createElement('img');
    htmlIcon.setAttribute('src', '/_layouts/images/icxlsx.png');
    htmlContent.appendChild(htmlIcon);
    var htmlSpan = document.createElement('span');
    htmlSpan.setAttribute('style', 'padding-left: 10px;');
    var htmlLink = document.createElement('a');
    htmlLink.setAttribute('href', restUrl);
    htmlLink.innerHTML = restUrl;
    htmlSpan.appendChild(htmlLink);
    htmlContent.appendChild(htmlSpan);
            
    // build the options for our dialog
    var options = {
        html: htmlContent,
        autoSize: true,
        title: 'Excel REST URL for ' + _file.get_name(),
        allowMaximize: false,
        showClose: true
    }

    // call our dialog
    SP.UI.ModalDialog.showModalDialog(options);
}


// this function is used to determine if the ribbon button 
// should be active or not based on the selected document type
function enableExcelREST() {
    
    // get the collection of selected items
    var items = SP.ListOperation.Selection.getSelectedItems();
    
    // check that only one item is selected
    if (items.length == 1) {

        // get the first (only) selected item
        var item = items[0];

        // get the listid of selected item
        var listID = SP.ListOperation.Selection.getSelectedList();

        // check to determine if the current execution of this function
        // is due to a RefreshCommandUI call
        if (_selectedItemId == null && _selectedItemType == null) {
            // this is the first execution of this function

            // store the selected item id
            _selectedItemId = item['id'];
            
            // prepare a CSOM query to get the selected item
            _selectedItemType = null;
            var listGuid = SP.ListOperation.Selection.getSelectedList();
            getStatus(_selectedItemId, listGuid);
        }
        else {
            // this path is called post a RefreshCommandUI that
            // is initiated from a successful CSOM query
            if (_selectedItemType == 'xlsx') {
                // we have an xlsx file type so enable the ribbon button
                _selectedItemId = null;
                _selectedItemType = null;
                return true;
            }
            else {
                // we do not have an xlsx file type so disable the ribbon button
                _selectedItemId = null;
                _selectedItemType = null;
                return false;
            }
        }
    }
    else {
        // more than one item was selected
        return false; // disable the ribbon button
    }
}

// this function gets called when the CSOM query has completed
function onStatusQuerySucceeded(sender, args) {

    // remember the selected item file type and title
    _selectedItemType = IssueItem.get_item('ows_File_x0020_Type');

    // this causes the enabledScript function to be re-executed
    // but this time we've already set the _selectedItemId so
    // a different logic path will be followed in enableExcelREST
    RefreshCommandUI();
}

// this function is called is the CSOM query bombs
function onQueryFailed(sender, args) {
    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

// this function builds and execute a CSOM query to fetch the selected item
function getStatus(ItemId, listGuid) {
    var clientContext = new SP.ClientContext();
    _site = clientContext.get_site();
    clientContext.load(_site);
     _web = clientContext.get_web();
    clientContext.load(_web);
    var lists = _web.get_lists();
    var list = lists.getById(listGuid);
    this.IssueItem = list.getItemById(ItemId);
    _file = IssueItem.get_file();

    // specify the columns we wish to return
    clientContext.load(IssueItem, 'Title', 'ows_File_x0020_Type');
    clientContext.load(_file);
    // execute the query
    clientContext.executeQueryAsync(onStatusQuerySucceeded, onQueryFailed);
}

If you want to download the completed solution, it can be found here: http://sdrv.ms/Juvbc1

I hope this helps…

About these ads

One Response to Excel Service REST API Ribbon Button

  1. ewitkowski says:

    How nobody has found this post and sang your praises is beyond me. REST URL’s arent difficult to form, but theyre a real pain in the you know what. Awesome feature, you should codeplex this. Nice job!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: