File movement from sharepoint Nested Subfolders using Microsoft Graph API to blob using ADF

Jagdev Sinder 0

I am trying to copy files from sharepoint to Azure Blob Storage using Microsoft Graph API in ADF. I am able to do it when the sharepoint folder structure is flat. I am facing issue when the sharepoint has nested subfolders. Is there a way to get "@microsoftDownloadurl" for each file which are available in the nested subfolders on sharepoint.

Appreciate your help on this!

Thank You,

Smaran Thoomu 12,105 Reputation points Microsoft Vendor

2024-06-06T10:49:23.3033333+00:00

Hi @Jagdev Sinder

Thanks for the question and using MS Q&A platform.
I found a similar thread on the Microsoft Q&A forum that might be helpful to you. It discusses how to download files from a SharePoint site folder using Microsoft Graph API.

Here's the link: Similar thread

Reference article: https://learn.microsoft.com/en-us/graph/api/driveitem-get?view=graph-rest-1.0&tabs=http

I hope this helps! Let me know if you have any further questions.
Jagdev Sinder 0 Reputation points

2024-06-07T01:26:14.76+00:00

Hi Smaran,

Thank you for sharing the information. If I follow the information it is giving me the result but, I have more than 80 folders and 5 hierarchy level. If I follow the approach and getting the list ID for main folder and then look to download each file will be cumbersome. Is there any feasible approach. Below is the attachment for a with 5 level of 2 subfolders.
Jagdev Sinder 0 Reputation points

2024-06-07T01:30:58.6466667+00:00

Hi Smaran,

Thank you for sharing the information. It is helping me to get the files which are available in 5 level deep hierarchy. I issue is I have around 80 just folder which has same level of folder hierarchy. Is there a way which will help me to get all the files from all the 80 folders in one go instead of reading one folder at a time .

Thank You,

JD
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Smaran Thoomu 12,105 Reputation points Microsoft Vendor

2024-06-10T09:36:54.76+00:00

@Jagdev Sinder It sounds like you have a complex folder structure with many subfolders and levels. In this case, using a recursive function to navigate through the folder structure and retrieve the download URL for each file may be the most feasible approach.
Jagdev Sinder 0 Reputation points

2024-06-11T21:52:07.85+00:00

Hi Smaran Thoomu,

Is there any documentation or solution to refer to cater my requirement.

Thank You,

Jagdev

1 answer

Amira Bedhiafi 18,741 Reputation points

2024-06-05T15:59:34.0333333+00:00

Implement a recursive function to navigate through the folder structure. For each item, check if it's a folder or a file. If it's a folder, make a recursive call to list its contents. If it's a file, retrieve the @microsoft.graph.downloadUrl.

Once you have the download URLs, use Azure Data Factory to move the files to Azure Blob Storage.
Please sign in to rate this answer.
Jagdev Sinder 0 Reputation points

2024-06-05T21:34:02.93+00:00

Hi Amira,

Thank you for your quick response. Could you please share some document or example on this. Need assistant how to handle such nested folder scenario and get the download link for each files.

I am using following webactivity code - https://graph.microsoft.com/v1.0/sites/siteid/drives/driveid/items/root/children

I am getting below drive information where @microsoftDownloadUrl information is missing because of folder and subfolders available in the respective drive.

"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#Collection(driveItem)",

               "value": [

                              {

                                             "@microsoft.graph.Decorator": "decorator has been deprecated. Refer to folder.decorator",

                                             "createdBy": {

                                                            "user": {

                                                                           "email": "***",

                                                                           "displayName": "***"

                                                            }

                                             },

                                             "createdDateTime": "**",

                                             "eTag": ""{******},10"",

                                             "id": "*****",

                                             "lastModifiedBy": {

                                                            "user": {

                                                                           "email": "****",

                                                                           "displayName": "****"

                                                            }

                                             },

                                             "lastModifiedDateTime": "******

                                             "name": "_Current folder",

                                             "parentReference": {

                                                            "driveType": "documentLibrary",

                                                            "driveId": "****",

                                                            "id": "***",

                                                            "name": "Documents",

                                                            "path": "/drives/***/root:",

                                                            "siteId": "**"

                                             },

                                             "webUrl": "***",

                                             "cTag": ""c:{***},0"",

                                             "fileSystemInfo": {

                                                            "createdDateTime": "**",

                                                            "lastModifiedDateTime": "***"

                                             },

                                             "folder": {

                                                            "childCount": 86

                                             },

                                             "shared": {

                                                            "scope": "users"

                                             },

                                             "size": 1111

                              },

Amira Bedhiafi 18,741 Reputation points

2024-06-11T09:22:04.25+00:00

Can you give the structure of your folders?

Jagdev Sinder 0 Reputation points

2024-06-11T10:13:34.92+00:00

Hi Amira,

The folder structure looks like.

Top Folder (1 folder)

Level 1 (15 folders)

Level 2 (Each 15 folders of Level 1 has subfolders with count for 25 subfolders)

Level 3 (Each level 2 Subfolders has few Subfolders or files)

Level 4 (Each level 3 subfolders has final files)

Hope that helps. I am trying to attach a screenshot hope it goes with the comment.

Thank You,

JD

Amira Bedhiafi 18,741 Reputation points

2024-06-12T17:44:18.6766667+00:00

To handle the nested folder structure in SharePoint and retrieve the @microsoft.graph.downloadUrl for each file, you will need to recursively navigate through the folder structure using Microsoft Graph API.

Authenticate and Get Access Token: Ensure you have the appropriate access token to interact with the Microsoft Graph API. This token should have the necessary permissions to read files in SharePoint.

List Items in a Folder: Use the Microsoft Graph API to list items in a folder. You can start at the top level and navigate through each level of the folder structure. The endpoint to list items in a folder is:
GET /sites/{site-id}/drives/{drive-id}/items/{item-id}/children
Replace {site-id}, {drive-id}, and {item-id} with the appropriate values for your SharePoint site and folder.

Check for Subfolders and Files: For each item in the folder, check if it is a folder or a file. If it’s a folder, recursively call the API to list items in that folder.

Retrieve @microsoft.graph.downloadUrl: When you encounter a file, retrieve its @microsoft.graph.downloadUrl which can be used to download the file.

Here is a sample recursive function in Python to achieve this:

import requests def get_items(site_id, drive_id, item_id, access_token): headers = { 'Authorization': 'Bearer ' + access_token } url = f'https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{drive_id}/items/{item_id}/children' response = requests.get(url, headers=headers) response.raise_for_status() return response.json() def get_all_files(site_id, drive_id, item_id, access_token, all_files): items = get_items(site_id, drive_id, item_id, access_token) for item in items['value']: if item['folder']: # If the item is a folder get_all_files(site_id, drive_id, item['id'], access_token, all_files) else: # If the item is a file all_files.append({ 'name': item['name'], 'downloadUrl': item['@microsoft.graph.downloadUrl'] }) # Usage access_token = 'your_access_token' site_id = 'your_site_id' drive_id = 'your_drive_id' root_folder_id = 'your_root_folder_id' all_files = [] get_all_files(site_id, drive_id, root_folder_id, access_token, all_files) # Now 'all_files' contains the list of files with their download URLs

Jagdev Sinder 0 Reputation points

2024-06-13T03:31:39.35+00:00

Hi Anima,

Thank you for your reply. Could you please shed some light how I could achieve this point in the above solution in Azure Data Factory -

Check for Subfolders and Files: For each item in the folder, check if it is a folder or a file. If it’s a folder, recursively call the API to list items in that folder.

This is where I am stuck and need your assistance on how to handle this in ADF.

Thank You,

Jagdev

Smaran Thoomu 12,105 Reputation points Microsoft Vendor

2024-06-14T05:14:24.47+00:00

Jagdev Sinder To check for subfolders and files in Azure Data Factory, you can use a combination of the "Web" and "ForEach" activities. Here's an example of how to do this:

Add a new "Web" activity to your pipeline.

Configure the "Web" activity to call the Microsoft Graph API to list the items in the current folder. You can use the following API endpoint to do this:

GET https://graph.microsoft.com/v1.0/sites/{site-id}/drives/{drive-id}/items/{item-id}/children

Add a new "ForEach" activity to your pipeline.

Configure the "ForEach" activity to loop through the items returned by the "Web" activity.

Inside the "ForEach" activity, add a new "If Condition" activity.

Configure the "If Condition" activity to check if the current item is a folder or a file. You can use the following expression to do this:

@equals(item().folder.childCount, null)

If the current item is a file, retrieve the @microsoft.graph.downloadUrl using the following expression:

item().@microsoft.graph.downloadUrl

If the current item is a folder, make a recursive call to list its contents using the following expression:

@activity('Web').output.value[?(@.parentReference.id == item().id)]

To handle multiple levels of subfolders, you can use a nested "ForEach" activity inside the "If Condition" activity to loop through the items in the subfolder.

Here's an example of how the pipeline might look:

Web activity (list items in current folder) | ForEach activity (loop through items) | If Condition activity (check if item is a folder or a file) | |-- If true (item is a file) | | | |-- Copy activity (copy file to Azure Blob Storage) | |-- If false (item is a folder) | | | |-- Web activity (list items in subfolder) | | | |-- ForEach activity (loop through items in subfolder) | | | |-- If Condition activity (check if item is a folder or a file) | | | |-- ... (repeat for each level of subfolders)

I hope this helps! Let me know if you have any further questions.

Jagdev Sinder 0 Reputation points

2024-07-02T03:24:42.45+00:00

Hi Samaran,

Thank you for sharing the above code. I am able to follow the instructions till number 7, but when I add the web activity for the last instruction (i.e., number 8) in the "If condition false" section, I get the following error message: "Position 54 Unrecognized expression: ?(@.parentReference.id == item().id)".

Please let me know what I am doing wrong here.

Thank you,

Jagdev

Smaran Thoomu 12,105 Reputation points Microsoft Vendor

2024-07-02T12:29:09.1366667+00:00

Hi Jagdev Sinder

I apologize for the confusion. The error message you're seeing indicates that there is a syntax error in the expression used in the "Web" activity. The correct expression to list the items in the subfolder is:

@activity('Web').output.value[?(@.parentReference.id == item().id)]

Please ensure that you have copied the expression correctly and that there are no syntax errors. You can also try testing the expression in the Azure Data Factory expression builder to ensure that it returns the expected results.

I hope this helps! Let me know if you have any further questions.

Jagdev Sinder 0 Reputation points

2024-07-03T03:04:32.4566667+00:00

Hi Smaran,

Thanks for sharing the above code but, the result is same error message. For trouble shooting when I am running "@activity('Web').output.value" at the same place as per point 8 in your original solution it is throwing below error message.

The web activity input post execution with error msg look like -

{

    "method": "GET",

    "headers": {

        "Authorization": "Bearer *********",

        "ConsistencyLevel": "eventual",

        "Accept": "application/json"

    },

    "url": [

        {

            "@microsoft.graph.Decorator": "decorator has been deprecated. Refer to folder.decorator",

            "createdBy": {

                "user": {

                    "email": "******",

                    "displayName": "*****"

                }

            },

            "createdDateTime": "2018-06-11T22:48:22Z",

            "eTag": ""{*****},10"",

            "id": "*****",

            "lastModifiedBy": {****

                "user": {

                    "email": "*****",

                    "displayName": "*****"

                }

            },

            "lastModifiedDateTime": "2018-06-11T22:50:01Z",

            "name": "_Foldername",

            "parentReference": {

                "driveType": "documentLibrary",

                "driveId": "*****",

                "name": "Documents",

                "path": "/drives/****/root:",

                "siteId": "******"

            },

            "webUrl": "*******",

            "cTag": ""c:{******},0"",

            "fileSystemInfo": {

                "createdDateTime": "2018-06-11T22:48:22Z",

                "lastModifiedDateTime": "2018-06-11T22:50:01Z"

            },

            "folder": {

                "childCount": 91

            },

            "shared": {

                "scope": "users"

            },

            "size": 11396521303

        },

Output with error msg -

{

"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (****)", "executionDuration": 0, "durationInQueue": { "integrationRuntimeQueue": 1 }, "billingReference": { "activityType": "ExternalActivity", "billableDuration": [ { "meterType": "AzureIR", "duration": 0.016666666666666666, "unit": "Hours" } ] }

}

Error Message -

The value type 'System.Collections.Generic.List`1[[System.Object, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]', in key 'url' is not expected type 'System.String'

Please help me to resolve this.

Thank You,

Jagdev
Sign in to comment

Share via

File movement from sharepoint Nested Subfolders using Microsoft Graph API to blob using ADF

1 answer