arraysgoogle-apps-scriptgoogle-apps-script-api

Adding Google Classroom ID based on user enrollment code


I have a spreadsheet where a user can list classes and Google Classroom enrollment codes, represented by the array userClassCodes. This array is allowed to contain blank values when the range contains blank cells. This array is represented in the following way:

[ ['class name 01', 'class code 01'], ['class name 02', 'class code 02'], ...]

I am using the Google Classroom API to get a list of the sheet user's enrollment codes and course IDs. I would like to iterate through the userClassCodes array and add the class ID to the array when there is a matching class code in the API response. If there is no match, I would like to preserve the entry in the array and add a blank value for the course ID.

I am having trouble properly constructing an array that will achieve the desired output. Here is my current code:

function googleClassroomImport() {
  var userClassCodes = SpreadsheetApp.getActive().getRange("Sheet1!A1:B25").getValues();
  var newArray = [];
  var options = {
    teacherId: 'me',
    courseStates: 'ACTIVE',
    pageSize: 50
  };
  var response = Classroom.Courses.list(options);

  response.courses.forEach(function (course) {
    for (let i = 0; i < userClassCodes.length; i++) {
      if (userClassCodes[i][1] == course.enrollmentCode) {
        newArray.push([userClassCodes[i][0], userClassCodes[i][1], course.id]);
      }
      else {
        newArray.push([userClassCodes[i][0], userClassCodes[i][1], ""]);
      }
    }
  });

  console.log(newArray);
}

Solution

  • Try this:

    function googleClassroomImport() {
      const ss = SpreadsheetApp.getActive();
      const sh = ss.getSheetByName("Sheet0");
      const rg = sh.getRange("A1:B25");
      const vs = rg.getValues().filter(r => r[0] && r[1]).filter(e => e);
      const arr = vs.map(r => r[1]);
      var options = { teacherId: 'me', courseStates: 'ACTIVE', pageSize: 50 };
      var response = Classroom.Courses.list(options);
      response.courses.forEach(course => {
        let idx = arr.indexOf(course.enrollmentCode);
        if (~idx) {
          vs[idx].push(course.id);
        } else {
          vs[idx].push('');
        }
      });
      console.log(newArray);
    }