Retrieving 'Person or Group' Fields from SharePoint List in Azure Data Factory or Synapse

Question

Retrieving 'Person or Group' Fields from SharePoint List in Azure Data Factory or Synapse

Anonymous

I'm able to retrieve data from a SharePoint list using Azure Data Factory > Copy Data (via the SharePoint Connector), but the list contains 'Person or Group' type fields that return only 'Id' values in the results.

These 'Id' values are not useful, as I cannot expand the connected/hierarchical list to obtain additional details like names and emails, even when using a query in the source tab. How can this issue be resolved?

Is there another method in Azure Data Factory, Synapse, or even Databricks to retrieve SharePoint list data?

The query I'm testing is:

$select=ProjectNumber,ProgramManager/EMail&$expand=ProgramManager&$top=1

The preview data shows:

User's image

Thanks,

Sunny

Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-11-13T14:22:54.6466667+00:00
Hi @Sunny Punalkar

Welcome to Microsoft Q&A platform and thanks for posting your query here.

To retrieve the 'Person or Group' fields from a SharePoint list in Azure Data Factory or Synapse, you can use the SharePoint REST API to expand the fields and retrieve additional details like names and emails.

You can use the Web activity in Azure Data Factory or Synapse to call the SharePoint REST API and capture the response in JSON format. In the URL of the Web activity, you can use the $select and $expand query parameters to specify the fields to retrieve and expand the 'Person or Group' fields.

For example, to retrieve the ProjectNumber and ProgramManager fields and expand the ProgramManager field to retrieve the email address, you can use the following URL in the Web activity:

https://<site-url>/_api/web/lists/getbytitle(

This will return the ProjectNumber and ProgramManager fields along with the email address of the ProgramManager.

Once you have retrieved the data in JSON format, you can use the Copy Data activity to load the data into your destination.

I hope this helps. Please let me know if you have any further questions.
RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff

2024-11-15T08:18:10.0766667+00:00

Hi @Anonymous

Would you tell me whether your issue has been resolved or have any update? If the answer helps, could you kindly accept the answer via the "Accept Answer" button?
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-11-18T14:00:05.3166667+00:00

@Sunny Punalkar Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff

2024-11-25T09:45:18.44+00:00

Hi @Sunny Punalkar

Would you please provide us with an update on the status of your issue?

I am looking forward to your reply.

Have a nice day!
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2025-01-24T10:06:51.8133333+00:00

@Sunny Punalkar We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

Our engineer have provided a detailed answer which has clear steps for which you are looking for. If you wish, you may re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!
Anonymous

2025-01-29T09:15:45.46+00:00

Hi all,
Tried multiple times with all the suggestions provided but nothing has worked so far, here are a few details -
Tried this copy activity (in fabric) with SharePoint connection (but also have tried the same in ADF) which allows OData query so used that one as well, thing to note is, the query parameter 'top' works but 'expand' doesn't and I have double checked, connections and everything is working as expected, here's the response that I get -

P.S. There's nothing beyond 'Title' in the screenshot

1 answer

Your answer

Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-11-13T14:22:54.6466667+00:00

Hi @Sunny Punalkar

Welcome to Microsoft Q&A platform and thanks for posting your query here.

To retrieve the 'Person or Group' fields from a SharePoint list in Azure Data Factory or Synapse, you can use the SharePoint REST API to expand the fields and retrieve additional details like names and emails.

You can use the Web activity in Azure Data Factory or Synapse to call the SharePoint REST API and capture the response in JSON format. In the URL of the Web activity, you can use the $select and $expand query parameters to specify the fields to retrieve and expand the 'Person or Group' fields.

For example, to retrieve the ProjectNumber and ProgramManager fields and expand the ProgramManager field to retrieve the email address, you can use the following URL in the Web activity:

https://<site-url>/_api/web/lists/getbytitle(

This will return the ProjectNumber and ProgramManager fields along with the email address of the ProgramManager.

Once you have retrieved the data in JSON format, you can use the Copy Data activity to load the data into your destination.

I hope this helps. Please let me know if you have any further questions.
RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff

2024-11-15T08:18:10.0766667+00:00

Hi @Anonymous

Would you tell me whether your issue has been resolved or have any update? If the answer helps, could you kindly accept the answer via the "Accept Answer" button?
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2024-11-18T14:00:05.3166667+00:00

@Sunny Punalkar Following up to see if the below answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff

2024-11-25T09:45:18.44+00:00

Hi @Sunny Punalkar

Would you please provide us with an update on the status of your issue?

I am looking forward to your reply.

Have a nice day!
Smaran Thoomu 24,260 Reputation points Microsoft External Staff Moderator

2025-01-24T10:06:51.8133333+00:00

@Sunny Punalkar We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

Our engineer have provided a detailed answer which has clear steps for which you are looking for. If you wish, you may re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!
Anonymous

2025-01-29T09:15:45.46+00:00

Hi all,
Tried multiple times with all the suggestions provided but nothing has worked so far, here are a few details -
Tried this copy activity (in fabric) with SharePoint connection (but also have tried the same in ADF) which allows OData query so used that one as well, thing to note is, the query parameter 'top' works but 'expand' doesn't and I have double checked, connections and everything is working as expected, here's the response that I get -

P.S. There's nothing beyond 'Title' in the screenshot

Answer 1

Hello @Anonymous To resolve this issue, you can use the SharePoint REST API to retrieve the Person or Group fields from the SharePoint list. You can use the expand query parameter to expand the Person or Group fields and retrieve additional details like names and emails. Here is an example of a REST API call that retrieves the 'ProgramManager' field from a SharePoint list: https:///_api/web/lists/getbytitle('')/items?$select=ProjectNumber,ProgramManager/EMail&$expand=ProgramManager&$top=1

You can use the 'Web' activity in Azure Data Factory to call the SharePoint REST API and retrieve the data. Alternatively, you can use Azure Functions or Databricks to retrieve the data from the SharePoint list and process it as per your requirements.

I hope this helps

Share via

Retrieving 'Person or Group' Fields from SharePoint List in Azure Data Factory or Synapse

1 answer

Your answer