I teased this article in a previous article where I showed how to automatically get a list of people in a department under a department head. We can use this list as our basis for what comes next. Next we will compare the new list to the previous list to see which people joined and which people left. This can be emailed to people who want this. And we can also apply the new list to SharePoint site groups to automatically update a group that has access to certain resources on various SharePoints.

Why would you want to automate the maintenance of SharePoint groups? Let's say you have a group of people all under a department head. The department is the legal department and members of the department we'll call lawyers. Now there are various SharePoints around the company and these SharePoints could have certain folders with legal documents and we want to lock down these folders, but always give access to the lawyers. Easy enough. Each SharePoint can create a site group and manually add each lawyer to this group. Job done, right? Wrong. Next week a lawyer left and a new one joined. Each SharePoint owner would have to somehow learn about the change and then manually remove and add people, right? Wrong again. We can automate this.

Okay. Some basics about SharePoint site groups. When you give access to a SharePoint, a folder, a list, a file, you can give access to individuals. But you can also give access to groups. SharePoint has some default groups created for each SharePoint. The name of your SharePoint plus the words "owners", "members", and "visitors". But you can also create custom groups.

A group is only available to a SharePoint and its child SharePoints. You can create a SharePoint under a SharePoint and it will share groups in the parent-child chain. SharePoints in different chains cannot share groups. There are some global groups created by SharePoint like "everyone" and "everyone except external users". There are also groups called security groups that only your IT can manage. In my experience, IT wants to control these groups and not allow somebody to automate them. And they don't want to get tickets every time the group needs to change. So we need to create our own groups. And these groups need to be created for each SharePoint chain.

So tell all SharePoint owners needing a list of lawyers to create a SharePoint group. It doesn't matter what they call it. Call it Lawyers or AllLegalDepartment, whatever. It's also important that they make you the owner of the list so that you alone can edit it.

Next you need a page where people can input the URL of their SharePoint and the group that they want updated. You can also capture their email address to record them as the owner and give them the option to be notified when their group is changed and to see what the changes were.

We should also give a button for people to subscribe and unsubscribe themselves from emails telling them who joined and who left.

That's it for the end user requirements!

Now we need to extend the automation that generates the list of department members.

After generating the new list of people and before saving it we need to retrieve the contents of the old text file and convert the list of email addresses to an array. We need to create two empty array variables: Peoplejoined and Peopleleft. Then we loop through the old list and each time we check if the person analyzed is on new list. If they are not on the new list, then they left. We add their email to the Peopleleft array. Then we loop through the new list and each time we check if the person analyzed is on the old list. If they are not on the old list, then they joined. We add their email to the People joined array.

If there is at least one item on the Peoplejoined array or at least one item on the Peopleleft array, then we retrieve the email addresses of everyone who subscribed. For each person we send them an email. We list out the people who joined and the people who left. Then we leave a nice message that they can unsubscribe at the same page where they subscribed. That's all for that part.

The more intense part is updating the various groups on various SharePoints. It's possible that someone added a new empty group to the list so we can't just add the new accounts. We have to analyze the entire list and add anyone who is missing.

We start by looping through the list of SharePoints and groups that we submitted on our page. We have their SharePoint and the group ID. So we get the list of group members as an array. We also reset our Peoplejoined and Peopleleft arrays back to empty.

We loop through the new list and for each person we check if they are on the group members array. If they are not we add them to the Peoplejoined array and we make a call to their SharePoint to add this person to the group.

We loop through the group members array and for each person we check if they are on the new list. If they are not we add them to the Peopleleft array and we make a call to their SharePoint to remove this person from the group.

If there are no people in Peoplejoined and Peopleleft then our work is done. If there are people in either of these lists, we look at the list of owners to see if any have indicated that they wish to be notified of changes to their group. If there are any then we loop through the list of owners looking for the email flag to be set to true. If it is we email them the list of people added to their group and the list of people removed. And we leave a nice message that they can unsubscribe at the page where they recorded their SharePoint group.

Next, I'll show some screenshots of how this looks.

Here's what it look like from the end user's perspective.

And here's the input form when the user clicks on +New. The user puts in the URL of their SharePoint and the drop-down menu fills in with available groups. The user can select which one they want and we capture the group ID number. By default the person is an owner and they can add other owners. And indicate whether they want to be notified of changes.

Of course an owner can always manage the owners later. Or delete the record completely.

These may look like simple things, but they can have a big impact on your organization and how work gets done. If you would like help with any of this, feel free to reach out and book a call with me.