Getting URI for Send HTTP request

[Anonymous]

Hi all, I am trying to follow these two blogs to access Excel with a dynamic path @manuelstgomes 

http://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow

https://manueltgomes.com/microsoft/powerautomate/how-to-access-dynamic-path-excel/

My trigger is a new folder [2020-08-12 (12nn) to 2020-08-13 (12nn)] created in a document library named 'Failed Pens'. This folder contains the excel (same name as folder) that I want to use. I am stuck at the very first 'Sending an HTTP request'. I am unable to obtain the URI. 

This uri below is able to run successfully but it retrieves the contents of the excel and not the excel ID:

_api/web/GetFolderByServerRelativeUrl('Failed Pens/2020-08-12 (12nn) to 2020-08-13 (12nn)/')/Files('2020-08-12 (12nn) to 2020-08-13 (12nn).xlsx')/$value

Please help, I have no idea how to get the ID and I've been trying for hours 😞

[manuelstgomes]

HI @Anonymous 

The template is prepared that you can import it, and don't worry about the insides. Just call it, and you'll get the information.

You just need to be careful with the path to the file, and that's it.

Can you please show me your Power Automate and where are you stuck.

I'm sure we can figure this out.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[Anonymous]

Hi @manuelstgomes Thanks for replying.

I am having trouble with the uri of the 'Send an HTTP request'. The uri (

_api/web/GetFolderByServerRelativeUrl('Failed Pens@{outputs('Get_Folder_name')}')/Files('@{items('Apply_to_each')?['Name']}')/$value) works but I am not getting any Excel IDs. I get $content-type and $content instead.

I think my uri is wrong but I do not know how to fix it. I have tried these uri:

1. _api/v2.0/drive

2. _api/v2.0/drive/root

3. _api/v2.0/drive/root:/Failed Pens/2020-08-12 (12nn) to 2020-08-13 (12nn)/2020-08-12 (12nn) to 2020-08-13 (12nn).xlsx

but all keep getting this error: Unexpected response from the service

I have also imported your template but unable to use it due to this uri issue.

[manuelstgomes]

Hi @Anonymous 

Here's how I use it. I use the URI:

_api/v2.0/drive/root:@{outputs('Compose')}

Test Power Automate:

URI part of the template:

I have everything in a solution to make things easier.

With that URI, it works. I'm finding it strange you cannot import the Power Automate's template. What error you're getting? Probably I made some sort of mistake.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[Anonymous · Accepted solution]

Hi, I have managed to solve the issue. It was indeed the uri problem. I'll list some links here for those who are not familiar with uri.

Use Microsoft Graph to test out your uri: 

https://developer.microsoft.com/en-us/graph/graph-explorer

Uri format guide: 

https://docs.microsoft.com/en-us/graph/api/resources/sharepoint?view=graph-rest-1.0

My problem was that the file I wanted wasn't in the default drive hence my uri looked something like that (drives instead of the usual drive):

_api/v2.0/drives/{drive id}/root:/{file path}

My flow was also a little different from Manuel's one since my trigger was when a new folder is created. I had additional steps to list the folder and filter the array to get the excel sheet. After the HTTP request is sent, I got a JSON result which I also had to parse to get the excel ID. The general idea of the flow remains the same as the ones listed in Manuel's and John's blog. Not sure if this is the best way to do this but it works dynamically.

[sauvetre]

Hi,

I am stuck with the same issue you had... but I did not get how you resolve the issue.

As you, I am not using the default library.   I have created another library but I have no clue how to get the drive id that could work with api/v2.0/drives/{drive id}/root:/{file path}.

Could you please let me know how you did get your {drive id}?

Thank you.

[ArnaudB]

Hello, I've got the same and it drives me crazy.

I've followed many howtos to get Excel ID via HTTP request but i've failed.

Strangely, put the request in a navigator works and i get the JSON data...

Help is very very very welcome. May @manuelstgomes  could have an idea ;-).

Arnaud.

[Anonymous]

Hi Arnaud,

Did you finally get a solution on this?

Best regards,

[moutinhoabreu]

Hi All,

I've followed @manuelstgomes tutorial and inclusively imported the zip file onto my power automate.

However instead of triggering the flow through a Http request i intend to trigger it on "when a new file is created", sharepoint connector.

I've built all the steps with @manuelstgomes tutorial, dropping the "http request" and doing an "Http call". I just can't point a valid URI and i'm always getting the error, below:

The provided 'Http' action URI '*sanitized*' is not valid. The URI must be a well formed absolute URI not referencing local host or UNC path. 

An overview of My Flow steps:

The urI i'm using (File Path is coming from List folder Step):

_api/v2.0/drive/root:FILEPATH

What am i doing wrong?

Thanks in advance!