cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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
Brad_Groux
Super User II
Super User II

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

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

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

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
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (9,604)