cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamic content in File & Table inputs - Excel (Business) connector "List rows present in a table"

So I am thinking on a Manual Trigger is a trigger initiated by a user from a Sharepoint Document Library for a selected file, in my case an Excel file. The next step I would like to achieve is to List rows present in a table, assuming all files stored in the Sharepoint Library are excel files with the same table structures.

 

The scenario is detailed in moredetail here: https://powerusers.microsoft.com/t5/Building-Flows/Excel-Business-problems/m-p/107609

 

According to Microsoft's Flow team response; Microsoft flow support to select a file from the picker in the "Excel (Business)" connector currently, but 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.

 

So If I want to choose a dynamic content in the "File" field to select a file, there is no way to achieve it in Microsoft Flow currently.

 

As suggested, I am submitting an idea to Flow Ideas Forum.

Status: New
Comments
Level: Powered On

I haven't tried the workaound mentioned above yet, but this seems like a pretty big issue to me. Especially when the old excel conncetor had this functionality. I really hope this gets resolved quickly. 

Anonymous
Not applicable

@amandalee you mind explaining your workaround in a bit more detail?  I'm trying to wrap my head around what you're suggesting.   I've spent hours trying to work around this !(^(&@#$% issue.

Level: Powered On

@Anonymous  sure no problems. My example is using files that I stored in Sharepoint Online (Shared Documents). I have a file I need to process each month, collect the data out of it and display it in PowerApps.

 

Each month when I receive a new file it is placed in Shared Documents/Inbox. From there I have a flow that moves the file once it has been created and places it in the Shared Documents/Processed folder. When the file is moved each month it Replaces a file with the same name in the Processed folder. By moving a file with Replace, the file name and table name don't change.

 

When I list all rows present in a table with the excel online (business) connector, I reference the file in the Processed folder and statically set the file and table name as they don't change when you use the Replace function. 

 

Make sure the file in the inbox and the processed box have the same name and that the table is always always called Table1 (this happens by default when you create a new table in excel). 

 

I hope this helps.

Level 10

Hello @amandalee 

 

As per your remarks here, you said that when file is moved to 'Processed' folder and then you execute another flow with 'list all rows present in a table', I want to know how you provide the file name on it. 

I am facing issues when I provide file name/file identifier or static file name.

 

If I provided static file name then it reverts with error - The parameter 'file' has an invalid value 

 

If I provided file name or file identifier then it revers with error - The expression "drives('b!c3zc0OntVkWt0NQnzdvi')/items/file.xlsx" is not valid

 

* This flow execute on trigger when new file is created in folder

 

 

 

Super User

John Liu just wrote a blog post with a work-around to dynamically set the spreadsheet file location.  It involves using an HTTP request to get the MS Graph ID of the file.  I totally agree this functionality should be native with the connector but this is a great work-around in the meantime.  http://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow