cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Nintex Newbie

is there any way to query excel in an online environment

Jump to solution

We are in the process of converting old Nintex 2007 workflows to a SharePoint 2013 Online environment.  I have a workflow that used Excel Services to query information from a spreadsheet for processing.  I am only finding information on being able to do this on premise and NOT online.  Does anyone know if this is possible or if there is a work around?  If not, is this on the roadmap going forward?  Appreciate any help anyone can provide.

0 Kudos
Reply
16 Replies
Highlighted
Automation Master
Automation Master

Re: is there any way to query excel in an online environment

Jump to solution

Hi!

In nintex workflow cloud‌ this is not possible, as this platform itself does not allow you anyway to get file contents. Even when calling SharePoint REST API to get file contents I'm repeatedly facing 401, access denied issue.

When talking about sharepoint online‌ you can do it, but not directly. You cannot query the regular XLSX or XLS Excel file. Sorry. In Office 365 there is no excel services mechanism that is used for such case in on-prem and both formats are binaries. But once you convert your Excel into CSV‌ file, you can simply get it's contents, store it as a collection and then iterate through that collection to query the information you need.

Regards,

Tomasz

Reply
Highlighted
Automation Master
Automation Master

Re: is there any way to query excel in an online environment

Jump to solution

Allright - this is not possible in Nintex "online" products, but it seems it is feasible with the Microsoft Flow. I haven' tried it yet, but there is a set of actions to work with the Excel file:

So you can try to simply call a Flow workflow out of your Nintex workflow, and then the Flow will do the rest.

Regards,

Tomasz

Reply
Highlighted
Automation Master
Automation Master

Re: is there any way to query excel in an online environment

Jump to solution

I wrote a blog post about it:    

Hope it helps.

Regards,

Tomasz

View solution in original post

Reply
Highlighted
Nintex Newbie

Re: is there any way to query excel in an online environment

Jump to solution

I started exploring Excel REST.  This seems to give me the proper results in the browser.  I have been try to use it in "Web Request" and "Call HTTP Web Service" to try and get this to return what I receive in the browser.  I am struggling with the authentication though and how to structure it.  Have you had any success in trying this?

Reply
Highlighted
Automation Master
Automation Master

Re: is there any way to query excel in an online environment

Jump to solution

Frankly speaking not. Can you provide more details about the topic?

0 Kudos
Reply
Highlighted
Nintex Newbie

Re: is there any way to query excel in an online environment

Jump to solution

When applying https://<server>/<site>/_vti_bin/ExcelRest.aspx/Shared%20Documents/ExcelTest.xlsx/model/Ranges('Main...) to the url of browser the proper return of data from Excel is retrieved where "MainRange" is a named range of data that I specified.

I am trying to use this same method inside a workflow to return data for me to parse.  I seem to have two potential options in Nintex for online:

When I configure the "Web Request" action with a GET and apply my user information (which is owner and has site collection admin rights) I get a status of 200 returned and a message that contains:

<script type="text/javascript"> var l_OFUErrorImageAlt_Text = "We can't process this request"; var l_OFUErrorHeader_Text = "An error occurred"; var l_OFUErrorLine1_Text = "We're sorry, but for some reason we can't open this for you."; var l_OFUBusyIm.......

It is configured like:

For "Call HTTP Web Service", it is configured like:

but I am not sure how to structure the Request Header properly.  With a blank header it returns a status code of 0 and no info.

Reply
Highlighted
Automation Master
Automation Master

Re: is there any way to query excel in an online environment

Jump to solution

Hi Thomas,

Sorry for the delay, I really had no time to look into it earlier...

Well, first of all I am sure that you need to add FedAuth and rtFa cookies as the RequestHeader for the Web Request action (read how to do it here: Working with security credentials (RequestDigest, FedAuth, rtFa)).

However I made a correct configuration to the Web Request action, I am able to properly get the information using Postman app, but for some reason Workflow returns me "Forbidden"  Don't have more time to debug why..

So I made the workaround, from the Nintex for Office 365 I am triggering workflow in Nintex Workflow Cloud passing the cookies as the Request Body:

and then there is the action in NWC configured to Call the Web service, using received Cookie:

And it works just fine:

Now the question is, what to do next? Unfortunately NWC does not give a functionality for the "Response" - the first workflow in O365 is not waiting for any feedback from NWC, it just triggers it and moves on. So the idea could be, that it then stops and waits for the NWC to update a record in a specific list, where it could push the XML structure of the Excel file, then once the element is created/ updated it moves on, or you can create there a separate workflow, that handles NWC response. 

I hope it helps a bit

Regards,

Tomasz

0 Kudos
Reply
Highlighted
Nintex Newbie

Re: is there any way to query excel in an online environment

Jump to solution

Thx Tomasz.  It appears you are using Live.  I am trying to get a trial version ok'ed so that I can try your solution

0 Kudos
Reply
Highlighted
Automation Master
Automation Master

Re: is there any way to query excel in an online environment

Jump to solution

What do you mean by "Live"?

0 Kudos
Reply