Bulk manage members of a Google Group - overview

The following Google Apps Script is designed to allow bulk management of members of a Google Group all from a Google Sheet. So with a list of email addresses and roles (owner, manager, member) you can add/remove/edit a large number of users with the click of a button. The tool provides an easier way of doing this as opposed to the web portal for managing members (www.groups.google.com).

Screenshot of the tool in a Google Sheet.

Features of the tool

• Overcomes some of the web portal limits on bulk adding members.
• Checks that the person running the tool is at least a 'manager' of the group so they do not accidentally lose access.
• Checks there is an 'owner' of the group present to prevent permanent loss of access to the group for everyone.
• Creates a full log of all changes made in the Google Sheet so you have a record of actions.
• Will stop if it encounters and issue with an email address (for example) but will not need to re-sync any members prior to that point.
• Uses toast popups to inform you of the progress as it may take several minutes to run depending on how many members you have.
• Uses an in-house API url to connect to Google Groups, for which the address has been removed from the Apps Script code (you will need to insert your own).
  

How does the tool work?

Details on the ‘Welcome’ sheet will connect to the relevant Google Group for syncing members. The button to start the process ‘Sync now’ is at the bottom of this sheet.

The ‘Users List’ sheet will contain a list of email addresses and roles (member, manager, owner) which will be compared with a list of current members of the Group (which the tool will automatically extract). This is the sheet that you need to maintain.
Any additions (members not currently in the Group but listed here), deletions (members not listed here but currently in the Group) and changes (members listed here and in the Group but with a different role) will then be automatically applied.

Note: this is not a complete solution here - it relies on API authentication which was provided at an institutional level and is unavailable outside of this. The code still provides invaluable insights however and you may have your own authentication you can plug in.

Download the tool

Download the bulk manage members of a Google Group tool here (please use 'File' > 'Make a copy' for your own version).

THE SCRIPTS

The following section details the specific Functions of the tool, giving you a better idea of their tasks. Here is a link to the manage a Google Group code blog post.

1) getGroupUsers

Runs the Function 'getSpreadsheet' to gather all of the data. Then runs the Function 'getGroupMembers' to get existing members of the Google Group. It pastes this data into the 'Group Members' sheet. Next it runs the Function 'syncUsers' to see if any changes between what is in the Google Sheet and what is in the Google Group exist. If there are differences then the relevant API calls are made to change/add/remove members.

There are a number of error-checking processes which take place as part of these steps (hence a lot of code) but it allows informing of the user if there is a problem, with a view to explaining what that problem may be. Plus allowing fellow developers to troubleshoot where an issue may lie. The final part of this Function looks at the status of the returned error variable to determine if everything ran successfully.

2) getSpreadsheet.gs

This Function gets the relevant spreadsheet information such as API details the user is asked to enter. It will also run the Function 'popupBoxBlanks' if there is missing information - to inform the user of this and prevent the rest of the script from running with incomplete data. This Function calls the 'getGroupMembers' Function to get existing group members, it then collates the information on the 'Users List' sheet.

If it is determined that the 'Users List' sheet is empty (ie maybe this is the first time the tool is being run) then it will copy over a list of existing members instead - rather than syncing to try and remove everyone. It will also run the Function 'checkOwnerRole' to make sure there is at least 'owner' listed in the Sheet

3) getGroupMembers.gs

This Function gets the existing Google Group members (their email address and role).

4) checkOwnerRole.gs

This Function looks through the 'Users List' sheet of data for at least 1 instance of the role 'owner' and that the user running the tool is listed as either 'owner' or 'manager' - to prevent a permanent loss of the Group for all and accidental removal of themselves.

5) syncUsers.gs

This Function goes through each member in the 'Users List' sheet and compares their details to those currently in the Google Group to see if anything needs updating/adding/removing. It uses JavaScript Objects to push items into as a key=value pair, using the email address as the key (since it is unique). It looks up the email address between the 2 sheets to then determine what action to take.

6) changeGroupUsers.gs

This Function changes the existing member's role in the Google Group to match the new one found on the 'Users List' sheet. It calls the relevant API url and sends the updated information.

7) addGroupUser.gs

This Function adds a new member to the Google Group. It calls the relevant API url and sends the new information.

 

8) deleteGroupUser.gs

This Function deletes an existing member of the Google Group after finding they no longer appear in the 'Users List' sheet, but are still present in the Group. It calls the relevant API url and sends the information for removal.

toastPopup.gs

This Function creates a Toast Popup notification with customised message. It is a standalone function so it can be called from anywhere else within Project.

logEvent.gs

This Function is used to output log information which may be useful for troubleshooting problems when running the code. It is also used to output all changes to the Google Group.  For further information see this blog post on logging actions when running a script.

popupBoxBlanks.gs

This Function creates a popup box for missing information on the 'Welcome' sheet to inform the user so they can fix it themselves.

statusUpdate.gs

This Function updates the Status cell on the 'Welcome' sheet which helps to inform the user of the progress of the tool. It is a standalone function so can be called at any time with the relevant message passed into it. This is so successes as well as errors can be displayed in a more user-friendly manner as opposed to the 'Log' sheet.

reset.gs

This Function allows the user to start again with the tool and quickly clear out the relevant input-boxes they are asked to complete. It will also clear the 'Users List' sheet.

onOpen.gs

This Function creates the Menu item for running the 'reset' Function.