I’m working on a project currently that involves a user passing a SharePoint file url to Power Automate, which then needs to retrieve the contents of the file (what I’m doing with those contents is best served in an upcoming blog post). When it comes to retrieving the contents of a SharePoint file in Power Automate, there are two standard options:
Since we’re going to be dealing with an incoming url, and won’t have a file identifier, we’ll be using “Get file content using path.” However, there’s still a wrinkle to this particular challenge, because a user can pass in a file url to any SharePoint site they have access to, and so we can’t hard-code the Site Address:
Fortunately, even though we won’t know the Site Address, we can extract it from a SharePoint file url with a few simple Power Automate functions. Assume that you have a variable called ‘FileURL’ that contains the link to the file in SharePoint; you can parse the site address by choosing “Custom value” for the Site Address and using the following expression:
slice(variables('FileURL'),0,nthIndexOf(variables('FileURL'),'/',5))
Breaking this down, nthIndexOf will give us the point in the text value where the search term appears for the nth time; in our case, we’re looking for the 5th forward slash, as all of our url’s should look like: https://contoso.sharepoint.com/sites/sitename/{filepath}
By using that nthIndexOf value inside the slice method, starting at the beginning of the value and extracting everything up until that point, we’re able to retrieve the site url.
Example FileURL:
https://mysite.sharepoint.com/sites/Communication/Shared%20Documents/logo.jpg
Output after running above expression:
https://mysite.sharepoint.com/sites/Communication
Putting this all together, by using our above function, and the FileURL variable as the input for the File Path, we can now retrieve the contents of the document in SharePoint:
As always, I hope this blog has been helpful, and that you have a fantastic day!
Cheers,
Matt
