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.

New Update – Easy Way 2021

The following video explains a way easier way to do this, no API, no messy File ID’s. Check it out!

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.

32 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

  9. Hi Josh,

    I keep getting this error on Get tables:

    {
    “status”: 400,
    “message”: “The parameter ‘drive’ has an invalid value ‘first(outputs(‘Tabellen_ophalen’)?[‘body/value’])[‘id’]’.”
    }

    I think the issue is linked to the fact that my system is in another language (Dutch) and all commands are also translated (“Tabellen_ophalen” means “Get_tables”). I think I understood correctly to translate your “Get_tables” to “Tabellen ophalen”; but I think I’m just not finding the correct translation for “Body/value”.

    Thanks in advance for any possible help!

    Like

  10. Hi Josh,

    Your guide has helped me a lot, but I’m getting lost in a final step.

    I work for a construction company which has many simultaneous construction sites, and I was tasked with finding a way to automatically alert the project leaders of any dangerous weather coming their way on one of their construction sites.

    Using Power Automate I can poll MSN weather and get a one line summary of the weather prediction for today and for tomorrow, which is enough.

    The current construction sites and their locations are in this weekly automatically regenerated excel file, which has a table inside which has three important columns: ProjNr, ProjName,Location.

    So using the “Get Items” command I did manage to get a beautiful result. I was able to get the weather forecast generated and using “Apply to each” with the MSN weather poll + an Append to string variable, I created an email which had Project number; Project name; Forecast, for each project.

    The problem arose after the first “re-generation” of the excel file. Because Power Automate uses a unique file ID to connect to a file; so with every new generation, Power Automate can’t find the file anymore (even though it’s there, with the exact same name etc, the file has a different ID)

    That’s where your guide comes in. Thanks to you I got my flow to automatically find the current file ID, Get tables, List rows present in a table.

    But now when I come back to my original purpose; which was to get a weather report for every location in the list, I can’t find a location variable anymore on which I can poll the weather report!

    If I try to use Apply to each and then use “Value” from “List rows present in a table”; it polls MSN weather with a location variable such as {“@odata.etag”:””,”ItemInternalId”:”4fcb84ff-b4d7-4f2a-a240-13e76750da51″,”Projnr”:”21-501″,”ProjName”:”Le Moulin Gris”,”Location”:”75000 Paris”}, 

    Which obviously MSN weather can’t handle very well. I would need a way to set up an “Apply to each” command on which it only polls MSN Weather with the query “75000 Paris”; and after that “Append to string variable” the returned weather summary along with the project number and name. 

    So I’d do Append to string variable with something like:
    [‘Projnr’] [‘ProjName’] : [‘MSN weather summary’]

    so it adds the weather summary on a new line each time. I would end up emailing the string variable to the group of project leaders so they automatically have the weather report for their running projects and they can plan according to the predicted weather at their construction sites.

    Thanks in advance for any help!!

    Like

  11. Thank you for this – I have been pulling my hair out with the Excel connector in Power Automate to get the dynamic file populated correctly. Your step-by-step guide worked perfectly for me – thank you!

    Like

  12. Thank you so much. All blogs show how to do it when the file is located in Shared Documents but not individual libaries, which is required in my scenario and now works 🙂

    Like

  13. Hi I get this error ‘The template language expression ‘first(outputs(‘Get_tables’)?[‘body/value’]?[‘id’])’ cannot be evaluated because property ‘id’ cannot be selected.

    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