How to sort a 2d array in Apps script (google sheet) base on a 1d array

Question

I have a script that I have been using for some time, but I recently had a bug on the data sorter and since then I have been unable to fix the problem.

I transpose arrays because they are normally horizontal.

I have a list of data and values ​​from staff rounds. There are missing values ​​depending on the round. I add the missing names with a value of " ", then sort them so I can paste the data into a database. But for a few days now, the sorting part hasn't been working properly.

• First row: Data name
• Second row: Data value
• Third row: Data name in the desired order

name :	A	B	E	D	C
value :	10	2	35	4	12
order :	A	B	C	D	E

the result it did before, with empty name at the end:

name :	A	B	C	D	E	""
value :	10	2	12	4	35	""

the result it give now:

name :	A	D	C	""	B	E
value :	10	4	12	""	2	35

Data : https://docs.google.com/spreadsheets/d/1HgguusJLXSomC5U2sDDwFqNL-isuPxM0gT8GjDoi2B8/edit?usp=drive_link

function test() {
  var objects = spreadsheet.getSheetByName("Feuille 121").getRange("A1:GG2").getValues()
  var order = spreadsheet.getSheetByName("Feuille 121").getRange("A3:GG3").getValues().flat()

  objects = Object.keys(objects[0]).map(function(c) {
    return objects.map(function(r) {
      return r[c];
    });
  });
  order = Object.keys(order[0]).map(function(c) {
    return order.map(function(r) {
      return r[c];
    });
  });

  // organise dans l'ordre des paramètre
  let orderIndex = {}
  order.forEach((value, index) => orderIndex[value] = index);

  // Sort
  objects.sort((a, b) => orderIndex[a[0]] - orderIndex[b[0]]);

  var length = objects.length

  objects = Object.keys(objects[0]).map(function(c) {
    return objects.map(function(r) {
      return r[c];
    });
  });
  console.log(objects)
  spreadsheet.getSheetByName("Feuille 121").getRange(1, 1, 2, length).setValues(objects)

}

Answer 1

The problem is that you try to transpose a flat array, which results in order becoming a different array than you intend.

The .flat() call in the var order = expression gives you a 1D array.

As you apply the transposition algorithm to a 1D array you get unexpected results. Specifically, after the flat call, order[0] is a string (not an array), so the length of the array that you get back in order is determined by the length of the string that happens to sit in cell A3, and each entry of order is now an array of single characters. If your headings (in row 1 of your sheet) have strings of more than one character, these characters in the modified order will not match your headings anymore and so nothing gets sorted.

As now order has become a 2D array of characters, the forEach loop on order iterates arrays, so the assignment to orderIndex[value] will be a coersion of such an array to string (including comma separators), resulting in an orderIndex that will be useless for sorting: most headings will not have a matching key in orderIndex.

The fix is straightforward: remove the statement that tries to transpose order.