Quick showcase today on something that took me too long to figure out, so naturally I want to share my solution with the community so others don’t struggle like I did.
The Problem: We have a SharePoint list on a SharePoint site that has one of the columns as person or group. This is great from an end user perspective as the user(s) will then show up with their ‘card’ which includes name, email, etc, it’s not so great for API calls to get the list details as the users will show up as numbers instead of names, emails, etc.
Here is an example where the Approver and Preparer are users in SharePoint but the API returns just these ID numbers.
This showcase will show how we can query the SharePoint site to get a list of users which we can then pair with the numbers returned in the API call SharePoint ‘Get a List.’
- Use the SharePoint ‘Make an API Call’ - see the screenshot below. Replace ‘mycompany’ with your domain, and the aaaaa and bbbb with your site. If you are unsure of you domain or site id data, you can run the SharePoint ‘Get a List’ and after the module runs you can find this information on the ‘input bundle’ after ‘Site ID.’
- Same as step 1 (replace the URL with your own) but now we have the id for the user information list which is typically hidden from the API, and we can pass this to our module here to get the mapping of the user numbers returned on the list to the actual details like name and email address. Note we also add a query string to this 2nd module, expand and id, this is what makes the data returned from this API call include all that information we are looking for.
That’s the end of the showcase, you can now do what you want with this data, for us we iterate through the array ‘value’ that gets returned in that 2nd module and store the data in a data store on Make. We then have a separate scenario where we call the ‘get a list’ SharePoint endpoint and we do lookups against this data so we have user names, full names, and email addresses, we can then use that data to build notifications and other Make automations.
Please let me know if you have a different way of solving this issue, and I hope others will find this guide helpful in the future.