Google Groups management through AppSheet

[Judson_White]

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());
});
}
}

[Koichi_Tsuji]

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 .

[Judson_White]

Yes I forgot to mention those EXTREMELY important steps. Thanks

[clementcjp]

@Judson_White You've mentioned that you use Google Groups to control which users can access your website.

My challenge is that I've been trying to use Google groups to let external users (specific ones only) to access the AppSheet but to no avail:
1) I create a Google Groups in my @owncompany.com domain
2) I add external users into the same group (maybe @customer.com)

3) I did the whole Domain auth and google groups settings correctly but the external users still cannot access the AppSheet.

Any advice where I should be looking at to rectify this?