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

Google Apps Script to list all Google Classroom courses and put them in a Google Spreadsheet


I am trying to create a Google Apps Script that will list all the Google Classroom courses that are active and archived, along with the ID, NAME, SECTION, and COURSESTATE. Everything works except that I have no idea how to fix the .getRange so that it will put all the information in the Google Spreadsheet. The error I get is "Incorrect range height, was 4 but should be 10". If I put the .getRange as simply "A1:D1", it will overwrite what is there for each Class until the script finishes, so I know the rest of the script works, but I can't figure out how to put the range so that all the Classes can be listed. What I have up to now is this:

function listCourses() {
var response = Classroom.Courses.list();
var courses = response.courses;
 if (courses && courses.length > 0) {
  for (i = 0; i < courses.length; i++) {
   var course = courses[i];
   var ids = course.id;
   var title = course.name;
   var sec = course.section;
   var state = course.courseState; 
   var arr1 = [];
   arr1.push(ids,title,sec,state); 
   var arr2 = [];
   while(arr1.length) arr2.push(arr1.splice(0,1));

var s = SpreadsheetApp.getActiveSpreadsheet();
var sh = s.getSheetByName('LISTS');

  for (var x=0; x<arr2.length; x++){   
  var newRow = sh.getLastRow() + 1; 

// Here in the .getRange is where it gives me the error. A new row should be added for each Class until all the Classes (with the information mentioned above) are listed. 

  sh.getRange(1, 1, newRow, arr2[0].length).setValues(arr2);   
  }

}}}

Solution

  • Try something like this: I don't use Classroom API much and I only have one class in it but this works for it.

    function listCourses() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sh = ss.getSheetByName('LISTS');
      var response = Classroom.Courses.list();
      var courses = response.courses;
      var arr=[];//You could put column headers in here
      for (i = 0; i < courses.length; i++) {
        var course = courses[i];
        var ids = course.id;
        var title = course.name;
        var sec = course.section;
        var state = course.courseState;  
        arr.push([title,sec,state]); //you could also go sh.appendRow([title,sec,state]); here if you wish and avoid the use of the two dimensional array all together as suggested in the other answer.
      }
      sh.getRange(1, 1, arr.length, arr[0].length).setValues(arr);   
    }