Google Groups management through AppSheet

I use google groups to manage external users access to my website. The website allows those users to request to be added to certain events on a Google Calendar. I treat them like users but they do not have access to the AppSheet application. However I found a way to easily add and remove access to Google Groups in AppSheet/Google Sheets using JavaScript, then triggering the script when the data source is modified (On Changed). My brother in law really helped with the JavaScript. We didn’t add code to check for blanks so I used a different google sheet with the sort function (=SORT(Data!A2:B)) that automatically removed the blank rows if you delete a member.

Anyway I figured I should share this here since this community has been very helpful to me over the past year as I built my apps.

function modify() {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“GROUP”);
let emails = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();
let group = GroupsApp.getGroupByEmail(“YOURGROUP@DOMAIN.COM”);
let current_group_size = group.getUsers().length;
let current_on_boarded_memebers = emails.length;
let operation = current_group_size < current_on_boarded_memebers;

// Just a note here if the operation is true then it is an addition to the group.
// If the operation is false then it is a subtraction and removal from the group.
if (operation) {
// create a map of the users email addresses so that there are no duplicates.
let current_members = new Map();
group.getUsers().forEach((v, k) => current_members.set(v.getEmail().toLowerCase(), true));
// create a new member and assign them to the group.
emails.forEach((v, k) => {
if (!current_members.has(v[0].toLowerCase())) {
Utilities.sleep(250);
let member = {
email: v[0],
role: “MEMBER”
};
console.log(JSON.stringify(member));
AdminDirectory.Members.insert(member, “YOURGROUP@DOMAIN.COM”);
}
});
} else {
// filters the group for users that are not included in the email list.
let consolidatedEmails = ;
for (var i = 0; i < emails.length; i++) {
consolidatedEmails.push(emails[i][0].toLowerCase());
}
let absent = group.getUsers().filter(e => !consolidatedEmails.includes(e.getEmail().toLocaleLowerCase()));
// remove each user that was not found in the email list.
absent.forEach((v, k) => {
Utilities.sleep(250);
AdminDirectory.Members.remove(“YOURGROUP@DOMAIN.COM”, v.getEmail());
});
}
}

1 Like

Thanks for sharing , and I do have similar arrangement as well as own Google App Script to get the Google Group user list through API and dump data to Google Spreadsheet.

Allow me to add important point to let your code to work on GAS, we need to enable 2 x Google Api as follows.

Groups Settings API and Admin DSK API must be turned ON on the resource tab on App Script .

3 Likes

Yes I forgot to mention those EXTREMELY important steps. Thanks

1 Like