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.

39 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

    Reply

  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

    Reply

  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

    Reply

    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

      Reply

  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

    Reply

    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

      Reply

  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

    Reply

    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

      Reply

  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

    Reply

    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

      Reply

  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

    Reply

  9. 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

    Reply

    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

      Reply

  10. 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

    Reply

  11. 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

    Reply

  12. 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

    Reply

  13. 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

    Reply

  14. 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

    Reply

  16. Perhaps you can help address this issue a bit differently. I want to be able to specify a dynamic Site URL and Document library when using Excel actions. If I put in a Dynamic URL then the list of document libraries is not created by the action. If I type in the name of Document Library vs. selecting from the list, the action will fail complaining about a bad drive reference.

    How might I provide the right Dynamic Document Library value to make the action work dynamically?

    Like

    Reply

  17. Hi, I get the following error.

    Unable to process template language expressions in action ‘List_rows_present_in_a_table’ inputs at line ‘1’ and column ‘34143’: ‘The template language expression ‘first(outputs(‘Get_tables’)?[‘body/value’]?[‘id’])’ cannot be evaluated because property ‘id’ cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.’.

    Like

    Reply

  18. Hi Josh,
    I have folder which which contains 30 excel files which gets populated once per week from email using Power Automate. The file name(s) contain a time stamp i.e. DirectOrdersRegional_2022-09-23T0931.xlsx . I’m trying to remove the string following the first underscore character “_2022-09-23T0931” from the name and only have “DirectOrdersRegional”.

    Thanks
    Don

    Like

    Reply

    1. Use a Parse JSON action to get the values to show up again. Its also possible to write expressions to get the values if your inside a loop, you would use something like:

      item()?[‘ColumnName’]

      Like

      Reply

  19. Hi,

    I setup this flow so far so good, but now i am stuck.

    I am applying a condition on two excel files, however i am not getting the dynamic column names from the dynamic file.

    If value from static file is equal to value from dynamic file then update row in dynamic file.

    The issue is i cant select any dynamic column/value from dropdown for the dynamic excel file

    Like

    Reply

  20. Is it possible to dynamically get tables from multiple worksheets in one Excel workbook? I’m trying to create a multi-tab workbook, one table on each. I want to be able to read the 3 tables and then use the data in my flow accordingly.

    So instead of using the expression first(outputs(‘Get_tables_from_Import_template’)?[‘body/value’])?[‘id’] I would need to get the second and third tables as well.

    Like

    Reply

Leave a Reply to Josh Cook Cancel reply

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s

%d bloggers like this: