Power Automate: How to access Excel with a dynamic path

[manuelstgomes]

One of the questions I see a LOT in this Forum are issues with accessing Excel files where you provide a dynamic path.

TL;DR: Download the template and have your problem fixed in 2 minutes.

It works in other actions in Power Automate, so why not here? Well, there’s a limitation in Power Automation that doesn’t allow that. The issue is that the exception is not clear at all, making people question if their Flows are correct or not.

It occurs on OneDrive files as well as SharePoint, so I’ll use OneDrive to demonstrate the issue and the solution.

The issue in detail

Let’s try to access the information in an Excel file. It’s quite straightforward. Just use the “Get a row“ action, and we’re good to go:

So far, so good. So now, to simulate the dynamic path, let’s put the path in a Compose action. It’s the same. We’re passing a path to Excel; we’ll use the same path, the same Excel, the same Table, and the same ID/Column combination.

Running we get:

Worse yet the error is quite cryptic:

The error itself doesn’t tell us much, but the issue is with the dynamic path since we’re using the same strategy, just building the path before using it. It isn’t stated directly in the official documentation, but there are only three ways to access the file (quoting from the documentation):

• Pick a file from the file picker.
  -Use output from the OneDrive for Business connector’s triggers/actions (file’s Id or File identifier property depending on which one is present for the particular OneDrive for Business’s action or trigger).
• Use output from the SharePoint connector’s triggers/actions (file’s Id or Identifier property depending on which one is present for the particular Sharepoint’s action or trigger).

So no dynamic path mentioned. It’s a strange limitation since, if we use the same strategy to access any SharePoint file, we don’t have this issue, but I digress. It’s a limitation, and we have to deal with it. So, how do we deal with it?

The Solution

Note Before we go any further, I have a template that you can find in my Template Archive ready for you that you can upload and have all. Use it and use the following information as a reference. I have another article that describes in detail how to call an HTTP triggered Power Automate in case you’re not familiar with that.

The solution can look a little bit complex, but I’ll guide you through each step of the process.

First, since I use Excels all the time, I decided to build a separate Flow that takes care of this for me. I’ve enclosed all the logic there and, once Microsoft fixes the issue, I need to replace that action with the future action, and that’s it.

So let’s look at the steps:

Roughly what we’re doing is triggering the Flow with external parameters, fetching the Excel details, and using its ID to get the information. As I mentioned before, accessing an Excel file using an ID is perfectly valid, so let’s use that.

The Trigger

The trigger is the most straightforward part of the process. If you’re not familiar with how to call another Flow using an HTTP call, I have an article that explains this in detail.

The input is what we would expect:

1. The path of the file
2. The table we want to access

Here’s the definition in case you want to build it yourself:

{
    "type": "object",
    "properties": {
        "path": {
            "type": "string"
        },
        "table": {
            "type": "string"
        }
    }
}

The Tricky Part

Now we have to use the “Send an HTTP request to SharePoint” action. You may be using Power Automate for a while and never had the need to use this action, but it can be quite handy when some of the pre-defined actions don’t cut it. With this action, we can access Sharepoint using the SharePoint’s API. Actions like “Get Item“ for example, use the same API, but they are just abstracting the technical details. In this case, we have to go a little bit deeper, but it’s simple to understand:

So we’re making a “Get” request to SharePoint to get the information about a file that exists on a particular path. The syntax is the one required by the API, so we’re just providing it and adding the path that we get from the trigger.

What do we get in return?

We get what we need — the Excel’s ID. Now we can do a call to Excel to obtain any information we want since the ID is a valid way to call Excel.

@{body('Send_an_HTTP_request_to_SharePoint')?['id']}

Simple right?

Let’s get the information we need

I included in this template examples in how to get “Worksheets,” “Tables,” and “Rows” so that, in case you need any of them, you can adapt the template to get that information.

We can call all of them the same way:

So now we have the information. All we need to do is to send it back from the Flow.

Returning the information

Sending the information back is quite easy. We have a “Response” action that can help us with this.

In this template, I used the most restrictive return so that you know how I do it. Since I want to get one row only I’m doing a:

first(body('List_rows_present_in_a_table')?['value'])

If you wish to return all of them, remove the “first” and return a JSON with all the information.

Making things easier

I’ve built a template that you can find in my Template Archive that is independent of other Flows, so you can import it and use it in multiple Flows. Adapt it any way you like to get the information that you need.