is there any way to query excel in an online environment

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

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

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?

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

When applying https://<server>/<site>/_vti_bin/ExcelRest.aspx/Shared%20Documents/ExcelTest.xlsx/model/Ranges('MainRange') 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.

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

The ongoing thread is about online, not on prem.

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

Back to Office 365 forum again

What do you mean by "Live"?

Thomas Heidt‌ can you mark any from the above answers as "Correct" or you are still looking for the answer?

Regards,

Tomasz

Thank you.  It all looks very encourageing.  I have been trying to figure out how to get the  Start event: External Start method you have listed.  I do not seem to have that in my release and have been trying to figure out where and why

 you should have it under Nintex group...