cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dual Super User III
Dual Super User III

Excel (Business) problems

Hi all

I have defined a very simple Flow#1 that is executed manually from Sharepoint when I select an Excel file in a Library. I am using Excel (Business) operations to list the rows from a table.

When executing it an error message in displayed

Excel (Business) connector Problems.png

 

Action 'List rows present in a table' failed

Fix It

If this error was temporary, you can try resubmitting the run and the issue may have been resolved.

Error type

This action failed because of an error in the connector.

Error Details

The error message from the service is below. This maybe useful for support:

BadGateway

 

This Excel (Business) action block is working fine in another flow (Flow#2) with the same connector. Also in Flow#1, both Excel (Business) and Sharepoint connectors, I am using the same corporate user. Can both connectors be used in the same flow? Is there any incompatibility between them?

Suggestions, welcome

Thank you in advance!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @ ,

 

Microsoft flow support to select a file from the picker in the "Excel (Business)" connector currently.

 

Choosing a dynamic content in the "File" field to select a file is not supported in Microsoft flow "Excel (Business)" connector currently.

 

Although the inputs on both action "List rows present in a table" blocks are apparently the same,the inputs on select a file from the picker is a friendly format of the file identifier,you couldn't choose a dynamic content in the "File" field to select a file.

 

If you want to choose a dynamic content in the "File" field to select a file,I afraid that there is no way to achieve your needs in Microsoft Flow currently.

 

If you would like this feature to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

 

 

 

Regards,
Alice Zhang

View solution in original post

12 REPLIES 12
Highlighted
Dual Super User III
Dual Super User III

Hi again

I think I have identified the problem

I have updated my flow by adding a new "Excel (Business) List rows present in table" action block.

I call this second action block "List rows present in table 2" and add it just before the first one. When executing it, the following result is displayed:

 Excel (Business) connector Problems 3.png

Both action blocks use the same connector, both of them target the same excel file, so the problem is not there. What's the difference between them?

 

Excel (Business) connector Problems 2.png 

 

In the one that works fine I select "File" input from the picker, so I navigate through the directories and choose the target file. I select "Table" input from the dropdown menu.

In the one that fails, I fill "File" input with Dynamic content. Also I fill "Table" input with a custom value.

 

Please also note the inputs on both action blocks are apparently the same:

 

 Excel (Business) connector Problems 4.png

 

 Excel (Business) connector Problems 5.png

The problem is, I want to "List rows present in a table" from the Excel file selected by the user on the SP Library, so I cannot implement the first "List rows present in a table" (the one that works); I need to the second "List rows present in a table" (the one currently failing).

 

Is this a bug? Cn you think of a possible workaround?

Suggestions, welcome

Thank you in advance!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

Hi @ ,

 

Microsoft flow support to select a file from the picker in the "Excel (Business)" connector currently.

 

Choosing a dynamic content in the "File" field to select a file is not supported in Microsoft flow "Excel (Business)" connector currently.

 

Although the inputs on both action "List rows present in a table" blocks are apparently the same,the inputs on select a file from the picker is a friendly format of the file identifier,you couldn't choose a dynamic content in the "File" field to select a file.

 

If you want to choose a dynamic content in the "File" field to select a file,I afraid that there is no way to achieve your needs in Microsoft Flow currently.

 

If you would like this feature to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

 

 

 

Regards,
Alice Zhang

View solution in original post

Highlighted

Thanks for your fast response, Alice

I've already posted a new idea. If someone reading it needs this new functionality, you can vote it here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Dynamic-content-in-File-amp-Table-inputs-Excel-Busine...



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Anonymous
Not applicable

What if you are using the file picker and still get the BadGateway?:

 

Thanks @v-yuazh-msft @efialttes

Highlighted
Frequent Visitor

This is not true. The Action "List rows present in a table" works fine with dynamic File Path. The problems you describe occurs only if you put your path together in the action itself. If you use a Variable that you define before the action it works fine.

FlowExcel1.pngFlowExcel2.png

Highlighted
Dual Super User III
Dual Super User III

Thanx for sharing, @KarimH ! I will doublecheck it next week and if so, mark your workaround as 'Accepted solution' 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted

@KarimH How did you initialize the variable FilePath? I see you used a split expression to generate the correct string but I am having trouble implementing this on my Logic app. Could you provide the method for obtaining the correct expression?

 

Many thanks,

 

-Sam

Highlighted

Hi Samlasota,

 

here is the expression for the split action:

split(split(variables('Thumb'),'items%2f')[1],'%3fversion%3d')[0]

 

I initialized the variable Thumb in a previous action with the file property "Thumbnail large"

thumb.png

 

I hope this helps

 

Karim Hamed

Highlighted
Advocate IV
Advocate IV

Just want to say that I used KarimH's method and IT WORKED. First initialize a variable that gets the value of Thumbnail Large. The name of this variable (for me) was "varThumb". Then initialize another variable for the File using this expression as the value: 

 

split(split(variables('varThumb'),'items%2f')[1],'%3fversion%3d')[0]
 
Then use the List Rows action and set the File field to the FileID variable. The table value you see in the screenshot just came in automatically.

In my case, I have a variety of users across a large organization who will create these Excel files. I get the files copied to a SharePoint library, but then I need to extract the rows within and copy them to a SharePoint list. From there, on item creation, I trigger a Flow that sends approval tasks to various people based on the contents of the line item. I write the approval responses and comments back to the SharePoint list to create a comprehensive log of approvals. 
 
I can't have users simply submit items to a list form because there may be dozens or even hundreds at a time. The Excel files are generated from SQL data -- like a report they export to Excel. I needed a way to consolidate line items from all Excel files and then get approval on each. Not a great system, but this just made everything a whole lot easier. Thanks very much for posting, KarimH.
 
Excel-FileID.PNG
 
Details on the Apply to Each step which creates the SharePoint list items:
 
Excel-FileID-2.PNG
Highlighted
Helper II
Helper II

@RachelRig 

Hello I am trying to follow your flow. I ran into a problem at the List rows present in a table step. You said that the table populated automatically for you and I am not getting that option. Could you identify where I am going wrong?

Capture1121.PNG

 

 

Highlighted

@trr0068  I replied to the email I got with your question but I'm not entirely sure that will make it back to you. So, I would just ask if you've got your Excel data formatted in a Table. The rows and columns must be in a Table named Table 1.

RachelRig_0-1605204940090.png

 

Highlighted

@RachelRig Thank you for the quick reply, yes my data is formatted in an Table

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (10,231)