Why?
To ensure that everyone who should be enrolled has received an invitation and anyone who should no longer be enrolled is removed — all automatically at the click of a button.
How?
Add the following functions, and the two referenced, to the Apps Script Editor and then run it from the custom menu in the container sheet:
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Classroom')
.addItem('Sync students', 'syncStudents')
.addToUi();
}
function syncStudents() {
const courseId = "01234567890"; // See Notes, below
const studentsToEnrol = getStudentsFromSheet(); // See Notes, below
const studentsEnrolledOnCourse = getStudentsFromClassroom(courseId); // See Notes, below
const studentsToRemove = studentsEnrolledOnCourse.filter(a => !studentsToEnrol.includes(a));
const studentsToInvite = studentsToEnrol.filter(a => !studentsEnrolledOnCourse.includes(a));
// "Removed" variables for the summary message:
let removedCount = 0;
let removedList = "";
let notRemovedCount = 0;
let notRemovedList = "";
// Remove students from the course if they are no longer on roll:
for (const student of studentsToRemove) {
try {
Classroom.Courses.Students.remove(courseId, student);
removedCount += 1;
removedList += student + "\r\n";
} catch (err) {
notRemovedCount += 1;
notRemovedList += student + "\r\n";
}
}
// "Invited" variables for the summary message:
let invitedCount = 0;
let invitedList = "";
let notInvitedCount = 0;
let notInvitedList = "";
// Invite new students to the course:
for (const student of studentsToInvite) {
try {
Classroom.Invitations.create ({ userId: student, courseId: courseId, role: 'STUDENT' });
invitedCount += 1;
invitedList += student + "\r\n";
} catch (err) {
notInvitedCount += 1;
notInvitedList += student + "\r\n";
}
}
// Display summary message:
let summaryMessage = "";
let s = "s";
if (removedCount > 0) {
if (removedCount === 1) { s = ""; }
summaryMessage += "Successfully removed " + removedCount + " student" + s + ":\r\n\r\n" + removedList;
s = "s";
if (notRemovedCount > 0) { summaryMessage += "\r\n"; }
}
if (notRemovedCount > 0) {
if (notRemovedCount === 1) { s = ""; }
summaryMessage += "Failed to remove " + notRemovedCount + " student" + s + ":\r\n\r\n" + notRemovedList;
s = "s";
}
if ((removedCount > 0 || notRemovedCount > 0) && (invitedCount > 0 || notInvitedCount > 0)) { summaryMessage += "\r\n"; }
if (invitedCount > 0) {
if (invitedCount === 1) { s = ""; }
summaryMessage += "Successfully invited " + invitedCount + " student" + s + ":\r\n\r\n" + invitedList;
s = "s";
if (notInvitedCount > 0) { summaryMessage += "\r\n"; }
}
if (notInvitedCount > 0) {
if (notInvitedCount === 1) { s = ""; }
summaryMessage += "Failed to invite " + notInvitedCount + " student" + s + ":\r\n\r\n" + notInvitedList;
}
if (summaryMessage === "") { summaryMessage = "No changes required." }
let ui = SpreadsheetApp.getUi();
ui.alert("Summary", summaryMessage, ui.ButtonSet.OK);
}Notes
To get the course id, see Find the IDs of your courses.
To get the list of emails for students who should be enrolled, see Read in data from a sheet.
To get find out which students are already enrolled, see List students enrolled on a course.
The .remove method is referred to as .delete in the Classroom API Reference (“Delete” is a reserved word in Javascript, so its name needed to be changed for use in Apps Script.)
Permissions
To run successfully, you might (try it first and if it runs, don’t worry!) need to add the following scope to the “appsscript.json” manifest file:
"oauthScopes": [
"https://www.googleapis.com/auth/classroom.rosters"
]