Posted by We are using Power Automate flow to export folders and files properties from a library in SharePoint to an external system, where we want te recreate the folder structure for presentation and navigation purposes.
In the file properties the folder is represented by the {Path} property but we want to use the folder ID instead to make it easier to create the relationship between file and folder. How can we add the folder ID in a way that we don’t need to use the ‘Apply to each’ action?
I’ve been reading some blog posts by John Liu and didn’t find a ready to copy and paste solution but the creative logic John is applying worked as an inspiration to me and it triggered an idea. I will need just 2 Select actions to make it happen, and will be based on a match of the {Path} and {FullPath} value.
The export of folders and files properties is done in 2 parts: part 1 is the export of the folders properties and part 2 is the export of the files properties. So it starts with an ‘Get files (properties only)’ action, then a ‘Filter array’ action to include only folders.
The first Select action is simple: we’re going to add the {FullPath} property of the folder and concatenate it with a forward slash and the ID property of the folder, in the expression:
concat(item()?['{FullPath}'],'/',item()?['ID'])This will give us the following output when we run the flow:
The second Select action is to select the properties that we’re going to export, including the folder ID (in case of a file) or parent folder ID (in case of a folder).
Let’s take the expression to get the parent folder ID and break it down into pieces.
First the Select FolderID array is converted to a string with comma separated values by a join function:
join(body('Select_FolderID'),',')Then an IndexOf function to find the (first) match of the {Path} value of the folder in the csv string:
indexOf(join(body('Select_FolderID'),','),item()?['{Path}'])In case of a match it will return the starting position that we can use in a substring function to isolate the ID value.
The first level of folders in the library don’t have a parent folder; they’re nested in the root of the library and have the same “path” as the library itself (ACBestek/). So the IndexOf function will always match with (the first part of) the first value and will return the value 0. Therefore we introduce the if function to exempt these folders from the rest of the expression and maintain the value 0.
if(equals(indexOf(join(body('Select_FolderID'),','),item()?['{Path}']),0),0,substring(...))(2022-2-22) Correction in the logic of the expression: we need to compare the path of the library (output from a ‘Filter array’ action: first(body(‘Filter_array_Library’))?[‘Path’]) with the path of the item.
if(equals(first(body('Filter_array_Library'))?['Path'],item()?['{Path}']),0,substring(...))The returned starting position plus the length of the {Path} value will give us the starting position of the ID value, that can be a whole number of one or more digits. So the length of the substring must be dynamic.
substring(join(body('Select_FolderID'),','),add(indexOf(join(body('Select_FolderID'),','),item()?['{Path}']),length(item()?['{Path}'])), <dynamic length>)An(other) indexOf function can return the position of the comma that separates the value from the next, and will give us the number of digits of the ID value.
For your information: the (optional) length in the substring function (see Note) is missing on purpose.
indexOf(substring(join(body('Select_FolderID'),','),add(indexOf(join(body('Select_FolderID'),','),item()?['{Path}']),length(item()?['{Path}']))),',')The complete expression to get the parent folder ID is:
if(equals(first(body('Filter_array_Library'))?['Path'],item()?['{Path}']),0,substring(join(body('Select_FolderID'),','),add(indexOf(join(body('Select_FolderID'),','),item()?['{Path}']),length(item()?['{Path}'])),indexOf(substring(join(body('Select_FolderID'),','),add(indexOf(join(body('Select_FolderID'),','),item()?['{Path}']),length(item()?['{Path}']))),',')))Now the Select Folder properties action will give us the parent folder ID (as string, that can be converted to an integer) as part of the properties to be exported to the external system and be used to create the relationship between the folders.
The same expression can be used to get the folder ID for a file and add it to the file properties to be exported.
One comment