How to get file content from Shared OneDrive link

[KMLAU_MOORE]

Hi everyone,

  I want to get file content from Shared OneDrive link as 

https://XXXXXXX-my.sharepoint.com/:t:/g/personal/XXXXXXX_XXXXX_onmicrosoft_com/EcLLnlKqL3lDpvzVE5LDP...

The link come from Dataverse Note table that was generated by MS Teams Approvals template. 

Supplementary Information: 

Would you teach me how to parse/resolve above link by power automate for further manipulation ?

[Expiscornovus]

Hi @KMLAU_MOORE,

You can place the URL in a HTTP action. The response will give you a HTTP Redirect 302 Object moved reply with Headers which contain a link to the actual location and a doc id. You can reuse that doc Id in another HTTP request. I shared this approach earlier in this thread:

https://powerusers.microsoft.com/t5/Building-Flows/HTTP-request-to-Sharepoint-sharing-link-fails-wit...

Below is an example for a OneDrive file

1. The Send an HTTP request uses the URI below

_api/web/GetFileById('@{substring(outputs('HTTP')['headers']['Location'], add(indexOf(outputs('HTTP')['headers']['Location'], 'sourcedoc=%7B'), 13), 36)}')

2. Also make sure the configure run after setting on that action is configured on the the status has failed

[KMLAU_MOORE]

Dear Expiscornovus, 

  Thanks for your teaching. However, I tried to place the URL in a HTTP action with configure run after setting on that action is configured on the status has failed.

The output is forbidden (403). 

And Response Headers did not contain a link to the actual location and a doc id. Would I have anything wrong?  Or Has any other solution advise for?

[Expiscornovus]

Hi @KMLAU_MOORE,

Apologies if it wasn't clear. The has failed configure run after setting needs to be applied to the Send an HTTP Request to SharePoint action and not on the HTTP action.

But that might not be the issue. You are getting a 403 instead of a 302. Dou you have permission to view the file? Is it an anyone who has a link is allowed to view the file type of link?

[KMLAU_MOORE]

Thanks for your reply.

Firstly, the follow your instructions add failed configure run after setting needs to the Send an HTTP Request to SharePoint action also.

And I am the file owner. I have permission to view the file because the file located on my OneDrive account for preliminary testing. However, no everyone did not been allowed to view it.

[KMLAU_MOORE]

Dear Expiscornovus, 

  Let me talk the whole story to you.  I use MS Teams Approval Template to work for simple reimbursement processing. User will attach their expense quotations to Teams Approval app. Unfortunately, Approval Template place the link to file in the Dataverse Note table. I need to extract the quotations file records to outside to be retained for a period of not less than 7 years. 

 

[Expiscornovus]

Hi @KMLAU_MOORE,

To be clear, I am using the below type of link in my testing. What type of link are you using?

Thanks for sharing that information. From you screenshot it looks like you have set the run after setting to both has failed and is successful for both actions. Can you correct that?

The HTTP only needs to run on successful status (please uncheck has failed)

The Send an HTTP request action needs to be running on has failed (please uncheck is successful).

[KMLAU_MOORE]

Thanks Expiscornovus,  thanks your teaching patiently and your great help. 

Unbelievable, I got a progression.  However, still not get the answer. Would you give me more opinions? I have already asked many people to teach me resolve the shared link. 

Regards,

Permission status for file

http action result

[Expiscornovus]

Hi @KMLAU_MOORE,

That is strange, your file is directly opening and you are getting a Status Code 200 and the file is directly opening. Which explains why it is not working for you.

I was expecting a Status Code 302 with a Object Move message. That response normally has a Location field in the Outputs Headers with a specific link to the new location. 

Is there anything specific with the .xslx file you are using in your test?

Have you tried copy/pasting the sharing link manually to test if that works? Would be great to compare it to retrieving it from the Dataverse Note table, to see if there is any difference. 

[KMLAU_MOORE]

Hi Expiscornovus, 

  After checking, the link in Dataverse Note table is same as copy/pasting the sharing link manually. Happy your fully support. 

As you know, I want to solve the problem of attachment file on Teams Approval Template. 

And I discovered that attachment file name will be in the title field on the Datavserse Note table. Can use MS graph API to search by filename to solve my problem? 

(As I know, the attachment file will store in the user's OneDrive/<Approvals  Document>

If yes, would you give me the guidelines to me to copy the attachment file out?

[Expiscornovus · Accepted solution]

Hi @KMLAU_MOORE,

I have done some testing with the Basic Approval template and the Notes table.

Can you try the below approach instead?

1. Extraction of the URI of the persons OneDrive site, based on the notetext attribute value

concat(split(outputs('Get_a_row_by_ID')?['body/notetext'], '/')[0],'/',split(outputs('Get_a_row_by_ID')?['body/notetext'], '/')[1],'/',split(outputs('Get_a_row_by_ID')?['body/notetext'], '/')[2],'/',split(outputs('Get_a_row_by_ID')?['body/notetext'], '/')[5],'/',split(outputs('Get_a_row_by_ID')?['body/notetext'], '/')[6])

File Path

Documents/Approvals Document/@{outputs('Get_a_row_by_ID')?['body/subject']}

 

[KMLAU_MOORE]

Hi Expiscornovus, 

   Thank  faithfully  for your teaching step by step. Following your instructions  and "add send mail" action at last

 (for fast file transfer testing).  And got an email with attachment successfully.  Happy with your solution. Thanks very much.