by 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 relatively 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 the 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 the 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:
- The path of the file
- 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 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 relatively 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.
Making things even easier
I’ve added another article that explains in detail how to use this template. Thanks to all in the comments for the fantastic feedback. You can find it here.
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
Great detailed explanation 👍
I’d like to know, how do I trigger such a solution from a submit of an MS Form?
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
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.
Hum, indeed. Need to try it out and, if it doesn’t work, find a solution. Will keep you posted :).
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
Hey Francesco,
I’ll need more information to try to help you. To keep the privacy of your Flow, can you email me some screenshots at manuel@manueltgomes.com, and I’ll help you there?
Then we’ll post here the solution for everyone to benefit, deal?
Cheers
Manuel
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
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
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?
One thing I need to mention is I created the flow in a solution.
I tried outside the solution and it works!
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
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
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
Hi Jordan,
Of course. Email me the information and try to give some detail so that I try to help you.
Talk soon
Manuel
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
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
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
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.
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.
Hey Jacky,
Can you please email me an example so that I can help you further? Then I’ll post the solution here so that everyone can learn from it.
Cheers
Manuel
Hi Manuel
Great post, could you provide and example of the format of the path. Everything I’ve tried gives me server error. I have a sub folder in the Documents library which is where by Excel will be stored
Hi Claire.
I’ll give you a fail-proof way to get the path. Create a temp Power Automate where you select manually the file that you want. After that, Power Automate will automatically fill in the path of that file. Copy and paste that, and you’re always sure that you have the correct path :).
Hope this helps
Cheers
Manuel
Hi Manuel – Thank you for providing this, though I’m somewhat lost and hoping you can point me in the right direction. Here’s what I’m looking to do: Someone emails an Excel File to a shared mail box. PowerAutomate saves the Excel file to a SharePoint library. Then, PowerAutomate opens up the excel file that was just saved, gets the information from it, and saves it into a SharePoint list. Is that doable and if so, how does your template fit in to that work flow? I can’t quite wrap my head around what triggers the actions in the template (even after reading through the post several times). Thanks in advance for any help you can provide!
Hi Tom,
That’s doable. Here’s a skeleton to get you started:
1. Import the template to a solution. That’s the easiest way to use it.
2. Create a new Power Automate with the trigger “When a new email arrives in a shared mailbox (V2)”
3. Use the action “Create File” to add the file to a location
4. Use the action “Call child Flow” and use my template (you’ll only have to pass the arguments)
5. Get a JSON parser and get the values from the template
6. Us that to save to SharePoint.
I know it’s high-level, but try it out and send me an email from where you got, and I’ll help you from that point forward. I’m sure we can figure this out. 🙂
I’ll probably spend some time and write another article that explains further how to use the template.
Cheers
Manuel
So I have a situation where I’m trying to use the “Add a row into a table” action. The file name will always be static, but every week a new sheet and table will be created and the name will be dynamic. I can’t seem to get this to work with a dynamic Table name. And even though it allows you to put in Dynamic Content, it won’t let you map any of the columns. Just gives you a new field called “Row” and in the description says “Row to add into the specified Excel table”.
Any help would be greatly appreciated
Hi,
This is something tricky that I haven’t got the right solution yet. Getting the values from a dynamic value in Excel is possible, but updating is something I need to work further to understand how to do it.
For now, and this solution is horrible, you need to change the Power Automate when you get a new sheet and table. I’ll try to come up with a better solution.
Cheers
Manuel
Hey Manuel, Im facing a similar issue as Caroline. Im not able to make an http call to even “_api/v2.0/drives/”. Facing the same 400 error. My trigger is a “For selected file” sharepoint trigger. Basically Im handing over the responsibility to the user whether he wants to run the flow and get the data from the excel. Please can you help me with a solution and also let me know via an example what my “path” parmeter would be in case I have to read this file from a folder in a sharepoint library?
Hi Sheldon,
I got this question a lot so I made a step-by-step article on using the template without understanding it or how to solve the most well-known issues.
https://manueltgomes.com/microsoft/powerautomate/how-to-access-dynamic-path-excel-part-ii/
If this doesn’t help you, write back, and I’ll help you further.
Cheers
Manuel
Have you gotten anywhere further with this? I’ve been pulling my hair out trying to Add a Row into a Table with Excel for business. Location (site) changes, but in every site, same document library name, same file name, same table name. Want to dynamically update the excel file in the appropriate site. Only he name of the Site changes, but like Col5and3ers above, I just get a “row” required parm. Any thoughts?
I appreciate the effort on your post. I read your guide for using power automate as a web service. Unfortunately I’m still completely not understanding how to trigger this or even where to start.
No worries. Let’s start from the beginning and figure it out. What’s your first issue?
very clear and good article easy to understand. Thank you
hi Manual, thanks for this amazing article. Is it possible to update a dynamic excel file with this flow? i cant find anything on the internet..
HI,
Sure it’s possible. What do you need to do?
hi Manuel, i just send you an email with screenshots, can you please help i am stuck i tried to find a way for a few hours 🙁