arrayssortinggoogle-apps-script

Sort a 2d array with 1d array in appscript


I want, to sort a 2d array with a 1d array in appscript.

so that the 2D array is in the same order as the 1D array.

There is the link of the google sheet:

Array 1D
a
b
c
d
e
f
g
h
i
j
k
l
Array 2D
b 156
f 68
a 507
c 22
d 430
e 555
g 689
k 62
l 395
i 209
j 745
h 37

I use alphabet for the exemple but in my case is not just letter but full name.

I try the sript of a other post name : "Sort array of objects by arbitrary list in Javascript", but it doesn't work, it just give me back my 2d array with out sorting it.

the script i try:

const objects = spreadsheetApp.getActive().getSheetByName("feuille 2").getRange('A;B').getValues()
const order = spreadsheetApp.getActive().getSheetByName("feuille 1").getRange('A;A').getValues()


const orderIndex = {}
order.forEach((value, index) => orderIndex[value] = index);

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

// Log
console.log('orderIndex:', orderIndex);
console.log('objects:', objects);

Edit: Script I tried based on the answer, which didn't work:

function myFunction() {

let objects = SpreadsheetApp.getActive().getSheetByName("Feuille 7").getRange('D:E').getValues()
let order = SpreadsheetApp.getActive().getSheetByName("Feuille 7").getRange('A:A').getValues().flat()

/* Create a mapping object `orderIndex`:
*/

let orderIndex = {}
order.forEach((value, index) => orderIndex[value] = index);

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

// Log
Logger.log("/////////////////////////order////////////////////////////////")
Logger.log(order)
Logger.log("/////////////////////////objects////////////////////////////////")
Logger.log(objects)


SpreadsheetApp.getActive().getSheetByName("Feuille 7").getRange('H:I').setValues(objects)
}

Solution

  • order is a 2D array. getValues() always returns a 2D array regardless of the size of the range. flatten it to a 1D array:

    const order = spreadsheetApp.getActive().getSheetByName("feuille 1").getRange('A:A').getValues().flat()
    

    There's no id attribute in 2D arrays. Remove it and get each inner array's first index and sort based on it:

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

    There's also a typo on getRange calls: It should be A:B and A:A(colon: -not semicolon; )

    myFunction()
    
    function myFunction() {
    
    let objects = [['f',130],['b',5],['c',1]]
    let order = ['a','c','b','f']
    
    /* Create a mapping object `orderIndex`:
    */
    
    let orderIndex = {}
    order.forEach((value, index) => orderIndex[value] = index);
    
    // Sort
    objects.sort((a, b) => orderIndex[a[0]] - orderIndex[b[0]]) 
    Logger=console
    
    // Log
    Logger.log("/////////////////////////order////////////////////////////////")
    Logger.log(orderIndex)
    Logger.log("/////////////////////////objects////////////////////////////////")
    Logger.log(objects)
    }