In PowerAutomate I created a scope with an http request to the sharepoint list containing the Person/People Picker column.
The http request to the list we’re working with
Method: GET
URI: _api/web/lists/getbytitle(‘@{variables(‘listName’)}’)/items?filter=Title eq ‘office’&$select=Title,columnName/EMail&$expand=columnName
Headers:
| Accept | application/json;odata=nometadata |
| Accept-Encoding | deflate, gzip |
Next we add a filter called selectItems
From: value outputs from the http request
Map:
| Title: | Title from created item |
| columnName (variable) | aitem()[variables(‘columnName’)] |
The selectItems filter adds items to a temporary array. From this temporary array, we will use another filter called filterArray.
From: body(‘selectedItems’)
Conditional:
If Title (name of the column in the list is Office) is equal to Office (from current list value called Office), then get that specific column info.
Advanced Conditional: @equals(item()[‘Title’], triggerOutputs()?[‘body/Office’])
Next we have an Apply to Each. This loop accounts for multiple users/people in this column. If the goal is to only retrieve one user from the list, this feature wouldn’t be necessary, however, it’s better to have it and not worry about a, ‘what if’ scenario.
| Body | body(‘filterArray’)?[0][variables(‘columnName’)] |
Inside the Apply to Each we are setting a variable called emailItem and appending to an array called emailArray.
| Name | emailItem |
| Value | items(‘applyToEach:emailArray_to_emailItem’)?[‘EMail’] |
The value comes from the emailItem which is the actual Email of the user. You can retrieve several items, Name, ID, and EMail.
**as a tip, it has to be EMail rather than Email, otherwise it will not pull the column info back**
The emailItem variable is then added to an empty array called emailArray
This loops through each item in the array until it’s complete. This can take a few seconds or a minute, depending on how many items are in the cell (sharepoint list).
Outside of the applytoEach loop, I created a join called join:emailArrayCC. The users from this array will be CC’d on email notifications based on a user’s office location.
| Inputs | join(variables(’emailArray’),’;’) |
Once done, this can be used in the To line or the CC section of the send email. The inputs above performs a join and separates each user’s email with a semicolon.
Run the flow and see the users added to the array and then emailed.
—————————————————————————————————————————————
This is for a small list and a simpler approach
This is for a small list of users.
Make the call to the list
This will return a row item based on the user’s (user who submitted form) office location
first(body(‘getTaxRouting’)?[‘Value’])?[‘TaxExam’]
The code above returns the column name called TaxExam
The screenshot displays the “From” which is the TaxExam column and the “Map” is what you need to display, which in this case is the Display Name. This gets an array of the users rather that the individual.
first(body(‘getTaxRouting’)?[‘Value’])?[‘TaxExam’]
This is the same concept except we’re able to return the Email of the users.
Now we can join/concatenate the email addresses with a semicolon. The same can be done with a comma for the display names.
An overview of the process.