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.