My program will go through a course an extract students name, students’ email, assignments name and submissions state and insert it to google sheets.
The code:
function courseData() {
const arguments = {
teacherId: 'me',
courseStates: 'ACTIVE'
};
try {
const course = Classroom.Courses.list(arguments).courses
for(let i = 0; i < course.length; i++){
Logger.log("course name: " + course[i].name)
Logger.log("course ID: " + course[i].id)
}
} catch (error) {
Logger.log('Error: ' + error);
}
}
function getAssignmentSubmissionState() {
var courseId = 'YOUR_COURSE_ID';
var assignments = Classroom.Courses.CourseWork.list(courseId).courseWork;
var students = Classroom.Courses.Students.list(courseId).students;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var newCheckbox = SpreadsheetApp.newDataValidation().requireCheckbox().setAllowInvalid(false).build();
sheet.clearContents();
var title = ["name", "email"];
var studentName = [];
var studentEmail = [];
var submissionState = [];
var assignmentTitle = [];
for (var i = 0; i < assignments.length; i++) {
var assignment = assignments[i];
var submissions = Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, assignment.id).studentSubmissions;
for (var j = 0; j < submissions.length; j++) {
var submission = submissions[j];
var student = students.find(function(student) {
return student.userId === submission.userId;
});
Logger.log("NAME: " + student.profile.name.fullName +", ASSIGNMENT: " + assignment.title + ", STATUS: " + submission.state);
studentName.push(student.profile.name.fullName);
studentEmail.push(student.profile.emailAddress);
submissionState.push(submission.state);
}
assignmentTitle.push(assignment.title)
title.push(assignment.title);
}
sheet.appendRow(title)
var lastRow = sheet.getLastRow() + 1;
for (var i = 0; i < studentName.filter((item, index) => studentName.indexOf(item) === index).length; i++) {
sheet.getRange(lastRow + i, 1).setValue(studentName[i]);
}
for (var i = 0; i < studentEmail.filter((item, index) => studentEmail.indexOf(item) === index).length; i++) {
sheet.getRange(lastRow + i, 2).setValue(studentEmail[i]);
}
var listLength = ((submissionState.length/assignmentTitle.length) - (studentName.filter((item, index) => studentName.indexOf(item) === index).length - assignmentTitle.length));
var finalSubmissionState = [];
for (var i = 0; i < submissionState.length; i += listLength) {
var eachSubmissionState = submissionState.slice(i, i + listLength)
finalSubmissionState.push(eachSubmissionState)
}
sheet.getRange(lastRow, 3, finalSubmissionState.length, finalSubmissionState[0].length).setValues(finalSubmissionState);
sheet.getRange(1, 1, 1, title.length).setValues([title]).setFontWeight("bold");
}
scopes:
"https://www.googleapis.com/auth/classroom.courses",
"https://www.googleapis.com/auth/classroom.coursework.me.readonly",
"https://www.googleapis.com/auth/classroom.profile.emails",
"https://www.googleapis.com/auth/classroom.profile.photos",
"https://www.googleapis.com/auth/classroom.rosters",
"https://www.googleapis.com/auth/classroom.coursework.me",
"https://www.googleapis.com/auth/classroom.coursework.me.readonly",
"https://www.googleapis.com/auth/classroom.coursework.students",
"https://www.googleapis.com/auth/classroom.coursework.students.readonly",
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/classroom.guardianlinks.me.readonly",
"https://www.googleapis.com/auth/classroom.guardianlinks.students.readonly",
"https://www.googleapis.com/auth/classroom.guardianlinks.students"
services:
classroom
sheets
The problem is submissions state is not inserted in the correct order (names and submissions state does not match)
correct output:
10:38:07 PM Info NAME: Alex Rosenberg, ASSIGNMENT: ASSIGNMENT 4, STATUS: TURNED_IN
10:38:07 PM Info NAME: Mr Squirrel, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:07 PM Info NAME: Anthony Skyba, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:07 PM Info NAME: MeMeBall, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:07 PM Info NAME: Khanaliev Markus, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:07 PM Info NAME: amir shekar, ASSIGNMENT: ASSIGNMENT 4, STATUS: CREATED
10:38:08 PM Info NAME: Alex Rosenberg, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info NAME: Mr Squirrel, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info NAME: Anthony Skyba, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info NAME: MeMeBall, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info NAME: Khanaliev Markus, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info NAME: amir shekar, ASSIGNMENT: ASSIGNMENT 3, STATUS: CREATED
10:38:08 PM Info NAME: Alex Rosenberg, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:08 PM Info NAME: Mr Squirrel, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:08 PM Info NAME: Anthony Skyba, ASSIGNMENT: ASSIGNMENT 2, STATUS: TURNED_IN
10:38:08 PM Info NAME: MeMeBall, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:08 PM Info NAME: Khanaliev Markus, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:08 PM Info NAME: amir shekar, ASSIGNMENT: ASSIGNMENT 2, STATUS: CREATED
10:38:09 PM Info NAME: Alex Rosenberg, ASSIGNMENT: ASSIGNMENT 1, STATUS: TURNED_IN
10:38:09 PM Info NAME: Mr Squirrel, ASSIGNMENT: ASSIGNMENT 1, STATUS: CREATED
10:38:09 PM Info NAME: Anthony Skyba, ASSIGNMENT: ASSIGNMENT 1, STATUS: TURNED_IN
10:38:09 PM Info NAME: MeMeBall, ASSIGNMENT: ASSIGNMENT 1, STATUS: CREATED
10:38:09 PM Info NAME: Khanaliev Markus, ASSIGNMENT: ASSIGNMENT 1, STATUS: NEW
10:38:09 PM Info NAME: amir shekar, ASSIGNMENT: ASSIGNMENT 1, STATUS: TURNED_IN
2
Answers
Although I’m not sure whether I could correctly understand your expected result, how about the following modification?
Modified script:
courseId
,assignments
,students
, I guessed that each row corresponding to each assignment title is retrieved.References:
SUGGESTION
I’ve tweaked a part on your existing code, specifically where you slice the gathered submission states, to achieve your expected output.
From:
To:
OUTPUT