G'day，
我的非营利组织使用的 Google Apps 帐号下有多个 Google 网上论坛。其中一些组非常大，使用 Web UI 管理其成员身份非常糟糕。
我希望有人能帮助我想出一个脚本，可以将我在Google文档电子表格上维护的列表与组同步，这样当我从工作表中删除某人时，他们就会从组中删除 - 当某人被添加到工作表时，他们会被添加到组中。
我正在查看目录API，但我不够精明，无法弄清楚如何为我需要的东西构建脚本。旁注，我确实使用了某人在此处发布的使用类似 API 方法的不同脚本，并且我让该特定脚本工作 - 换句话说，我的帐户现在已正确设置 API 访问。
也许以编程方式执行此操作的最简单方法是完全删除组的成员身份，然后在每次脚本运行时再次添加每个成员？
提前感谢您的帮助。
这里有几个函数可以用来完成你想要的
。首先是删除过程。 正如 Sandy 所评论的那样，创建一个列，您将使用该列来标记要删除的成员。您应该能够在此处使用您喜欢的任何内容，只要未删除的内容为空白即可。您需要更改变量"标记"以反映该列号，您还需要对"电子邮件"变量执行相同的操作。 那是保存用户电子邮件的列。
首先复制您的工作表，并确保在删除行时它们是正确的！
/**
*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 = 'group@email.com';//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);
}
最后，这里有一些用于管理组的其他功能，请根据需要使用它们。 可能最好创建一个菜单，您可以从电子表格中调用这些菜单。
/**
*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, 'your@groupemailaddress.com');////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 = 'group@email.com';
var members = AdminDirectory.Members.list(groupEmail).members;
for (var m in members) {
var member = members[m];
var email = member.email;
removeGroupMember(groupEmail, email);
}
}
这应该可以帮助您获得漂亮的成品。
编辑了代码以处理注释中报告的错误。还更新了标记的列必须使用"x"来删除标记。最后，要正确处理行的删除，您需要清除它，然后对整个工作表进行排序，否则将删除错误的行。请记住调整您的参数。
/**
*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 = 'test-group@email.com';//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, 'test-group@email.com');////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 = 'test-group@email.com';
var members = AdminDirectory.Members.list(groupEmail).members;
for (var m in members) {
var member = members[m];
var email = member.email;
removeGroupMember(groupEmail, email);
}
}