Excel Service REST API Ribbon Button
May 30, 2012 1 Comment
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:
Clicking on the ribbon button displays the URL required to access the select spread sheet via the REST API:
Here’s how the solution is structured:
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:
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):
The above two functions are contained in the excelRest.js JavaScript library which is deployed to the SharePoint root via a mapped folder:
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…