I'd like to allow a non admin user in our domain to be able to use a Google Sheet which is running code to:
- List some groups in our organisation
- Delete/Add users
This requires admin rights, so the following code will not run in a non-admin account... How is it possibile to authorise the non admin user to run admin code?
var RatatoskSheet = SpreadsheetApp.getActiveSpreadsheet();
// -- ADD USER TO GROUP -- Set trigger to onedit -- //
function addUsertoGroup(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() === 'AddUser') { //Hinders edits on other sheets
var userData = SpreadsheetApp.getActive().getSheetByName('AddUser');
var userEmail = userData.getRange(2, 1).getValue(); //Gets data from AddUser.A2
var groupId = userData.getRange(2, 2).getValue(); //Gets data from cell B2
var newMember = {
email: userEmail,
role: "MEMBER"
};
AdminDirectory.Members.insert(newMember, groupId); // Adds new member to a Google group
var groupData = SpreadsheetApp.getActive().getSheetByName('GroupAddress');
var groupTwo = [userEmail, groupId]
groupData.appendRow(groupTwo); //Add member and group to GroupAddress
var header = ['UserEmail', 'GroupID'];
userData.clear(); //Reset AddUser (Delete all)
userData.appendRow(header).setFrozenRows(1);
}
}
// -- REMOVE USER FROM GROUP -- Set trigger of this function to onedit -- //
function deleteGroupMember(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() === 'RemoveUser') { //Hinders edits on other sheets
var RemoveUserData = SpreadsheetApp.getActive().getSheetByName('RemoveUser');
var groupData = SpreadsheetApp.getActive().getSheetByName('GroupAddress');
var userEmail = RemoveUserData.getRange(2, 1).getValue(); //Gets data from RemoveUser.A2
var groupId = RemoveUserData.getRange(2, 2).getValue(); //Gets data from RemoveUser.B2
AdminDirectory.Members.remove(groupId, userEmail); //Removes member from a Google group
var removeDataValues = RemoveUserData.getDataRange().getValues();
var groupDataValues = groupData.getDataRange().getValues();
var resultArray = [];
for (var n in groupDataValues) { //
var keep = true
for (var p in removeDataValues) {
if (groupDataValues[n][0] == removeDataValues[p][0] && groupDataValues[n][1] == removeDataValues[p][1]) {
keep = false;
break;
}
}
if (keep) {
resultArray.push(groupDataValues[n])
};
}
var start = 2; //Starts from Row 2 //
var killTheRows = groupData.getLastRow() - start 1; // // These lines deletes all rows in GroupAddress
groupData.deleteRows(start, killTheRows); //Delete all rows with values//
groupData.getRange(2, 1, resultArray.length, resultArray[0].length).setValues(resultArray); //Repopulate the rows in GroupAddress
var header = ['UserEmail', 'GroupID'];
RemoveUserData.clear();
RemoveUserData.appendRow(header).setFrozenRows(1);
}
}
// -- LISTS ALL GROUPS AND USERS WITHIN THEM -- Set this as a timed trigger to error correct once a day -- //
function listAllGroups() {
var grouprows = [];
var pageToken;
var page;
do {
page = AdminDirectory.Groups.list({
domain: 'THEDOMAIN',
maxResults: 200,
pageToken: pageToken
});
var groups = page.groups;
if (groups) {
for (var i = 0; i < groups.length; i ) {
var group = groups[i];
if (group.email.substring(0, 5) === "staff") {
grouprows.push(group.email);
}
}
}
pageToken = page.nextPageToken;
} while (pageToken);
var rows = [];
var pageToken, page2;
for (var j = 0; j < grouprows.length; j ) {
do {
page2 = AdminDirectory.Members.list(grouprows[j], {
domainName: 'YOURDOMAIN',
maxResults: 500,
pageToken: pageToken,
});
var members = page2.members;
if (members) {
for (var i = 0; i < members.length; i ) {
var member = members[i];
var row = [member.email, grouprows[j]];
rows.push(row);
}
}
pageToken = page2.nextPageToken;
} while (pageToken);
if (rows.length > 1) {
var groupData = RatatoskSheet.getSheetByName("GroupAddress");
var header = ['UserEmail', 'GroupID'];
groupData.clear();
groupData.appendRow(header).setFrozenRows(1);
groupData.getRange(2, 1, rows.length, header.length).setValues(rows);
}
}
groupData.deleteRow(2); //NB! Removes first group([email protected]) Make this whole line a comment if unsure.
}
CodePudding user response：
A way to achieve this is by creating a service account and using domain wide delegation of authority.
After that, since you want to continue on using Apps Script, you will have to get the access token for this service account and make the request using UrlFetchApp since you cannot pass an access token while making a request using Admin SDK Directory advanced service.
Therefore, the requests will end up looking something similar to:
var options = {
method: "GET",
contentType: "application/json",
muteHttpExceptions: true,
};
var response = UrlFetchApp.fetch('https://admin.googleapis.com/admin/directory/v1/groups/{groupKey}/members', {
headers: {
Authorization: 'Bearer ' token
}
});
However, this approach might depend on the restrictions you have set in place for your account.
Another option is to deploy your script as a web app
For deploying a web app, the script should contain a doGet(e) or a doPost(e) and return a HTML service HtmlOutput object or a Content service TextOutput object.
After doing this, you should deploy the web app with the following settings:
Execute as: Me
Who has access: Anyone within domain