Power Automate: Access Excel with a dynamic path II

I have to say I’m surprised by the reaction that ”Power Automate: How to access Excel with a dynamic path.” got. It’s a big problem, and it’s a prevalent one.

But I feel that the article doesn’t help everyone. Well, it’s not a question of feeling; just look at the comments, so I want to address each item in the comments and provide examples in how to achieve them. I hope this helps as many people as the other article is currently supporting. So I wanted to write another article explaining how to access excel using a dynamic path part II if you will. 

How to import it?

When I thought about fixing this issue, I created two templates so that people can import it and use it, but here’s how to do it.

1. Return the first value in the Excel
2. Return all the values in the Excel

Download the template and let’s import it.

Then go to:

Select the file and upload it:

Power Automate asks about your connections. Select or create new ones.

If all goes well, you should see a screen like this:

After that, select the Power Automate from your list:

And edit it to check that all is correct:

We have the template imported.

There’s only one extra step. You need to change the path to the SharePoint that has the Excels that you want to access.

How to use it?

Like I mentioned before, using it is the part where I tried to make it as simple as possible. Here’s what you need to do. In the template that you imported, you need to find the URL. This is the way that you can call the template from any of your Power Automates. It expects two parameters:

1. The path
2. The table inside the Excel

Locate your file

First, I created a file and uploaded it to my SharePoint instance. It’s a simple test file used to demonstrate, but the size of the file is not essential. Please remember that you’re using a dynamic path so you can pass to the HTTP whatever you need. Here’s my test Excel.

And inside, you can find:

Ok, I’m not the most creative person, I’ll admit it :).

I called the table “TEST.” To name yours, you need to go to “Table” and change the name:

Please don’t confuse this with the sheet’s name. That’s not important for our case.

Call the template

In your Power Automate, add the following action:

I’m using a manual trigger to show you that you can put any kind of parameter (variable, compose, etc.) in the call, and it will work.

Get the results

Let’s test what we got so far:

Looks good. Now let’s parse the information. Let’s add more rows to the Excel.

And let’s run again to see what we get.

Parse the results

We need to parse the JSON that comes from the response. To do that, you can use the following Schema:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "ID": {
                "type": "string"
            },
            "NAME": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "ID",
            "NAME"
        ]
    }
}

To do this, you can get the return value in the Body and use the “Generate from sample“ in the Parse JSON

Now you have all the data; you can use a “Apply to each” on the Body and parse the results as you want them.

Small BIG caveat

What if your path has spaces? That will return an error, right? Not really because our template has a safeguard regarding that.

If you supply to the “Send an HTTP request to SharePoint” with something like this:

/Folder With Spaces/Test Excel.xlsx

You’ll get an error. For the string to be usable in an URL, you need a “Compose” before that “encodes” it.

It’s a simple formula:

replace(triggerBody()?['path'],' ','%20')

Final thoughts

I tried to make this as detailed as possible, but with the templates and the explanations above, I’m sure that you’ll be able to do it and understand what you’re doing. If you have questions, just reach out, and I’ll try to help the best way I can.

Have a suggestion of your own, or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.

Photo by Nathan Dumlao on Unsplash