Google-sheets – Custom Score for Spreadsheet

For the first column, in column D for example, use the following formula:

=UNIQUE(A1:A5)

and the second column, in column E for example:

=ARRAYFORMULA(IFERROR(VLOOKUP(D:D;A:B;{2}*SIGN(ROW(A:A));0),""))

A couple of days of looking at your code and I think I may have a solution, but it's a little bit of a kludge.

You can create a function that returns a 2D array which will fill the cells below and to the right of the original cell. So I would try something like this: (sample sheet here)

= getSheetNames(2)

where your student sheets are after the second sheet, and use this custom function:

function getSheetNames(after) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  if (arguments.length === 0) {
    after = 0;
  }
  var allSheets = ss.getSheets();
  var allSheetNames = [];
  var sheetCount = allSheets.length;

  for (var i = after; i < sheetCount; i++) {
    //as you're putting them all in one row, you don't use array wrappers
    allSheetNames.push(allSheets[i].getName());
    //because it's merged need to add extra cell
    allSheetNames.push('');
  }
  return allSheetNames;
}

Edit - as you are using one row and inserting into merged cells - I have made some changes