Find ID of SharePoint Document by File Name in Power Automate
Did you know that there are two types of identifiers used in
the SharePoint connector to identify an item in a SharePoint list or a
SharePoint document? The first one is the "Unique Identifier" (integer type)
which is sequentially generated by the system and unique to the list or library (e.g. 13). The other one is the "File Identifier" (string type)
which is the "folder + file name" in the SharePoint site. (e.g.
"contact/Jim%20Glynn%20(sample)_84060750AB16EB11A812000D3A6AA8DC/")
It is a bit confusing that some of the actions in the SharePoint connector
accept the File Identifier as a parameter while some of them accept
the Unique Identifier as a parameter. This is the list of actions in
SharePoint connector
which accept the integer type Unique Identifier.
- Delete item
- Get file properties
- Get item
- Grant access to an item or a folder
- Stop sharing an item or a file
- Update file properties
- Update file properties using AI Builder model results
- Update item
Getting the Unique Identifier would be easy if the output of the trigger
or the action in the steps above contains the value of the Unique Identifier.
But in my scenario, I only have the file name and the folder path (File
Identifier) but I need the Unique Identifier to update the file properties. (this also applies if you want to update or delete the item, grant access or stop sharing)
Get File ID by "Get files (properties only)" action
After looking around in the web and I found the solution in the blog post
by
Pieter Veenstra. Basically, the solution is to use the Get files (properties only) action to get the list of files in the specific folder and filter the one with a matching filename to get the Unique Identifier.
This is the overview of the whole child flow.
1. Trigger Input Parameters
This is the child flow and it accepts the SharePoint document library, folder
and the filename as parameters.2. SharePoint Site URL
3. Get files (properties only)
The SharePoint Site, document Library Name and the Folder from the trigger input
parameter are populated in this step. There is a "Filter Query" parameter in
this action but it is a shame that
it only works for the custom columns
and we cannot filter based on the output properties with curly brackets
{FilenameWithExtension}.4. Filter array
This is the step to get the particular JSON object from the array of files in
the folder (specified in step 3). This is the better and efficient alternative of using "Apply to each" control to loop through the whole array. Since we cannot select from the Dynamics Values list in the Filter array step, use the following expression to specify the {FilenameWithExtension} property from the output.item()?['{FilenameWithExtension}']
5. Compose Output
Finally, the Unique Identifier integer of the specified file name can be retrieved using the flow expression below. Since the Filter array action returns an array, get the first() record from the output.first(body('Filter_array_to_Get_File_by_File_Name'))?['ID']
Get File ID by "Send an HTTP request to SharePoint" action
The method above is not effective and it can take longer time if there are a lot of files in the same folder. I asked around in the community for more efficient solution and Rob Dawson suggested to try it with GetFileByServerRelativeUrl method using "Send an HTTP request to SharePoint" action.
The "Method" is GET and the sample complete Uri is as follows:
_api/web/GetFileByServerRelativeUrl('/sites/documents/contact/Jim Glynn (sample)_84060750AB16EB11A812000D3A6AA8DC/Sample Picture.png')/listItemAllFields?$select=Id
You might notice that the server relative URL starts with the custom site collection URL "/sites/documents" from the SharePoint Site variable. In order to split the custom site collection URL from SharePoint Site variable, uriPath() function is used as follows:
uriPath(variables('SharePoint Site'))
If you want to learn other URI functions, you can check out this blog post by Pieter Veenstra.
The custom site collection URL is followed by the Library, Folder and File Name. This would be the complete sample URL.
/sites/documents/contact/Jim Glynn (sample)_84060750AB16EB11A812000D3A6AA8DC/Sample Picture.png
$select=Id is used to filter the Id column only from the listItemAllFields property.
The Headers include the following value. If we do not specify nometadata in the header, the output will be a bit messy with the metadata values.
Accept: application/json;odata=nometadata
When we run the action above, we will get the Unique Identifier integer of the file using the expression below. (the expression may need to be updated depending on the output JSON)
body('Send_an_HTTP_request_to_SharePoint')?['d/ID']
Summary
There are two types of identifiers to identify SharePoint document or list items which are "Unique Identifier" (integer) and "File Identifier" (string). The "Unique Identifier" is used in some SharePoint connectors and it can be retrieved with file name by using "Get files (properties only)" and "Send an HTTP request to SharePoint" actions.
Finding the ID of SharePoint Document by File Name is one of the prerequisites for my upcoming blog post in
the File field as input and SharePoint as storage, so stay
tuned for the next post.
Thank you for the article! Even though the issue I was having was slightly different, this pointed me to the right directoin!
ReplyDeleteThanks for your comment, FabrÃcio. I am glad it helps.
DeleteHello
ReplyDeleteSome of the connectors are not the same anymore will be really helpful if we could replicate your steps, the icons have changed and the connector names have changed
Updated the screenshots and added the name of the connector (Microsoft Dataverse).
DeleteHow do we get properties of files that are stored in dataverse?
ReplyDeleteWhat kind of files that are you referring to? Notes attachment? Files uploaded through file data type column?
DeleteAnd what kind of properties are you looking for?
you can filter wit Get properties for the filename, you have to add to the ODATA filter query : FileLeafReq eq 'yourfilename.ext'
ReplyDeleteI looked so many different places to figure out how to list files and then update the file properties of each. They all pointed me in the wrong direction with the List Folder action which uses the string type ID and of course fails when using the Update File Properties action. Lot's of frustration. Thanks for making it simple!!!
ReplyDeleteI'm glad to know that you finally found the solution for your problem in my blog post.
DeleteHi Linn,
ReplyDeleteYour article helped me to fix the app I support. And saved the day for many people in hospitals using that bloody app.
Thanks a lot!❤
Olena
Awww ... That's so great to know about it. ☺️ Thanks for sharing, Olena
DeleteHi Linn,
ReplyDeleteI am Converting the Email body as PDF and adding that content into the attachment body but when we open the file from email it says the format is not correct. Any help would be appreciated
Hi Anu
DeletePlease check the data type of the content that is returned from the step from converting the Email body as PDF. If it is the binary data type and if you are using the Dataverse connector to create an email attachment, you will need to convert the output of the file to base64 before using it as the Body of the attachment.
If it is an Office 365 Outlook, I believe you also need to convert the output of the file to base64.
Hi,
ReplyDeleteI am new to power automate and I am working on a project related to power automate
I want to get the file content from sharepoint and upload it in a word template but I found difficulty in finding the file identifier
can you help me in this and thanks
Thank you for this post. I am again and again astonished of the user UN-friendliness of power automate.
ReplyDelete