How to append data to end of rows in Google Apps Script

Question

One of our high schools is trying to create weekly reports for students who are currently failing one or more classes. The reports are in the format linked below.

If a student is failing multiple classes, the information is on different rows. The information needs to be combined, with each student on one column - column 1 is the name, and the subsequent columns are for the grade/class/teacher information.

Currently, I have code that deletes blank sheets, creates a new sheet titled "Output", writes a header row, and writes unique names in column 1 (portions commented out so it doesn't create a duplicate "Output" sheet every time):

function copyRows() {
  //Initialize variables
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Sheet1");

/*  
  //Create output Sheet
  ss.insertSheet("Output");
  */
  var writeSheet = ss.getSheetByName("Output");
  /*

  //Delete unwanted columns (1, 3, 4, 7, 9)
  dataSheet.deleteColumn(1); //Deletes first column
  dataSheet.deleteColumn(2); // Deletes third column (because it shifts after every deletion)
  dataSheet.deleteColumn(2); //Deletes fourth column
  dataSheet.deleteColumn(4); //Deletes 7th column
  dataSheet.deleteColumn(5);//Deletes 9th column


  //Delete unwanted Sheets
  var deleteSheet2 = ss.getSheetByName("Sheet2");
  var deleteSheet3 = ss.getSheetByName("Sheet3");
  ss.deleteSheet(deleteSheet2);
  ss.deleteSheet(deleteSheet3);  

  //Write data to header row
  var headerRange = writeSheet.getRange("A1:V1");
  var headerValues = [
    ["name", "class1", "grade1", "teacher1","class2", "grade2", "teacher2","class3", "grade3", "teacher3","class4", "grade4", "teacher4","class5", "grade5", "teacher5","class6", "grade6", "teacher6","class7", "grade7", "teacher7"]
    ]
  headerRange.setValues(headerValues);
  */

  var lastRow = dataSheet.getLastRow();
  var mainArray = dataSheet.getRange(1, 1, lastRow, 4).getValues();

  var allNames = []; //List of all names, including duplicates
  var uniqueNames = []; //List of all unique names

  for (i = 0; i < mainArray.length; i++) { //Sets allNames
    allNames.push(mainArray[i][0]);
  }

  for (i = 0; i < allNames.length; i++) { //Sets uniqueNames
    if (allNames[i+1] != allNames[i]) {
      uniqueNames.push(allNames[i]);
    }
  }
  var uniqueNamesArray = uniqueNames;


  //New method that converts 1d array to 2d array

  Array.prototype.reshape = function(rows, cols) {
    var copy = this.slice(0); // Copy all elements.
    this.length = 0; // Clear out existing array.

    for (var r = 0; r < rows; r++) {
        var row = [];
        for (var c = 0; c < cols; c++) {
            var i = r * cols + c;
            if (i < copy.length) {
                row.push(copy[i]);
            } 
        }
      this.push(row);
    }
  };
  var uniqueNamesRow = uniqueNames;
  uniqueNames.reshape(uniqueNames.length, 1); //Changing uniqueNames from row to column


  var writeNamesRange = writeSheet.getRange(2,1,uniqueNames.length,1); //writeSheet column 1
  writeNamesRange.setValues(uniqueNames);

Example data:

1. John Doe 50 Band Mr. Dean
2. Mary Smith 60 US History Ms. Jones
3. Mary Smith 25 Chemistry Ms. Dyar
4. Mary Smith 40 Algebra 2 Ms. Harris
5. Bob Miller 55 Band Mr. Dean
6. Larry Jones 22 Algebra 2 Ms. Harris

With the output of:

1. John Doe 50 Band Mr. Dean

2. Mary Smith 60 US History Ms. Jones 25 Chemistry Ms. Dyar 40 Algebra 2 Ms. Harris

3. Bob Miller 55 Band Mr. Dean

4. Larry Jones 22 Algebra 2 Ms. Harris

Note that Mary Smith's data has been combined into one row.

I just cannot figure out how to iterate through the rows and append the data to the end of the appropriate row.

Sorry for the previous lack of details, and thanks for the feedback.

Answer 1

I tried this for a while, and came up with a different approach. Below in case someone finds it useful, I also added some comments

function appendToRecords2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetWithNewData = ss.getSheetByName("New");
  var sheetWithExistingData = ss.getSheetByName("Existing")
  // Defines the range where the NEW DATA is located
  var newData = sheetWithNewData.getRange("a2:b25") 
                              .getValues();
  var lastRow = sheetWithExistingData.getLastRow();
  sheetWithExistingData.getRange(lastRow + 1, 1, 24, 2)
              .setValues(newData);

/* 
Parameters for the last to one row
   1st = +1, this is always the same. This indicates that it's last row + 1
   2nd = The column where to copy the data, normally 1
   3rd = The number of rows that contains the new data. This is pretty much the same as the rage defined in getRange
   4th = The number of columns that contains the data, same as above
*/

}

IMPORTANT: All credit goes to @jvdh from his answer at Script to append range of data in Google Sheets. I only made some minor changes and added the comments clarifications

Answer 2

I wrote some codes based on your input and output (not on your code). Since student's name is a primary key, I think dictionary data structure is suitable here. Below code is not so elegant and many literals were hard-coded but I guess you'll be able to grasp the idea. Sample sheet is here.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('input');
  var rows = 6;
  var cols = 4;
  var input = ss.getRange(1,1, rows,cols).getValues();

  var students = [];
  for(var i = 0; i < input.length; i++) {
    var name = input[i][0];
    var class = {
      score: input[i][1],
      class: input[i][2],
      teacher: input[i][3]
    };

    var j;
    for(j = 0; j < students.length; j++) {
      if(students[j].name == name) {
        students[j].classes.push(class);
        break;
      }
    }
    if(j == students.length) {
      students.push({
        name: name,
        classes: [class]
      });
    }
  }

  var ts = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('output');

  var output = [];
  for(var i = 0; i < students.length; i++) {
    output[i] = [students[i].name];
    for(var j = 0; j < students[i].classes.length; j++) {
      output[i].push(students[i].classes[j].score,
                     students[i].classes[j].class,
                     students[i].classes[j].teacher);
    }
    ts.getRange(i+1,1, 1,output[i].length).setValues([output[i]]);
  }
}