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)
}
order
is a 2D array. getValues()
always returns a 2D array regardless of the size of the range. flat
ten 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)
}