Nintex Workflow – Updating an Excel Spreadsheet UDA
SharePoint 2013 (Enterprise edition) comes with Excel Services. One of the lesser known pieces of functionality, is the Excel Services web service. More information on it can be found here : http://msdn.microsoft.com/en-us/library/office/ms494732(v=office.14).aspx
Since this is an ASMX web service, it can be called from Nintex Workflow using the Call Web Service action. This web service behaves a little differently to others you may be used to. You can’t just call a method to update a cell in your spreadsheet. The way to use this web service, is to first obtain a Session ID. This is then used through future calls to the web service, until you finally close your session.
Getting a Session ID
The url to the Excel Service web service is http://[siteurl]/_vti_bin/ExcelService.asmx . In order to update a spreadsheet, firstly, open up a session to the spreadsheet so that it can be edited. The web method to call is OpenWorkflowForEditing. A bunch of XML will be returned, and the Session ID needs to be extracted from the XML by clicking on the Select Element link in the action.
Notice that at the bottom of the action configuration is the Web Service Output. The “Specify Elements” needs to be checked, and then it’s possible to select the appropriate XML node. This XML browser gives you an easy way to get to the data in the XML that is needed, without having to know XPath:
The OpenWorkflowForEditingResult is the node that contains the Session ID that will be needed for any future calls to update this Excel file.
Updating a Cell
There is a trick to updating a cell using Nintex Workflow and the Excel service. The SetCell web method is what is needed to update a single cell.
When selecting that web method in the Call Web Service action, behind the scenes, a SOAP packet is being built. You can configure most of the parameters for this web method in the nice UI provided. In order to complete it though, you need to go into SOAP Editor mode. The cellValue field, for some reason is missing the namespace behind it. For this web method call to work, put in a “m:” before the two cellValue parameters.
That is all you need for the SetCell web method to work.
Closing an Excel Service Session
On completion of the update, the session needs to be closed. This requires yet another Call Web Service call, this time calling the CloseWorkbook method to close the session we have open. This will commit the update.
Update Cell User Defined Action
The reason this was put into a UDA, is that I had requirements for a project where I needed to update a single cell in multiple places in my workflow. This allowed me to reuse this functionality in different locations in my workflow, but also throughout other workflows.
The configuration of the UDA when being used in a workflow is a little messy. The parameters are not in any particular order. Wait for the next release of Nintex Workflow (after 11th March 2014) and you’ll have the ability to reorder parameters and variables and then we can make this a little more visually appealing. In the end, this works for what it needs to do.
Without going into too much of the detail, you can see from the screenshot above what the UDA design looks like. We are opening a session, checking for errors, updating a cell, checking for errors and finally closing the session. Although in the screenshot, I’m doing something naughty, in that I’m not labeling my actions, in the downloaded UDA file below, they have been labeled and should make it easier for you to understand what it is doing.
Requirements
As with most of my posts that use actions that require credentials, I use a Nintex Workflow Constant. In this workflow/uda, I’m using one called FarmAdmin. As long as you have a credential with that name, the workflow/uda should work fine.
If you have a different named constant, just update the UDA and republish it an the workflow.
