I'm trying to find a streamlined way to keep members in about 100 Google groups up to date from info already in a Google Sheets workbook. One sheet has a list of 1700 students w/ columns for student email and group email. Another sheet contains a list of just the group names and the group emails. Based on the codes I found on it seems possible with Google apps script. Unfortunately, I have no previous experience with Apps Script and minimal experience with javascript. I've been trying to piece together a process that might look like this.
- Deleting all current members of the groups.
- Uploading the correct members.
- Rinse and repeat.
Not the most elegant, but it seemed workable.
This is what I've done so far and I've hit a wall.
I found the combination of these 2 functions will remove all members from 1 group:
//Function to Remove 1 group member from 1 group
function removeGroupMember(groupEmail, userEmail) {
Logger.log(userEmail)
userEmail = userEmail.trim();
AdminDirectory.Members.remove(groupEmail, userEmail);
}
//Remove all Members from 1 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)
}
}
I thought I could nest another loop to pair it with this code that grabs the group emails from the sheet.
//get all of the groups from the spreadsheet
function getGroupsFromSpreadsheet(){
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName('google_groups');
const data = sheet.getRange(2,2,100).getValues();
return data;
logger.log(data)
}
This is what I came up with after a few trial and errors. It runs from the script editor and the execution log says it's successful, but then nothing actually happens in Google Admin - they're all still in the groups.
function removeAllMembers() {
var groups = [getGroupsFromSpreadsheet()];
var groupEmail = [getGroupsFromSpreadsheet()];
var members = AdminDirectory.Members.list(groupEmail).members;
for (var element in groups) {
for (var m in members) {
var member = members[m];
var email = member.email;
removeGroupMember(groupEmail, email);
}
}
}
The issue in the code you have is in fact caused my the
groups
andgroupEmail
variables in the code. Therefore, I suggest you try this:Reference
Range Class Apps Script - getValues;
SpreadsheetApp Class Apps Script - openById() .