Power Automate – Excel Dynamic Filename

In the Excel connector we are forced to use the file picker (folder icon) to select a Excel file. But what if we did not know the filename, or we are expecting NEW Excel files.. We will need a dynamic filename.

I have learnt this method from John Liu. I have pointed so many people, and community users to John’s blog when they run into this Excel connector limitation. I have found multiple people thinking its too complicated, or confusing, because the method works with Graph API to get the File ID.
So this blog will be a very easy guide to follow and hopefully will be able to get your Flow being able to use a dynamic filename. If you have any questions or problems with this guide or other Power Automate related questions, reach out to me on Twitter.

Things to Know

To make this even easier, we will be having the Excel file that be stored into a SharePoint Document Library. We can also use the SharePoint HTTP call action to better build our REST call more easily.
* We will still need the Excel file to have a table defined *

Getting Started

First we will create a new SharePoint Document Library for the files, or use an existing document library.

Next, we want to have one Excel file in the Library to start, so we can make sure this is working. Here is what mine looks like:

My example, I have a Document Library called ‘Excel Files’ with a Excel file to start with

Overview of The Flow

For the Flow, I am using the SharePoint action ‘Send an HTTP request to SharePoint‘. This action uses MS Graph in the background, but makes it a lot easier to construct a API call.
First we will use a simple call to SharePoint using the ‘Send an HTTP request to SharePoint‘ action to get all the Document Libraries for our site.
Than use the output of that action to find the Document Library Drive ID.
This is how we navigate through the SharePoint site, we want to do this until we are in the folder of our Excel File.

Lets Build that Flow

First we add ‘Send an HTTP request to SharePoint‘ action

Uses Graph API in the back-end

In the SharePoint HTTP action, use the following:
– Site Address: If your SharePoint site does not show up in the drop down, click use ‘Enter custom value’ and type the homepage of your site
– Method: GET
– Uri: _api/v2.0/drives/
– Headers: accept application/json

Now trigger the Flow to get the Output of the HTTP request.

You can also read and get what you need through the Output Body window

I am using Visual Studio Code to paste the Output into, its free, and easy to use.
If you have lots of Document Libraries, you may want to use a Find feature and search for the Document Library name. For this demo I will be searching Excel Files.
We need to look for a specific ‘id’ inside the output

Copy this id,we can use this value to navigate inside that Document Library in our HTTP request. Our new URI should looks something like:
_api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8

Now we need to add /root:/ to the end of our URI

We are almost complete! We now need to specify the path to our file. Here are some examples:

If file is directly in the Document Library(No Folders) – Just add the filename with the extension
_api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8/root:/MyNewExcelFile.xlsx

If file is in a folder – Add the folder name, than the Excel file with the extension
* NOTE: If folder name has spaces, than use %20 instead of a space *
Folder name = Historical Files
_api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8/root:/Historical%20Files/MyOldExcelFile.xlsx

Mine is not in a folder so I will be using the first one. Now to make the file dynamic, I will be using a Compose action to store the Filename. So in my HTTP Request I will be using my Compose action with the Filename and extension at the end. My example will look like this:

The Filename, can come from any Dynamic content, I am just using a Compose for the sake of this Demo

Now test the Flow to validate the HTTP request is valid. The output should have some references to the file, like the name of the file, size, created date time, and last modified date time. My output looks like this:

Next, to get the file ID, we use a Compose action with an expression.
Add a Compose action, inside the Compose we will use the HTTP request Body dynamic content in a expression:

With the fx logo present on the dynamic content tab. Click the Dynamic content Body. Now we use JSON path to grab the file ID.
This is done by adding a ?['id'] to the end of the expression. My expression looks like this:

body(‘Send_an_HTTP_request_to_SharePoint’)?[‘id’]

body('Send_an_HTTP_request_to_SharePoint')?['id']
You can copy my expression, just make sure to change the Send_an_HTTP_request_to_SharePoint to your HTTP request action name

Next add an Excel Get tables action. Use the outputs from the Compose – get file ID for the File

Now add an Excel List rows present in table action.
File: Use the outputs from Compose – get file ID action (same as we did for Get Tables)
Table: Click Enter custom value. Use this expression to get the first table name
first(body('Get_tables')?['value'])?['id']
Power Automate has been updated the new expression to get the tables is:
first(outputs('Get_tables')?['body/value'])['id']
** Same as before. Get_tables is my action name **

first(outputs(‘Get_tables’)?[‘body/value’])[‘id’]

Success!!

Now, sometimes the Dynamic content of the Excel columns wont be there now. You can easily grab any column you like using the item() expression. For example I have a column named Tasks so my expression will be
item()?['Tasks']

item() can only be used inside an Apply to each

Conclusion

Being able to use a Dynamic filename in the Excel connector can be tricky. But I am hoping with this guide, anyone who needs this business requirement can easily follow this, and get it done.

Again I wanted to give a shout out to John Liu. I originally learnt this method from his blog, which can be found here:
http://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow

Thanks for reading. If you have any questions or problems with this guide or other Power Automate related questions, reach out to me on Twitter.

19 thoughts on “Power Automate – Excel Dynamic Filename”

  1. Hi,

    I have a problem . And I’m thinking if you can help me.
    So, the problem is: I have an excel file with data named source.xlsx. There are ten columns. One column is the name of the forms (form ABC, form XYZ, form QWE… etc.). I have also the excel files with the name corresponding to the name of the files.(ABC.xls, XYZ.xls….) The files are present.
    And I want to automatically, each day, get data from the file source.xlsx and put the rows with data to the corresponding name of the files, row ABC to file ABC.xlsx and so on… The problem is that I have each day about hundred rows to sort and put the the about hundred files. How to solve it ?

    Many thanks for the suggestions.

    Like

  2. Hi Josh,
    This looks exactly what I need to resolve an issue where I want to copy data from Excel files (held in the Doc Library) to another Sharepoint list. with your solution though, when I run the HTTP step at the start, i don’t see any Excel file listed in the output. Are you able to advise please?
    Thanks..
    Paul.

    Like

  3. Hello,
    I’ve followed your first 2 steps. I built out he compose with my file extension in the inputs (Auto%20Upload%20Teams%20Doc%20Only/EA%20RTH%20candidate%20for%20Teams%20Review.xlsx) and also built out the Send a HTTP request to SharePoint step with the Uri with the ID I pulled and the dynamic Outputs at the end of the uri line (_api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8/root:/Outputs).
    However, I am receiving an “unexpected response from the service clientRequestID” error. Any ideas?

    Like

    1. Hi Emily – Make sure when using a dynamic path inside a Compose action, that you either select Dynamic content tab and click on the ‘Compose’ outputs. OR,

      You can also use the outputs expression Like this:
      outputs(‘Compose’)

      Try copying and pasting this into your URI field:
      _api/v2.0/drives/b!MJxrwkJ2FUGanaxoijg0eZmnU9iP4aJJsidrZJQNlsBAb-8VLzYJRbD9CKmJa0j8/root:/@{outputs(‘Compose’)}

      Like

  4. Hi Josh, thanks for this in-depth explanation. I’m a complete novice but up to try and experiment. I wanted to ask this question to ensure I understand this function well so I can also experiment.

    I want to save weekly reports to a folder within a SP library, when I save the weekly file to the library, I was hoping to use the ‘When file is created in folder’ trigger so that the flow would then read the xlxs file and send a custom email to the people in the ‘Email’ column.

    With your function, could I incorporate that in? The file name is the same apart from the date changes within the title every week.

    Thanks for any help or guidance.

    Like

    1. Hello Lebene,

      If you’re using the Trigger “When a file is created in a folder”. Your work is much simpler.
      Using this trigger gives you the File ID as dynamic content. So you could follow the blog. But wont have to worry about using the HTTP action to get the file drive ID.

      Like

  5. Hi Josh
    First off – great walkthrough, really clear and helpful.

    Unfortunately when I try this, when I get to the Excel connector part (I’m just using ‘Get Worksheets’ as my Excel file doesn’t have any tables in) I get a ‘Not supported file format. Please select another file.’ error. Any idea what this might be?

    Thanks

    Like

    1. Hello Stu,

      I just did a test on my end using the “Get worksheets” action, and everything ran successfully.
      Can you verify that the file is in Excel format (xlsx)

      Like

  6. Hello Josh,
    Thanks a million! You put me on the right track with your article. I was going insane trying to refer to files using a path variable which did obviously not work.
    But even though I started working with your solution I got curious and did a “Get Metadata using Path” for the file(s) I was working with. I used the Id value being returned for the file at the locations where you used the Id you retrieved using the http call.
    And that works perfect as well and is much simpler.
    Just a tip.

    Kind regards,

    Like

    1. Hello 😊. Thank you!

      Yes that does work!! I am currently making a post with all the new updates to the Excel connector. The product team has done some fantastic work in the past months.

      Like

  7. Hi,

    Your walkthrough is super brilliant and easy to follow for a beginner like me. However, I am getting this error “Not supported file format. Please select another file.” at the Get Tables action. Any help would be appreciated as I can’t seem to find a solution to fix it.

    Thank you.

    Like

  8. Great tutorial. I am really happy that I found this site. Your articles on Power Automate are super helpful.
    I have successfully implemented the above flow in my system and it works fine!
    Although in the Compose action where you input the file name (here its ‘MyNewExcelFile.xlsx’) , I want the file name to be dynamically accessed from the folder structure instead of entering the filename in the ‘Inputs’ section of Compose action.
    All help is appreciated a lot for helping out a beginner!

    Like

    1. Thank you!!

      Where are you trying to get the file from? All that is needed is the Filename with the excel extension (xlsx).

      In my example I am getting the filename dynamically. I am just using a file in the library as an example to easily set up the Flow.

      Let me know if you need more information

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s