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

errixed
October 10, 2023
192 views
1 vote
2 Answers

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:

Answers

Although I’m not sure whether I could correctly understand your expected result, how about the following modification?

Modified script:

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();
  sheet.clearContents();


  // I modified the below script.
  var studentObj = students.reduce((o, { userId, profile: { emailAddress, name: { fullName } } }) => (o[userId] = { emailAddress, fullName }, o), {});
  var { m, head } = assignments.reduce((o, { id, title }) => {
    o.head.push(title);
    Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, id).studentSubmissions.forEach(({ state, userId }) =>
      o.m.set(userId, o.m.has(userId) ? [...o.m.get(userId), state] : [state])
    );
    return o;
  }, { m: new Map(), head: ["name", "email"] });
  var values = [head, ...[...m].map(([k, v]) => [studentObj[k].fullName || null, studentObj[k].emailAddress || null, ...v])];
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values).offset(0, 0, 1, values[0].length).setFontWeight("bold");
}

When this script is run with valid values of 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:

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

To:

  var finalSubmissionState = [];
  // sets empty arrays depending on the number of students on your script
  for (i = 0; i < studentName.filter((item, index) => studentName.indexOf(item) === index).length; i++)
    finalSubmissionState.push([]);

  // separates each entry on your submission state array
  // to each individual final array, that corresponds
  // to each student
  for (i = 0; i < listLength; i++) {
    for (j = 0; j < studentName.filter((item, index) => studentName.indexOf(item) === index).length; j++) {
      finalSubmissionState[j].push([dataVal[(i * listLength) + j]]);
    }
  }

OUTPUT

Please signup or login to give your own answer.

Click here to cancel reply.

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

Answers

Modified script:

References:

SUGGESTION

OUTPUT