Power Automate: How to access Excel with a dynamic path

One of the questions I see a LOT is the Power Automate Forum are issues with accessing Excel files where you provide a dynamic path. 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:

If we run it we get something like this:

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.

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 Romson Preechawit on Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

19 thoughts on “Power Automate: How to access Excel with a dynamic path

    1. Hi Avi,

      Thanks for the kind words.

      Regarding MS Forms I got you covered in another article here

      Jump to “Before we start” to see how a form is triggered. Then you have 2 choices:
      1. You can have all the code in another Flow to re-use in multiple flows. To achieve that you can check here how to generate a Flow as a service and call it to get all information
      2. You can paste the code after you get the items from the Form and use some of the parameters to call the Excel.

      I know this looks scary, so give it a go and, if you can’t proceed, just drop me a comment with your Flow or email me and I’ll help you from there.

      Cheers
      Manuel

  1. This is great! Exactly what we need at this moment.
    I wonder if this method will work for the “Add a row into a table” action for Excel though.

  2. Hello Manuel, thanks for this tutorial.
    But in my case it’s not working:
    1) I want access to an excel located in “documents” library
    2) I’m getting file properties for a specific excel by using REST API or the SharePoint Action “Get File Properties” (I used both, with the same error) to get the file ID
    3) By using the action (Excel connector, i.e. Get worksheets) the File is not found by selecting the file’s ID property

    any suggestion?
    Thanks

      1. Hi Manuel, thanks for your reply.

        After some hours of work I found the issue in my flow and fixed it.
        Basically I was getting the SharePoint ID of the excel file by using SharePoint connector actions or “classic” rest API (i.e. “_api/web/lists(”)/items?”), instead of getting the MS Graph ID by using v2.0 endpoint as you explained in your tutorial and in the shared flow.

        It was a carelessness that cost me lots of hours of work, but that’s okay: I learned something new.

        Thanks again,
        Francesco

        1. Hi Francesco,

          Those are fantastic news. I’m sad about the time lost, but you solve the problem! Well done!

          Any help, let me know, and I’ll try to help you.

          Cheers
          Manuel

        2. Hi Manual,

          Thanks a lot for your post! it’s very helpful!
          But my case is not working too. it’s similar to Francesco’s case.
          I put below in the Url and I do get the file information in Json. but when I tried to get worksheet or table, it’s not working. I tried both id and UniqueId.
          _api/web/GetFolderByServerRelativeUrl(‘/sites/PowerPlatformDemo/Application%20Recertification/2020/04/ADP/rework1’)/Files(‘ActiveUser202004.xlsx’)

          got json from http GET request:
          {
          “d”: {
          “__metadata”: {
          “id”: “https://XXX.sharepoint.com/sites/PowerPlatformDemo/_api/Web/GetFileByServerRelativePath(decodedurl=’/sites/PowerPlatformDemo/Application Recertification/2020/04/ADP/rework1/ActiveUser202004.xlsx’)”,
          “uri”: “https://XXX.sharepoint.com/sites/PowerPlatformDemo/_api/Web/GetFileByServerRelativePath(decodedurl=’/sites/PowerPlatformDemo/Application%20Recertification/2020/04/ADP/rework1/ActiveUser202004.xlsx’)”,
          “type”: “SP.File”
          },

          “CheckInComment”: “”,
          “CheckOutType”: 2,
          “ContentTag”: “{5F263209-32C1-48F4-9C64-22FE0CAEC96C},2,4”,
          “CustomizedPageStatus”: 0,
          “ETag”: “\”{5F263209-32C1-48F4-9C64-22FE0CAEC96C},2\””,
          “Exists”: true,
          “IrmEnabled”: false,
          “Length”: “18513”,
          “Level”: 1,
          “LinkingUri”: “https://XXX.sharepoint.com/sites/PowerPlatformDemo/Application%20Recertification/2020/04/ADP/rework1/ActiveUser202004.xlsx?d=w5f26320932c148f49c6422fe0caec96c”,
          “LinkingUrl”: “https://XXX.sharepoint.com/sites/PowerPlatformDemo/Application Recertification/2020/04/ADP/rework1/ActiveUser202004.xlsx?d=w5f26320932c148f49c6422fe0caec96c”,
          “MajorVersion”: 1,
          “MinorVersion”: 0,
          “Name”: “ActiveUser202004.xlsx”,
          “ServerRelativeUrl”: “/sites/PowerPlatformDemo/Application Recertification/2020/04/ADP/rework1/ActiveUser202004.xlsx”,
          “TimeCreated”: “2020-05-03T20:22:24Z”,
          “TimeLastModified”: “2020-05-03T20:22:24Z”,
          “Title”: “”,
          “UIVersion”: 512,
          “UIVersionLabel”: “1.0”,
          “UniqueId”: “5f263209-32c1-48f4-9c64-22fe0caec96c”
          }
          }

          I tried below in the URL too, I have this file for testing which just been created under Document folder
          _api/v2.0/drive/root: Book.xlsx
          get 400 error
          Unexpected response from the service
          clientRequestId: 3cba37ea-09da-422f-ba91-4d869bcd9f63
          serviceRequestId: 9c454f9f-2025-a000-55c3-513f52f31595

          when you get a min, can you help me?

          1. One thing I need to mention is I created the flow in a solution.
            I tried outside the solution and it works!

  3. Hi Manuel,
    I would be interested to find out if there was a way to complete a Microsoft Excel sheet, save it to SharePoint Online and use the row text in a single column as “questions” in a Microsoft Form?

    Right now I am able to create the MS From and on final completion it populates a SharePoint list, Microsoft Word document and updated MS Planner. I would like to have more control over the questions in MS Forms and either use a SharePoint Online custom list or Excel sheet which ever is easier or possible.

    Thanks for this information it was extremely useful.

    Regards
    James

    1. Hi James,

      I don’t have anything written about that, but I found this article that may help you:
      https://davidlozzi.com/2018/02/06/use-microsoft-forms-to-collect-data-right-into-your-excel-file/

      It allows you to load information directly from an Excel file and generate a Form, but there’s always manual work. I haven’t explored this yet, but I think you won’t be able to automate it fully.

      But you raise an exciting question and challenge. Let me explore further and get back to you with a solution.

      Cheers
      Manuel

  4. Hi Manuel,

    I’m currently trying to use your method of passing the path to an excel file between flows (thanks very much for writing this up!), and then getting the file ID, but I am encountering an error response
    status”: 400,
    “message”: “Unexpected response from the service\r\nclientRequestId: e4348ad9-4f86-4c86-914f-d2f2787dc766\r\nserviceRequestId: d4db4c9f-c033-b000-43c3-9949d5c417d9”,
    “error”: {
    “message”: “Unexpected response from the service”
    }
    Could I possibly email you some screenshots of my flow to see if you can point out where my problem lies?

    Cheers,

    Jordan

  5. Hey Caroline,

    I’m glad that you added the other comment; otherwise, I would be turning my head trying to find the issue

    Solutions are tricky regarding connections. I tried to simulate your error and found one of my own. When I try to save, I get this beautiful message when I try to run it:

    “Request to Azure Resource Manager failed with error: ‘{“error”:{“code”:”InvokerConnectionOverrideFailed”,”message”:”Could not find any valid connection for connection reference name ‘shared_sharepointonline_2′ in APIM tokens header.”}}’.”

    I haven’t seen yet a decent article explaining the limitations of solutions (probably need to do some research and write one myself), but this looks like an excellent example of a flaw or a bug. I found the following thread in the forum:

    https://powerusers.microsoft.com/t5/General-Power-Automate/Add-existing-flows-to-Solutions/td-p/258103/page/2

    “New Sharepoint related Flows can be created within solutions, but existing Sharepoint Flows still cannot be added to them. This is really unfortunate, since most of our existing flows have Sharepoint triggers or Sharepoint actions.”

    I tried this myself, and it didn’t work.

    All of this to say, solutions are a wonderful concept, but still not ready to be used. They create more issues than they solve, so I would recommend, for now, to keep Power Automates outside solutions. If you want to access child Power Automates, you can always use the HTTP request and get the same result.

    Hope this helps and sorry for the bad news.
    Manuel

  6. Hi Manuel,

    Thanks for the blog that was helpful.
    I have a similar use case in which I am passing dynamic values to excel online connector and able to do the following actions “List rows in a Excel”, “Delete row in a excel”. But I am struggling with Adding / Updating a row in excel. Have you tried these actions out with parameters?

    Thanks
    Bhushan

    1. Hi Bhushan,

      That’s a tricky one. When we reference the Excel directly, the connector adds the Table’s fields so that we can add each field. In this case, the connector doesn’t know Excel, so it cannot understand what the fields will be.

      Let me investigate further and let you know.

      Cheers
      Manuel

  7. Hi,
    I’m trying to set up a flow that trigger by “selecting a file”(excel file) from the sharepoint list library document.
    And pull all the data from the table update to a sharepoint list.
    I am able to pull the data from a specific file, but If i try to select another file in the Sharepoint library document, it keeps giving the same data from the previous file.
    Also for the “lists rows present in a table” I have to choose a specific file in order to get the table information for that action.

  8. Hello,
    I’m trying to set up a flow that trigger by “selecting file” on sharepoint lib document. It is going to be a excel document.
    After I select the file the flow would run, and get all the data from the excel sheet and update the data to a new sharepoint list. However, I cannot set up a flow that a select dynamic file on the flow. All I can do is selecting a specific file and pull that file’s information. Is there a way to set up a flow that would run by selecting any excel file that saved at sharepoint lib document and update information to a sharepoint list.
    Thanks.

Leave a Reply

%d bloggers like this: