Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-classroom

problem to insert data in to google sheets in correct order using apps script

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

what i get: enter image description here

Solution

  • I was trying to slice the array using

    ((submissionState.length/assignmentTitle.length) - (studentName.filter((item, index) => studentName.indexOf(item) === index).length - assignmentTitle.length));

    it was incorrect!

    From:

    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);

    To:

    var finalSubmissionState = [];
  for (var i = 0; i < submissionState.length; i += eachStudentName.length) {
    finalSubmissionState.push(submissionState.slice(i, i + eachStudentName.length));
  }

  var stateLastRows = [];
  for (var i = 1; i <= finalSubmissionState.length; i++) {
    var eachRow = sheet.getRange(sheet.getLastRow() + 1, i).isBlank() ? sheet.getLastRow() : sheet.getLastRow() + 1;
    stateLastRows.push(eachRow);
  }

  for (var col = 0; col < finalSubmissionState[0].length; col++) {
    for (var row = 0; row < finalSubmissionState.length; row++) {
        sheet.getRange(stateLastRows[row] + col - 5, row + 3).setValue(finalSubmissionState[row][col]);
      
    }
  }