Google Classroom Assignment Completion Leaderboard

I've been using Google Classroom to post a lot of assignments that students can work on at their own pace; some of them are optional assignments, but most are required. We also have incentives set up for milestones such as 500 assignments completed by the class.

Rather than having to go through each assignment and see how many have been completed, and how many each student has completed, I've written a script that logs that information to a Google Spreadsheet. In case you'd like to do the same, here's the code and some basic instructions on how to set it up for yourself.

1. Create a Google spreadsheet with a list of student email addresses in column A starting at row 2.
2. Rename the sheet Achievements (or change line 17 of the code below).
3. Under the Tools menu choose Script editor and paste in the code below.
4. Follow the directions at https://developers.google.com/classroom/quickstart/apps-script to authorize your script.
5. Run (play) the function listCourses to find the courseId for the course that you want to run this on
6. Set up a trigger to run the function countClassroomAssignments() every morning or every week.

	// Create a Google spreadsheet with a list of student email addresses in column A starting at row 2.
	// Rename the sheet Achievements or change line 17 of this code.
	// Under the Tools menu choose Script editor and paste in this code.
	// Follow the directions at https://developers.google.com/classroom/quickstart/apps-script to authorize your script.
	// Set up a trigger to run countClassroomAssignments() every morning or every week.
	// https://developers.google.com/apps-script/reference/spreadsheet/sheet
	function listCourses() {
	// Run this then check the execution logs to find the courseId for your desired course
	var courses = Classroom.Courses.list().courses;
	for (x = 0; x < courses.length; x++) {
	Logger.log('%s : %s', courses[x].id, courses[x].name);
	}
	}
	function countClassroomAssignments() {
	var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Achievements");
	var lastRow = sheet.getMaxRows();
	var range = sheet.getRange(2, 1, lastRow); // row 2, column A, numberOfRows
	var users = range.getValues();
	for (i = 0; i < lastRow-1; i++) {
	var email = users[i] + domain;
	var numberSubmitted = countWork(email);
	//Logger.log(email)
	//Logger.log(numberSubmitted);
	sheet.getRange(i+2, 2).setValue(numberSubmitted);
	}
	}
	function countWork(email) {
	var courseId = ""; // get this from the output of function listCourses() and put it between the quotation marks
	var numberSubmitted = 0; // the variable we'll use for counting
	var assignments = Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, "-", {userId:email});
	var submissions = assignments.studentSubmissions;
	for (j = 0; j < submissions.length; j++) {
	var submission = submissions[j];
	var state = submission.state;
	if (state == "TURNED_IN" || state == "RETURNED") {numberSubmitted++;} // add 1 if it has been turned in or returned
	var grade = submission.assignedGrade;
	var draftGrade = submission.draftGrade;
	if (grade == 0 || draftGrade == 0) {numberSubmitted--;} // subtract 1 if you've given it a zero for being blank
	}
	return numberSubmitted;
	}

view raw countClassroomAssignments.gs hosted with ❤ by GitHub

My Classroom Technologies

In my grade six classroom we have been using a number of different educational technologies, including hardware, software, and web-based tools.

Our main hardware platform is a set of Dell Latitude E6410 laptops. Since these are fairly old machines, we have reimaged them with Lubuntu, a lightweight Linux distribution that makes them quite usable. We have a charging cart, extra batteries, and an external battery charger.

On these laptops we have installed the Chrome web browser (in addition to the included Firefox), Audacity for audio recording, Cheese for taking photos and videos, and MinecraftEdu.

Also in the classroom we have two Raspberry Pi computers running KanoOS for computational thinking activities, and a few older gaming consoles for indoor recesses, motivation, and esports (a subject for another post).

We have access to Chromebooks, iPads, tripods (with iPad brackets), microphones (with USB cables and lightning adapters), green/blue screens, a GoPro HERO Session camera, Mac mini computers, Lego Mindstorms EV3 robot kits, and programmable Parrot Rolling Spider drones.

Our web-based tools include:
Google Classroom - assignments and online interactions, and guardian email summaries
Google Sites - hosts our class website
Remind - communicating with my students' grown-ups
Classcraft - a gamified behaviour management system
Mathletics - math practice
Math Live - interactive math lessons
Prodigy - chocolate-covered broccoli math practice
Newsela - news and non-fiction at adjustable reading levels
Duolingo - French language learning
Kids A-Z (Raz-Kids) - optional reading (ebooks and audiobooks)
Code Combat - coding and computational thinking activities

And of course we use other platforms for occasional assignments, such as Marvel's Comic Creator or Weebly. We'll also be trying many other tools throughout the year. For the most part, though, we are not focusing on learning tools, but rather learning with the tools.