How to sync a Google Groups membership with a Google Spreadsheet?

Question

G'day,

I have several Google Groups under a Google Apps account that my nonprofit uses. Some of these groups are quite large and managing their membership with the web UI is pretty awful.

I'm hoping someone can help me come up with a script that can synchronize a list that I maintain on a Google Docs Spreadsheet with a Group so that when I remove someone from the sheet, they get removed from the group -- and when someone gets added to the sheet, they get added to the group.

I was taking a look at the Directory API but I'm not savvy enough to figure out how to build a script for what I need. Side note, I did play around with a different script someone posted here that uses similar API methods, and I got that particular script to work -- so in other words, my account is now properly set up for API access.

Perhaps the easiest way to do this programmatically would be to delete the group's membership entirely, then add each member again, each time the script runs?

Thank you in advance for your assistance.

Answer 1

Here are several functions that you can use to accomplish what you would like.

First here is the removal process. As commented by Sandy, create a column that you will use to mark members for removal. You should be able use what ever you like here as long as the ones not being deleted are blank. You will need to change the variable "marked" to reflect that columns number, you will also need to do the same for the "email" variable. That being the column that holds the users emails.

Make a copy of your sheet first and make sure that when the rows are deleted that they are the correct ones!

/**
 *Removes members from group that are marked for removal in a specific column
 */
function removeMarkedMembers() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  
  var range = sheet.getDataRange();
  var data = range.getValues();

  //Adjust these following variables to match your sheet
  var groupEmail = '[email protected]';//change to your group email address
  var marked = 7;//number of column used to mark for removal ex. Column A = 1
  var email = 4; //number of column that holds member email address.

  //must adjust columns numbers to zero based array integers
  marked = marked-1;
  email = email-1;

  var rows = [];

  for (var d in data) {
    var rowData = data[d];
    if(rowData[marked] != "" || rowData != null) {
      removeGroupMember(groupEmail, rowData[email]);
      rows.push(new Number(d)+1)
    }
  }

  for (var r in rows) {
    sheet.deleteRow(rows[r])
  }
}


function removeGroupMember(groupEmail, userEmail) {
  userEmail = userEmail.trim();
  AdminDirectory.Members.remove(groupEmail, userEmail);
}

Lastly here are some other functions for managing groups, use them as needed. Probably best to create a menu that you can just call these from inside the spreadsheet.

/**
 *Adds all email addresses in column to group.
 */
function addAllToGroup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var row = sheet.getLastRow();
  var column = 7;
  var range = sheet.getRange(2, column, sheet.getLastRow(), 1)
  var emails = range.getValues();

  for (var e in emails) {
    var email = emails[e]
    if(validateEmail(email)) {
      addGroupMember(email, '[email protected]');////Must replace with group email address you want them added too.
    }
  }  
}


function addGroupMember(userEmail, groupEmail) {
  var member = {
    email: userEmail,
    role: 'MEMBER'
  };
  AdminDirectory.Members.insert(member, groupEmail);
}

function validateEmail(email) { 
    var re = /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;
    return re.test(email);
} 

/**
 *Removes all members from a defined group
 */
function removeAllMembers() {
  var groupEmail = '[email protected]';
  var members = AdminDirectory.Members.list(groupEmail).members;

  for (var m in members) {
    var member = members[m];
    var email = member.email;
    removeGroupMember(groupEmail, email);
  }
}

This should help you get a nice finished product.

Edited Code to handle reported errors in the comments. Also updated the marked column must use an "x" for marker to be removed. Lastly to properly handle the deletion of the row, you need to clear it and then sort the entire sheet or your will get wrong rows removed. Remember to adjust to your parameters.

/**
 *Removes members from group that are marked for removal in a specific column
 */
function removeMarkedMembers() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');  
  var range = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn());
  var data = range.getValues();

  Logger.log(data)

  //Adjust these following variables to match your sheet
  var groupEmail = '[email protected]';//change to your group email address
  var marked = 2;//number of column used to mark for removal ex. Column A = 1
  var email = 1; //number of column that holds member email address.

  //must adjust columns numbers to zero based array integers.
  marked = marked-1;
  email = email-1;

  Logger.log(marked+' : '+email)

  var rows = [];

  for (var d in data) {
    var rowData = data[d];
    if(validateEmail(rowData[email]) && rowData[marked] == "x") {
      Logger.log('marked')
      removeGroupMember(groupEmail, rowData[email]);
      rows.push(new Number(d)+2)
    }
  }

  for (var r in rows) {
    var row = rows[r];
    sheet.getRange(row, 1, 1, sheet.getLastColumn()).clear();
  }
 range.sort(1);
}


function removeGroupMember(groupEmail, userEmail) {
  Logger.log(userEmail)
  userEmail = userEmail.trim();
  AdminDirectory.Members.remove(groupEmail, userEmail);
}

/**
 *Adds all email addresses in column to group.
 */
function addAllToGroup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var row = sheet.getLastRow();
  var column = 1;
  var range = sheet.getRange(2, column, sheet.getLastRow(), 1)
  var emails = range.getValues();

  for (var e in emails) {
    var email = emails[e]
    if(validateEmail(email)) {
      addGroupMember(email, '[email protected]');////Must replace with group email address you want them added too.
    }
  }  
}


function addGroupMember(userEmail, groupEmail) {
  var member = {
    email: userEmail,
    role: 'MEMBER'
  };
  var members = AdminDirectory.Members.list(groupEmail).members
  for (var m in members) {
    if(members[m].email == userEmail) {
      return 'Member already exist';
    }
  }
  AdminDirectory.Members.insert(member, groupEmail);
}

function validateEmail(email) { 
    var re = /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;
    return re.test(email);
} 

/**
 *Removes all members from a defined group
 */
function removeAllMembers() {
  var groupEmail = '[email protected]';
  var members = AdminDirectory.Members.list(groupEmail).members;

  for (var m in members) {
    var member = members[m];
    var email = member.email;
    removeGroupMember(groupEmail, email);
  }
}