cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Reading tables from multiple excel online

Hello, I'm trying to create a flow to consolidade data from multiple excel files on a sharepoint/teams folder:

(I'm sorry but I don't know how to post an image of my flow, but it's simple so I'll just describe it)

 

I'm trying to use the "list folders" connector from sharepoint, and it works nicely, giving me a "body" with a lot of information on the files.

 

Then I go for an "apply to each" using the last task's "body". That also works.

 

Then I go for "Get tables" or "List rows in a table" connectors from Excel Online (Business). Then I select the sharepoint site and document libraries. For the "file" parameter I've tried picking every single one from the loop's items, but none works, all of them result in an error saying the "file" expression is invalid. I've even tried choosing a sample file and noticed the flow seemed to store the file name as a path string (later I've used 'peek code' to see it's actually some kind of ID), then I've noticed this path omits the "/shared files/" I got from the "path" listed on the "body" of the "list folders" item. I've even tried to emulate the "correct" string by subsetting the "path" string from the "body", but I couldn't make that work either, because for some reason I was unable to make the length function inside the substring function work.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Reading tables from multiple excel online

Hi @Anonymous ,

 

I guess that you are using dynamic content for the File filed of action Get tables.

 

It is a known issue for Excel online connector that dynamic contents are not allowed in File and Table fields in actions of Excel Online.

 

Here is one of the similar requests on Flow Ideas Forum, please feel free vote and comment it at here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Allow-Dynamic-Content-in-Excel-File-fields/idi-p/188582?advanced=false&collapse_discussion=true&filter=location&location=idea-board:FlowIdeas&q=excel%20online%20dynamic%20content&search_type=thread

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Reading tables from multiple excel online

If you could provide an expanded screenshot of your Flow and steps, and of any detailed error messages you're receiving we could likely better assist you. You add screenshots with the Photos button in the reply window:

AddScreenshots.png

From your description, I believe what you are seeking is the ID parameter, which is part of the Dynamic content popup window after the various SharePoint related actions. Example:SharePointID.png

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Anonymous
Not applicable

Re: Reading tables from multiple excel online

Hi Brad, thanks for your reply.

 

I've tried to post my image using this button you mentioned, but it asks for a "source". I've tried the local address to the file, since I don't have access to any image hosting sites, but that didn't seem to work.

My flow is simple, it's just a "list folder" that works, then an "apply to each" with the "list folder" "body" as parameter, then a "get tables" with the same sharepoint group as "location", "documents" as the library and "ID" from list folder as the "file".

 

I've mentioned I've tried exactly this ID field, and also some other fields from the output of a "list folder" action, such as "name" and "path".

The error I get exactly is:

 

{
"status": 400,
"message": "The expression \"drives('SOME ID HERE')/items/Shared+Documents/Forms\" is not valid.\r\nclientRequestId: (SOME OTHER ID HERE)",
"source": "excelonline-ne.azconn-ne.p.azurewebsites.net"
}
 
Or when I try something other than "ID", 'name' for example yelds:
{
"status": 400,
"message": "The expression \"drives('(SOME LONG ID WITH TWO _ SEPARATORS')/items/Shared+Documents/(MY FILE NAME).xlsx\" is not valid.\r\nclientRequestId: (SOME ID HERE)",
"source": "excelonline-ne.azconn-ne.p.azurewebsites.net"
}
Could it be some kind of mismatch between a sharepoint folder and a onedrive folder, or something like this?
I'd find it weird since manually selecting a particular file from the list works fine, although it defeats the purpose of the list folder -> apply to each.
 
Thanks again,
 
João
Community Support Team
Community Support Team

Re: Reading tables from multiple excel online

Hi @Anonymous ,

 

I guess that you are using dynamic content for the File filed of action Get tables.

 

It is a known issue for Excel online connector that dynamic contents are not allowed in File and Table fields in actions of Excel Online.

 

Here is one of the similar requests on Flow Ideas Forum, please feel free vote and comment it at here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Allow-Dynamic-Content-in-Excel-File-fields/idi-p/188582?advanced=false&collapse_discussion=true&filter=location&location=idea-board:FlowIdeas&q=excel%20online%20dynamic%20content&search_type=thread

 

Best regards,

Mabel

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: Reading tables from multiple excel online

Hi @v-yamao-msft , thank you for the attention!

 

I'm sorry I was unable to figure that out from the other posts, and it is unfurtunate that I can't do what I want for now, but anyway this community is really helpful! (:

 

Best regards,

 

João

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 177 members 5,020 guests
Please welcome our newest community members: