September 25, 2022

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

Manuel Gomes

I'm a previous Project Manager, and Developer now focused on delivering quality articles and projects here on the site. I've worked in the past for companies like Bayer, Sybase (now SAP), and Pestana Hotel Group and using that knowledge to help you automate your daily tasks

View all posts by Manuel Gomes →

5 thoughts on “Power Automate: Access Excel with a dynamic path II

  1. Are path and table supposed to be swapped? I’m sort of following this……

    So we import your template, but need to build a totally separate flow in order to get info needed to run your template? So we need to build a manual one to fetch a URL?

    1. The idea of having the template is that you can re-use it in any Flow. So you have a place that deals with all the Excel “stuff” and returns the data you need to process it.
      This way, if you need to fix a problem in the parsing, you only need to fix it once and in one place.

  2. Also “paste the URL from the template here” – the Azure URL? That’s the only template URL. The sharepoint URL is user provided, and not really “from the template”

    1. Hi Erik,
      The template has a “When an HTTP request is received” trigger. Open that trigger, and you’ll find an URL generated for you by Power Automate.
      That’s the URL to be copied.
      Cheers!

Leave a Reply

Your email address will not be published.