如何将Google网上论坛会员与Google电子表格同步？ [英] How to sync a Google Groups membership with a Google Spreadsheet?
问题描述
今天，
我的非营利组织使用的Google Apps帐户下有几个Google网上论坛。其中一些团体规模很大，通过网络用户界面管理其会员资格非常糟糕。
我希望有人能够帮助我想出一个脚本，它可以将我在Google文档电子表格中保留的列表与一个组同步，以便当我删除某人从表中，他们被从组中删除 - 当有人被添加到表中时，他们会被添加到组中。
我正在看看目录API ，但我不懂足以想出如何为我所需要的构建脚本。注意，我确实玩过不同的脚本，这里发布的人使用类似的API方法，并且我得到了特定的脚本来工作 - 换句话说，我的帐户现在已经正确设置为API访问。
也许最简单的方法是通过编程方式完全删除组的成员资格，然后在每次脚本运行时再次添加每个成员？
预先感谢您的帮助。
以下是几个可用于完成您的功能的功能想要。
首先这里是删除过程。正如桑迪所评论的那样，创建一个您将用于标记要移除的成员的列。只要没有被删除的项目是空白的，你应该可以使用你喜欢的任何东西。您需要更改变量已标记以反映该列号，您还需要对电子邮件变量执行相同的操作。这是保存用户电子邮件的列。
首先制作工作表副本，并确保当行被删除时它们是正确的！ p>
/ **
*从组中删除标记为删除的成员
* /
函数removeMarkedMembers（）{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sheet = ss.getSheetByName（'Sheet1'）;
var range = sheet.getDataRange（）;
var data = range.getValues（）;
//调整以下变量以匹配工作表
var groupEmail ='group@email.com'; //更改为您的群组电子邮件地址
var marked = 7; //用于标记删除的列的数量列A = 1
var email = 4; //保存成员电子邮件地址的列数。
//必须将列数调整为零，以数组为单位
marked = marked-1;
email = email-1;
var rows = [];
（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（行[r]）
}
}
函数removeGroupMember（groupEmail，userEmail）{
userEmail = userEmail.trim（）;
AdminDirectory.Members.remove（groupEmail，userEmail）;
}
最后，这里还有一些用于管理组的功能，根据需要使用它们。可能最好创建一个菜单，您可以从电子表格中调用这些菜单。
/ **
*添加列中的所有电子邮件地址组。
* /
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（）; （电子邮件地址）
$ b $ {
var email = emails [e]
if（validateEmail（email））{
addGroupMember（email，'your @ groupemailaddress.com'）; ////必须替换为您希望他们添加的群组电子邮件地址。
$ b函数addGroupMember（userEmail，groupEmail）{
var member = {
email：userEmail ，
角色：'会员'
};
AdminDirectory.Members.insert（member，groupEmail）;
}
函数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）;
$ b $ **
*从定义组中删除所有成员
* /
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）;
}
}
这应该有助于您获得完美的成品。 / b>
编辑代码来处理评论中报告的错误。
也更新了标记列，必须使用x除去。
最后，要正确处理删除行，您需要清除它，然后对整个工作表进行排序，否则将删除错误的行。
/ **
*从组中删除标记为删除的成员（）;
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（数据）
//调整以下变量以匹配工作表
var groupEmail ='test-group@email.com'; / /更改为您的群组电子邮件地址
var marked = 2; //用于标记为删除的列的数量列A = 1
var email = 1; //保存成员电子邮件地址的列数。
//必须将列数调整为基于数组的整数。
标记=标记为1;
email = email-1;
Logger.log（标记+'：'+ email）
var rows = [];
（var d in data）{
var rowData = data [d];
if（validateEmail（rowData [email]）&& rowData [marked] ==x）{
Logger.log（'marked'）
removeGroupMember（groupEmail，rowData [电子邮件]）;
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）;
$ b函数removeGroupMember（groupEmail，userEmail）{
Logger.log（userEmail）
userEmail = userEmail.trim（）;
AdminDirectory.Members.remove（groupEmail，userEmail）;
}
/ **
*将列中的所有电子邮件地址添加到组中。
* /
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（）; （电子邮件）
$ b $ {
var email = emails [e]
if（validateEmail（email））{
addGroupMember（email，'test- group@email.com'）; ////必须替换为您希望他们添加的群组电子邮件地址。
$ b函数addGroupMember（userEmail，groupEmail）{
var member = {
email：userEmail ，
角色：'会员'
};
var members = AdminDirectory.Members.list（groupEmail）.members
for（var m in members）{
if（members [m] .email == userEmail）{
返回'成员已经存在';
}
}
AdminDirectory.Members.insert（member，groupEmail）;
}
函数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）;
$ b $ **
*从定义组中删除所有成员
* /
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）;
}
}
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.
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 = '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);
}
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, '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);
}
}
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 = '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);
}
}
这篇关于如何将Google网上论坛会员与Google电子表格同步？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
