Get file identifier for list rows present in a table when using a dynamic location

[Tim_S]

I have a flow that uses "List rows present in a table". I am getting the location dynamically but I cannot find out how to get the file. From the posts I have been reading and researching, I know I need the file identifier but cannot figure out how to find it. The file will always be named the same, such as myfile.xlsx, and the table will always be Table1. Any ideas?

Thanks,

[muralidharan]

Hi @Tim_S ,

Can you please share the flow to get more visibility ?

Thanks,

Murali

[Tim_S]

Location - will be a custom value retrieved dynamically from PowerApps. Not sure if the way I am using it is correct.
Documents - will be a custom value entered as "Documents."

File - I need help with but the file will always have the same name, such as "myfile.xlsx."

Table - Will always be named "Table1."

The error details are "The parameter 'file' has an invalid value". The value is the path to the file. From what I have read on it, it needs a file identifier and not a path.

Hope this helps. Let me know if you need information.

Thanks

[WillPage]

The file identifier in the context of the Excel online API is a long string. You can parse it out from the Get file metadata action.

What you need to do here is pass in the site URL from the Power App as the dynamic content (e.g. https://mycompany.sharepoint.com/sites/dynamic%20site) and use it in the get files (Properties only) action. In the Advanced settings of that Get files action, put this into the Filter query: FileLeafRef eq 'myfile.xlsx'

This will return a collection of files within that site/library combo called myfile.xlsx. If there is only one it'll still be a collection (array) but with only one item.

Now you need to get the file ID of that file for the List rows action.

Add a Get file metadata action. Put the same dynamic site address in there as the previous action and for the Identifier, add an expression: first(body('Get_files_(properties_only)')?['value'])?['{Identifier}']

Now, add a compose and put this expression in: last(split(outputs('Get_file_metadata')?['body/FileLocator'],'id='))

Now the output of the compose can go into the List tables action as dynamic content.

A further note, you probably won't get away with typing Documents as a custom value into these actions for the library name. Use the Get all lists and libraries action and filter the output for Documents or Shared Documents

[Tim_S]

@WillPageEverything looks good up to the List rows present in a table action. I get an Invalid Request
clientRequestId: with a string Id here
serviceRequestId: with a string Id here.

If it helps, the document library is associated with a channel in Teams.

[WillPage]

@Tim_S  You are correct, I have given you the wrong info. Here's the real way of doing it:

From the Get file properties action, add a compose and put this expression in:

Now rejoin this array on /

Now do this, where the site address is your dynamic URL

Now this:

Then this:

And this. Location is your dynamic URL, Document library is the output of the Drive ID above and File is the output of File ID

Filter its output for Name = Table1

Now select its ID

..and finally use it in the List rows action

[Tim_S]

@WillPage I do not have a Get file properties action. I have a Get file (properties only) action. Did you mean that or do I need to add a Get file properties action?

[WillPage]

If you don't already know the ID of the file, find it with Get files (properties only) and specify FileLeafRef eq 'myfile.xlsx' in the Filter query.

[Tim_S · Accepted solution]

After some more research, here is what I ended up doing. I used two actions.

I created a variable for Site Address since I ended up using it a few more time through the entire flow. I get the first part of the path for the Uri from the user and finish it statically as the sub-folders and file name will be the same every time.

Location is your site address.

Document Library: outputs('Send_an_HTTP_request_to_SharePoint')?['body']?['parentReference']?['driveId']

File: body('Send_an_HTTP_request_to_SharePoint')?['id']

Table will always be Table1 and it allowed me to type Table1 as a custom value.

[VEEROCHET]

could spell out what is after root:/ in your uri?

Thanks