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:

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

With the output of:

John Doe 50 Band Mr. Dean
Mary Smith 60 US History Ms. Jones 25 Chemistry Ms. Dyar 40 Algebra 2 Ms. Harris
Bob Miller 55 Band Mr. Dean
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.

Sangbok Lee Sangbok Lee · Accepted Answer · 2017-02-28T02:48:44

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]]);
  }
}

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

2 Answers