Create arrays which are named from another array

Question

So, I've got assessment marks for various students and classes. The script correctly works through the data and creates an array out of the names of each class.

In other words, part of what the script does is to create an array which may be something like

ArrayOfSets = [A1, A2, B1, B2, C1, D1, E1}

If the classes the students were in were called A1, A2, B1, ..., E1.

What I'd now like to do is some analysis of the assessment marks in each set.

To do that, I'd like to create an array of marks for each class, something like:

A1 = [14, 18, 21, 26] A2 = [29, 11, 24, 15] ... E1 = [22, 16, 3, 25]

Not sure how I might go about that? The original data is in three columns, with column 1 being the student name, column 2 being the student class and column 3 being the student assessment mark.

Example data set

When I have an array of marks for each individual class, I can then do some data analysis.

Many thanks,

Dave.

Answer 1

It can be something like this:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get the all data from the source sheet
  var src_sheet = ss.getSheetByName('Sheet1');
  var [header, ...data] = src_sheet.getDataRange().getValues();

  // make an object { a1: [ 162, 22 ], a2: [ 57, 42 ], b1: [ 50 ], ...}
  var data_obj = {};
  for (let [name, set, mark] of data) {
    try { data_obj[set].push(mark) }
    catch(e) { data_obj[set] = [mark] }
  }

  // make an array: [ [ 'a1', 162, 22 ], [ 'a2', 57, 42 ], [ 'b1', 50 ], ...]
  var data_array = [];
  for (let set in data_obj) data_array.push([set, ...data_obj[set]]);
  data_array.sort();

  // put the array on another sheet
  var dest_sheet = ss.getSheetByName('Sheet2');
  dest_sheet.clearContents();
  for (let row of data_array) dest_sheet.appendRow(row);
}

It should work fine the results table has dozens rows I think. But if it has hundreds rows the code (the appendRow() method in the last line) can be a little bit sluggish. Let me know if you need to speed up the code.