cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JPMontoya
Helper II
Helper II

Dynamic Path, oneDrive & Excel

Hi,

 

I intend to build a flow that extracts project-data from a table. This is supposed to happen for a range of projects, each with their own excel-file. To keep file-management somewhat convenient, I aim to create a Button-Flow, where the users can specify the file-path (folder-path) on their oneDrive and specify the filename.

This information is then to be used in the flow with the 'list rows present in table', respectively 'get tables' actions of the excel-connector.

 

Now, having naively tried to just put together the user-input strings as path/filename and failing in the process, I did some digging and according to what I have read in forums and in the documentation, the excel-connector respects only

  • filenames chosen via the drop-down selector in flow -> not an option for my case
  • sharePoint file-IDs -> (strongly) would like to avoid
  • oneDrive file-IDs -> a match made in heaven?!

 

So what I have tried is to use oneDrives 'list files in folder'-action to get access to folder-/file-metadata, in a first step at the root-folder level and then comparing the names from the meta-data with the user input. This then can be repeated to delve deeper into the folder structures until the specified file has been found.

The issue I am facing is that whilst the returned metadata works for identifying folders and files and for "navigation" inside of the oneDrive folder-structure, the file-ID that I ultimately end up with does not get accepted by the excel-connector. I have also tried to use the 'FileLocator' or the 'Path' attributes returned in the file's metadata but to no avail. I always end up with the following error:

Screenshot 2020-07-09 at 14.21.57.png

Now this is where my knowledge ends, but to me this indicates that excel does not accept the ID because it is a oneDrive-ID. This however would contradict the documentation, so I assume I am simply doing something wrong. Here the screenshots of the flow with a simple toplevel-folder/file setup:

 

Screenshot 2020-07-09 at 14.33.03.pngScreenshot 2020-07-09 at 14.33.14.png

 

Any help, information or ideas are welcome. Thanks in advance.

 

cheers,

JP

1 ACCEPTED SOLUTION

Accepted Solutions

Ok I got it 😎.

 

I can't say that I understand what's going on, but I got it working consistently. So what happened was that I ran the flow in 'Test'-mode from the Edit-Screen and used use 'Data from previous runs' option so I didn't have to always enter the file- & folder-name. Apparently, this at some point lead to something going wrong and the excel-connector failing completely, no matter if I selected the target manually via the file-picker or tried dynamic input.

Running the flow via the actual 'Run' functionality outside of the edit-mode fixed it for me. It now works again both in normal 'Run' mode as well as in 'Test' mode. Going back to my old flow for which I created this message-thread, I tired the same "fix" by this time re-entering the input in the 'Test' mode, which also fixed it.

Notably, both times I got the additional window, asking me to confirm the accounts for the oneDrive- and excel-connections, which I normally only get the first time running a flow with any connector. So it seems that something went wrong with that.

 

So the good news is that I got it fixed. The bad news is that I don't really understand how and why it happened in the first place, as well as if that is something I have to worry about for all my flows now. So if it is alright with you @v-litu-msft, I would be very thankful if you could still try wrapping your Assets.xlsx with a folder and see if you experience the same issues that I got.

 

Best regards,

JP

View solution in original post

4 REPLIES 4
v-litu-msft
Community Support
Community Support

Hi @JPMontoya,

 

Please try to follow these steps to get tables, I tested on my side, it works successfully:

Annotation 2020-07-10 105818.jpg

 

Annotation 2020-07-10 105830.jpg

 

Annotation 2020-07-10 105843.jpg

 

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

Hi @v-litu-msft 

 

Thanks for your response! I have been able to reproduce your result. However, as soon as I don't operate on the root-folder level anymore, meaning the file is inside of another folder, I get the same error again.

I am curious, do you experience the same issue when you wrap your Assets.xlsx into a folder? If yes, then I would think that the file-ID alone is not enough and that it somehow must be chained with ID of the folder(s) of the folder-hierarchy in which it is placed (or something along those lines).

 

Kind regards,

JP

Doing some experimenting I found out something very interesting. The part that appears to have nothing to do with the file-IDs put into the connector. It looks to be the excel-connector itself that fails to establish a connection. looking at the 'peek code'-view, it is exactly the same part that also appears in the error message.

 

Screenshot 2020-07-10 at 13.47.47.pngScreenshot 2020-07-10 at 13.51.45.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Interestingly, this now also happens when I manually select the target files via the file-picker. Now I am really, really, really curious whether that is something on my end, or whether you experience the same issue as well.

 

Cheers,

JP

Ok I got it 😎.

 

I can't say that I understand what's going on, but I got it working consistently. So what happened was that I ran the flow in 'Test'-mode from the Edit-Screen and used use 'Data from previous runs' option so I didn't have to always enter the file- & folder-name. Apparently, this at some point lead to something going wrong and the excel-connector failing completely, no matter if I selected the target manually via the file-picker or tried dynamic input.

Running the flow via the actual 'Run' functionality outside of the edit-mode fixed it for me. It now works again both in normal 'Run' mode as well as in 'Test' mode. Going back to my old flow for which I created this message-thread, I tired the same "fix" by this time re-entering the input in the 'Test' mode, which also fixed it.

Notably, both times I got the additional window, asking me to confirm the accounts for the oneDrive- and excel-connections, which I normally only get the first time running a flow with any connector. So it seems that something went wrong with that.

 

So the good news is that I got it fixed. The bad news is that I don't really understand how and why it happened in the first place, as well as if that is something I have to worry about for all my flows now. So if it is alright with you @v-litu-msft, I would be very thankful if you could still try wrapping your Assets.xlsx with a folder and see if you experience the same issues that I got.

 

Best regards,

JP

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,625)