Flow to Create and update a List of Azure AD Users

[Jakel]

Problem:

My flow is able to create a list of Active Azure AD Users only once and I have been unsuccessful in making the flow check if the User Exists and only Add a New User and Remove Users. I am also not great at doing flows and programming - but can hobble something together.

Actual Outcome:

I can create a list of AD with no problem, but when I rerun it, it creates duplicate entries when I actually want it to check and only add new users or delete a user. I can get this condition to run, but now it only repeats the entry of the first entry indefinitly... so I just end up with hundreds of rows of a single user. How to fix it per my expected outcome?

Expression Used: (I am not good at these)

length(body('Get_items')?['value'])

Expected Outcome:

I have Azure AD and I want a Sharepoint List of my AD created and each day have that list updated with any new users or removed users. 

This sharepoint list will be used for other apps/lists and excel work, etc. so I don't want to manually export and update my lists every day anymore.

Thanks in Advance!

[ManishSolanki]

Hi @Jakel 

I have designed a power automate that will sync Azure AD group users with SP list. New users in AD grp will be added into the SharePoint list and for users removed from the grp, their entries will be deleted from the SharePoint list.

Following is the logic:

1. It is a scheduled power automate flow, you can schedule it as per your need.

2. We will declare the array variables for storing email address or principal name (unique property) and store values from the Azure AD grp & SP list respectively.

3. We will take union of both variables to get the combined list of users (both from AD grp and SP list) and store it in the third array variable.

4. Next, we will filter out the SP users to get the users who needs to be added in SP list, basically new user added in Azure grp and add it to SP list.

5. Similarly, we will filter out Azure AD users to get the users who need to be deleted from SP list (users removed from Azure AD grp)

I have used a SP list 'Active User List' with only one column 'Email', please modify the flow as per your requirements.

Here are the screenshots of the flow:

I have used parallel actions to populate arrays:

below screenshot is continued for 'apply to each 4' (right side path)

Please let me know if you face any problems.

Please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Jakel]

wow, thanks for the detailed response. Let me try this and ill report back!

[Jakel]

Hi, I have spent the morning replicating what you have provided, it almost works... with exception to the last Apply to Each 3, where I receive a Bad Request for Get User

[ManishSolanki]

Hi @Jakel 

If you share the screenshot of the flow and the error encountered, it will be of much help.

Thanks

[Jakel]

Hi Manish, sure here are the errors. 

[ManishSolanki · Accepted solution]

Hi @Jakel 

Thanks for sharing the screenshots 🙂

Pls check if you have added only email addresses in the array variables 'varADGrpUsers' and 'varSPListUsers':

I run the flow and in my case I am getting user email address as input to 'Get User' action as shown below:

Pls let me know if you face any problem remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[Jakel]

Thanks for the help! it was tremendous. After the fix, It was giving an error also because I had some Nulls, which were some users without emails in my AAD. I fixed the users causing problems and it works now.

If I wanted to add other fields from the AAD, do I need to do an array for each one? For example, first name, last name, etc.

[MasonStorm]

I'm at a total lost. I'm new to Power Automate. I don't understand the screenshots provided or the instructions given. I have a bunch of Teams channels  and was wondering if there is a way to have Azure groups members sync to Teams channels when a new person is added to a Azure group. Let's say, a new person gets hired, The new hire is added to a Azure group. Once added, I need Power Automate to automatically add that person to the standard Teams channels. Right now, this is done manually. Which template do I need to use in Power Automate to make this happen? Thank you.

[SLokesh314]

Hi @ManishSolanki ,

Here everything is good and well, but I'm getting an error like I mentioned below.

[Darryl_Elwin]

Hi. 
I have been working through your guide, many thanks for that, as someone new to power automate, it's been a big help. But I seem to have hit a block when it tries to update the list with a new user, I get a "Bad request" error.

The flow can delete users from the list, so confused as to why it can't update.

Any advice appricated 

Cheers  

[ManishSolanki]

Hi @Darryl_Elwin 

You could try replacing "Get User" action with "Get user profile (V2)" as shown below. It also accepts the UPN or id of a user as input and return the details of the it.

Thanks

[parindas21]

Hi , Your solution is great ! However, I have similar case where I also want to update a user record in Sharepoint list if the user profile is updated. Could you please assist ?

[ManishSolanki]

Hi @parindas21 

When you say profile is updated, what all properties (like name, phone no. etc.) are expected to be updated in active directory?

[parindas21]

Hi @ManishSolanki 

thanks for getting back !

So ,basically, the idea is what you created :

1. I have users in azure AD / entra AD with their basic profile attributes : Name, country, UPN, Mail, Office location

2. I have a sharepoint list ( empty at first )

--

When the flow is supposed to trigger or Run it should :

1. Create the azure AD users who belong to a specific azure Ad group in Sharepoint as an entry if they do not exist

2. However, since we deal with user profiles and it can get updated like ( mail is updated or name) the same should also reflect in Sharepoint list on subsequent run of the flow

I have taken your script and am using the same logic as you explained :

a. Get user list from Azure AD group

b. get items from Sharepoint list ( at first it would be empty on its debut run )

c. Initializing array variables for both Sharepoint and Azure AD group

d. Storing array of data in them and then doing a union

c. Filtering again for each array Sharepoint and Azure AD

d. after this I get struck as I want to ( as described above)

d.1. Create item if its not present

d.2 Update item if already present

d.3. Remove item from the SP list if the user is not a part of Azure AD Group.

[parindas21]

Also, in your Flow snippet, how do you populate item() ? what is the expression you had used , could you kindly share?

[ManishSolanki]

Hi @parindas21 

In those filter array actions; I have used the following expression as the right side operand:

item()

 Pls note that it shouldn't be entered as plain text but in the form of expression.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

[parindas21]

@ManishSolanki  could you also help in update logic based on profile updates as I described above ? Thanks !