Classroom Update Time Efficiency

[Chad Frerichs]

I'm trying to list Google Classroom update dates for course, announcements, & coursework as well as the teachers of each active course created after a certain date to a Sheet that is tied to a DataStudio visualization. I keep hitting the execution time limit. I would love any suggestions on how to improve the efficiency of the code below.

/**

 * Lists Google Classrooms that are active and created after a certain date, the teachers listed for those classrooms, and the last update time of CourseWork, Announcements and the Course itself to a defined tab in a Sheet.

 */

function listClassrooms()

{

  //Defining the sheet(aka tab) where the list will be dumped

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Classroom List');

  //Clearing the sheet of previous data

  sheet.clear();

  SpreadsheetApp.flush();

  //Preparing the array to house the data

  var classroomArray = [["Name","Teacher Name","Last Course Update","Last Announcement Update","Last Coursework Update"]];

  var courses = new Array();

  //Data regarding courses created after the following date will be gathered

  var dateToCheck = new Date("2018-07-01");

  var pageToken, page;

  //Loop through each page of courses creating an array of course information

  do

  {

    var response = Classroom.Courses.list({pageToken: pageToken});

    var currentPage = response.courses;

    for (i=0; i<currentPage.length; i++)

    {

      courses.push(currentPage[i]);

    }

    pageToken = response.nextPageToken;

  }

  while(pageToken);

  for (i = 0; i < courses.length-1; i++)

  {

    var course = courses[i];

    //Gather course creation date

    var creationDate = new Date(course.creationTime);

    //Check the creation date against the date set above

    if(course.courseState=="ACTIVE" && creationDate > dateToCheck)

    {

      //Get the most recent Announcement update date

      var announcementDate = latestAnnouncement(course.id);

      //Get the most recent CourseWork update date

      var cwDate = latestCoursework(course.id);

      //Get the most recent Course Update date

      var courseDate = new Date(course.updateTime);

      //Gather the list of teachers for the course

      var teacherList = getTeacherList(course.id);

      //Append the data to the array

      Logger.log(course.name + " processed");

      classroomArray.push([course.name, teacherList, courseDate, announcementDate, cwDate]);

    }

  }

  //Dump the array of data to the sheet

  sheet.getRange(1, 1, classroomArray.length, 5).setValues(classroomArray);

  SpreadsheetApp.flush();

}

//Get the most recent Announcement update date

function latestAnnouncement(id)

{

  if (Classroom.Courses.Announcements.list(id).announcements)

  {

    var announcements = Classroom.Courses.Announcements.list(id,{ orderBy: "updateTime"}).announcements;

    var announcementDate = new Date(announcements[0].updateTime);

  }

  return announcementDate;

}

//Get the most recent CourseWork update date

function latestCoursework(id)

{

  if (Classroom.Courses.CourseWork.list(id).courseWork)

  {

    var cw = Classroom.Courses.CourseWork.list(id ,{ orderBy: "updateTime"}).courseWork;

    var cwDate = new Date(cw[0].updateTime);

  }

  return cwDate;

}

//Gather the list of teachers for the course

function getTeacherList(id)

{

  var teachers = Classroom.Courses.Teachers.list(id).teachers;

  var teacherNum = teachers.length-1;

  var teacherList = "";

  //Gather teachers' readable names

  for ( j = 0; j <= teacherNum; j++)

  {

    teacherList += teachers[j].profile.name.fullName;

    if (j < teacherNum)

    {

      teacherList += ", ";

    }

  }

  return teacherList;

}