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

If you have the URL of SharePoint site, you may pass it as one of the input parameters to the child flow but for my case, the value is retrieved from the SharePoint integration of the Microsoft Dataverse. Use List Rows action from Microsoft Dataverse connector to query the default absoluteurl of the SharePoint site.



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 Site Address property is populated with the same SharePoint Site variable. (e.g. https://contoso.sharepoint.com/sites/documents/ )
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.

Comments

  1. Thank you for the article! Even though the issue I was having was slightly different, this pointed me to the right directoin!

    ReplyDelete
    Replies
    1. Thanks for your comment, Fabrício. I am glad it helps.

      Delete
  2. Hello
    Some 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

    ReplyDelete
    Replies
    1. Updated the screenshots and added the name of the connector (Microsoft Dataverse).

      Delete
  3. How do we get properties of files that are stored in dataverse?

    ReplyDelete
    Replies
    1. What kind of files that are you referring to? Notes attachment? Files uploaded through file data type column?
      And what kind of properties are you looking for?

      Delete
  4. you can filter wit Get properties for the filename, you have to add to the ODATA filter query : FileLeafReq eq 'yourfilename.ext'

    ReplyDelete
  5. I 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!!!

    ReplyDelete
    Replies
    1. I'm glad to know that you finally found the solution for your problem in my blog post.

      Delete
  6. Hi Linn,
    Your 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

    ReplyDelete
    Replies
    1. Awww ... That's so great to know about it. ☺️ Thanks for sharing, Olena

      Delete
  7. Hi Linn,

    I 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

    ReplyDelete
    Replies
    1. Hi Anu

      Please 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.

      Delete
  8. Hi,
    I 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

    ReplyDelete
  9. Thank you for this post. I am again and again astonished of the user UN-friendliness of power automate.

    ReplyDelete

Post a Comment

Popular Posts