How to get google classroom assignment data using app script in google sheets

Question

Hi I am trying to get google classroom assignment of a student list in my sheets. I know how I need to add its value in the sheet but problem is that I am not getting data in the format in which I wish to have it. I am getting the data in wrong format. Can anyone of you actually help me on it. Here is my code.

  const courseWork = Classroom.Courses.CourseWork.list(courseId, { orderBy: 'dueDate desc' }).courseWork
        .filter(x => _.lowerCase(x.title).indexOf('assignment') !== -1 || _.lowerCase(x.title).indexOf('challenge') !== -1)
        .map(x => {
            return _.assign(
                x,
                { sub: _.groupBy(Classroom.Courses.CourseWork.StudentSubmissions.list(courseId, x.id).studentSubmissions, 'userId') }
            );
        });

    const topics = _(courseWork)
        .groupBy('topicId')
        .mapKeys((v, k) => _.find(Classroom.Courses.Topics.list(courseId).topic, ['topicId', k]).name)
        .toPairs()
        .map(x => { return { name: x[0], cw: x[1] }})
        .value();

    sheet.getRange('F1:1').clear();
    sheet.getRange(1, 6, 1, topics.length + courseWork.length).setValues([_.flatMap(topics, t => [t.name, ..._.map(t.cw, 'title')])]);

    sheet.getRange('C2:100').clear().removeCheckboxes();    // assumption: 100 se zyada student nahi honge
    sheet.getRange(2, 3, studentsList.length, 3 + topics.length + courseWork.length)
        .setValues(studentsList.map(s => {
            const topicTreeForStudent = topics.map(t => {
                const sub = t.cw.map(x => x.sub[s.userId][0].state);
                return _.assign(t, { sub: sub, submittedCount: sub.filter(st => st === 'TURNED_IN' || st === 'RETURNED').length });
            });

            return [
                s.profile.name.fullName, s.profile.emailAddress, _.sumBy(topicTreeForStudent, 'submittedCount') / courseWork.length,
                ..._(topicTreeForStudent).map(x => [x.submittedCount / x.sub.length, ...x.sub]).flatten()
            ];
        }));

The problem is that the data I am getting is in format like this shown in picture but I only want to see turned in or missing or late status here with respective row . I don't want the status to be showing me created i just want to see the data with respective email id if that assignment was submitted or is yet missing or student submitted it late.

Answer 1

I don't know if here are the true heroes who can to untangle your gorgeous code (I failed)... So, as the last resort, I'd suggest (faintheartedly) just to filter the result table. Something like that:

function remove_all_but_TURNED_IN() {
  const ss = SpreadsheetApp.getActiveSheet();
  const data = ss.getDataRange().offset(1,0).getValues();
  const new_data = data.filter(x => x[3] == 'TURNED_IN');
  ss.getDataRange().offset(1,0).clearContent();
  ss.getRange(2,1,new_data.length,new_data[0].length).setValues(new_data);
}

Input:

┌───────────────┬─────┬─────────────┬─────────────┬─────────────┬─────────────┐
├───────────────┼─────┼─────────────┼─────────────┼─────────────┼─────────────┤
│ '[email protected]' │ 123 │  'CREATED'  │  'CREATED'  │  'CREATED'  │  'CREATED'  │
│ '[email protected]' │ 456 │ 'TURNED_IN' │ 'TURNED_IN' │ 'TURNED_IN' │ 'TURNED_IN' │
│ '[email protected]' │ 789 │  'CREATED'  │  'CREATED'  │  'CREATED'  │  'CREATED'  │
│ '[email protected]' │ 123 │ 'TURNED_IN' │ 'TURNED_IN' │ 'TURNED_IN' │ 'TURNED_IN' │
└───────────────┴─────┴─────────────┴─────────────┴─────────────┴─────────────┘

Output:

┌───────────────┬─────┬─────────────┬─────────────┬─────────────┬─────────────┐
├───────────────┼─────┼─────────────┼─────────────┼─────────────┼─────────────┤
│ '[email protected]' │ 456 │ 'TURNED_IN' │ 'TURNED_IN' │ 'TURNED_IN' │ 'TURNED_IN' │
│ '[email protected]' │ 123 │ 'TURNED_IN' │ 'TURNED_IN' │ 'TURNED_IN' │ 'TURNED_IN' │
└───────────────┴─────┴─────────────┴─────────────┴─────────────┴─────────────┘