I am working for a school, and obviously things are difficult right now. There is a Google Group for, basically, every class (there are some exceptions where 1 teacher has a single group for all their classes of a type, for instance) filled with all the students in that class so the teachers may easily email everyone at once. But, sections, groups, and student enrollments do and will continue to change. As such, I am trying to update the Google Groups to which students belong automatically based on some online reports. But I keep getting some sort of time out error. The first attempt was to simply delete all members from all groups one by one and then re-add all of them, but that kept timing out after running for 20 minutes - I actually just tried running the code to add EVERYONE again and it threw "Exceeded maximum execution time" on row 9924 of 36xxx. Then I tried comparing yesterday's student roster vs today's student roster and only adding/removing those students who were different (if they are in yesterday's roster but not today's they need removed, if they are in today's and not yesterday's they need added), but that was problematic because sometimes a group email (or rather the groups tied to a section) will change, so it wasn't removing students from groups that they should no longer be in based on a section-group mapping change (for instance, group 123 and 124 both mapped to Farris.History.1@domain.edu, but now 124 maps to Farris.History.2@domain.edu. But since yesterday's roster and today's roster both showed that student xyz belonged group 124, no change is made since the updates were based on roster changes and nothing changed on the roster, even though it should be mapped somewhere else).
So really, I need need some way to automatically make sure that all students are in the groups they should be in and only the groups they should be in, and it needs to update daily. I do have a sheet with the mapping of section ID -> group email and a sheet with student ID -> section ID. Sheet
Script things that I have tried:
function addAllMembers(){
const students = SpreadsheetApp.openById("student sheet").getSheetByName("Today").getDataRange().getValues();
const sched = SpreadsheetApp.openById("schedule sheet").getSheets()[0];
const sID = [].concat.apply([], sched.getRange(2,1,sched.getLastRow()-1).getValues());
const groups = sched.getRange(2,2,sched.getLastRow()-1).getValues();
let sEmail, ID, gEmail, r, group;
for (let i in add){
Logger.log(i);
sEmail = students[i][0]+"@domain.edu";
ID = students[i][5];
r = sID.indexOf(ID);
if(r > -1)
gEmail = groups[r][0];
try {
addGroupMember(sEmail, gEmail);
} catch(e) {
Logger.log(e);
}
}
}
^ This times out.
function getServerMembers(){
const sh = SpreadsheetApp.openById("server").getSheetByName("Server");
const grps = sh.getDataRange().getValues();
let out = [[]];
let mem;
// for (let i in grps){
for (let i = 0; i < 100; i++){
Logger.log(grps[i][1]);
if (grps[i][0] == "")
continue;
try {
out.push(GroupsApp.getGroupByEmail(grps[i][1]).getUsers());
} catch(e) {
Logger.log(i);
break;
}
// Utilities.sleep(1000);
}
Logger.log(out);
}
^ This was an attempt to rebuild the student roster from the server information rather than the teacher sheets. It failed because apparently you can't call the GroupsApp.getGroupByEmail().getUsers() function too many times. Also when I added the Utilities.sleep(1000) as suggested by the error message it still failed after timing out with just 55 of 800 groups, or threw an "Unknown error when on .getUsers()" message.
I am sorry for so much background, etc. I've just been pounding on this for days and the school really, really needs this working given the strange circumstances in which we all find ourselves.
