Automate Adding Members to Google Group from Google Sheets — Google Apps Script

Urwa Shabir
Feb 7 · 2 min read

SCENERIO:
I was using Google Group to provide access to our dashboard in Google Data Studio. The members kept adding and the process became tedious. We were already keeping/adding all emails in a Google Sheet for some mapping. So this is what I did.

SOLUTION:
I added a script to Google Sheets that auto added new email addresses to the Google Group every time the sheet was edited.

CODE:

function addMembersFromSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YOUR_SHEET_NAME");
var emails = sheet.getRange(1,1,sheet.getLastRow()-1,1).getValues();
var group = GroupsApp.getGroupByEmail("group.name@yourcompany.com");
for(i = 0; i < emails.length; i++) { try {
addMember(emails[i][0], group);
}
catch(e) {
console.error(e);
continue;
}
}
}
function addMember(email, group) {

var hasMember = group.hasUser(email);
Utilities.sleep(1000);

if(!hasMember) {
var newMember = {email: email,
role: "MEMBER",
delivery_settings: "NONE"};
AdminDirectory.Members.insert(newMember, GROUP_ID);
}
}

COUPLE OF SMALL THINGS:

  • addMembersFromSheet function goes through a list of emails from first column of your sheet.
  • addMember takes two arguments the email and group from the first function and adds it to the group.
  • The API we have used here is AdminDirectory that you need to enable in “Resources” > “Advance Google Services” > “Admin Directory API”
  • I have used try-catch block to catch any emails that are invalid and capture the error in stackdriver logs.
  • I have also used continue to ensure even if the email is invalid, the script continues to run for the remaining email ids.
  • To ensure the script is not invoked too many times, I have used Utilities.sleep(1000);to cause a 1 second delay in the for loop.
  • Lastly, I have set up trigger on the script that is fired every time the sheet is edited, since the sheet is edited only when the emails have to be added.

This is all for today folks! See you soon and do not hesitate to ask any questions regarding Apps Script. I learnt by doing and would love to help you find your way.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade