Updating excel from the web
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 Farm Admin. But I don't think you would be able to do this with the out of the box actions. It might require custom action or web service that uses the Excel API to open the document at let it recalculate and save it.
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.
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. 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. Without going into too much of the detail, you can see from the screenshot above what the UDA design looks like.
This is then used through future calls to the web service, until you finally close your session. 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 Open Workflow For Editing.
Note Workbooks that exceed 30 megabytes (MB) cannot be viewed in Excel for the web from within Microsoft Share Point Online.
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.
For this web method call to work, put in a "m:" before the two cell Value parameters.
That is all you need for the Set Cell web method to work.But I'm trying to use this in more efficient way and to update multiple cells in one call to service using method set Range. I am able to call the Open Workbook For Editing method successfully, and it returns a session ID as it is supposed to.