Sorting background colors in a range of cells using custom comparator function

Question

I use a script to sort a sheet on a particular item in a column. Data in this column is a number preceded by letters so the normal sort method from the range or sheet class does not work properly.

Here is the script, the actual question comes below.

function sortIDs(){ // test on column 5
  sortOnNumbersInCol(5);
}

function sortOnNumbersInCol(col){ // numeric sort except for 2 first rows on numeric value in column col
  var sh = SpreadsheetApp.getActive().getSheetByName('copie de travail');
  var data = sh.getDataRange().getValues();
  //Logger.log('length départ='+data.length);
  var codes = data.shift();
  var headers = data.shift();
  var dataOut=[];
  for(var n=0 ; n<data.length ; n++){
    if(data[n][col] != ''){
      dataOut.push(data[n]);
      //Logger.log('data['+n+']['+col+']='+data[n][col].substring(7)+'|');
    }
  }  
  dataOut.sort(function(a,b){
    var aE,bE;
    aE=a[col].substring(7); // the numeric part comes at the 7 th position (and following)
    bE=b[col].substring(7);
    return aE - bE
  })
  dataOut.unshift(headers);
  dataOut.unshift(codes);
  sh.getRange(1,1,dataOut.length,dataOut[0].length).setValues(dataOut);
}

This works perfectly for data sorting but it doesn't care about cell colors obviously... Some of my coworkers use colors to designate items in this sheet and when I sort the range the colors don't follow.

So my question is : How can I sort this sheet with my specific criteria and keep the correlation with cell colors ?

below is a screen capture of sheet data

If I run my script on this range the colors won't move... that's my problem ;)

and thanks for the title edit by the way, I had no inspiration, yours is very nice ! — Serge insas
Hm, you can prepare a getBackgrounds array, then instead of pushing values in dataOut array, how about expanding a little and pushing an object for each value with properties like { color : "000000", value: "" }. Or what TheMaster said below for an optimal solution: — Oleg Valter is with Ukraine
Create a map of {data:colors}. Then you could easily map the sorted data to colors — TheMaster
@Sergeinsas - re:edit - thank you, too bad I am editing more than answering as of late :) Anyways, since TheMaster is already adding an answer, I will leave that to them, I like the idea about mapping values to colors more than creating an extra matrix of data structures that came to my mind from the start — Oleg Valter is with Ukraine
FWIW, The normal sort method can be used if you use proper numbering like 01 instead of 1: MCINPRO04 instead of MCINPRO4. Constant 2 or 3 digits should work. — TheMaster

TheMaster TheMaster · Accepted Answer · 2020-07-26T13:52:56

Update:

Another way to do this without the space complexity is by sorting the keys/indexes instead of the actual array.

function sortOnNumbersInCol(col = 5) {
  const sh = SpreadsheetApp.getActive().getSheetByName('copie de travail');
  const dataRg = sh.getDataRange();
  const noOfHeadrs = 2;
  const rg = dataRg.offset(
    //Remove  headers before getting values
    noOfHeadrs,
    0,
    dataRg.getNumRows() - noOfHeadrs,
    dataRg.getNumColumns()
  );
  let data = rg.getValues();
  let colors = rg.getBackgrounds();

  /* Filter Empty rows if needed 
  [data, colors] = [data, colors].map(arr =>
    arr.filter((_, i) => data[i][col] !== '')
  );*/

  const dataKeys = [...data.keys()];
  dataKeys.sort(function(a, b) {
    return data[a][col].substring(7) - data[b][col].substring(7);
  });
  const [dataOut, colorsOut] = [data, colors].map(arr =>
    dataKeys.map(i => arr[i])
  );
  const outRg = sh.getRange(
    noOfHeadrs + 1,
    1,
    dataOut.length,
    dataOut[0].length
  );
  outRg.setValues(dataOut);
  outRg.setBackgrounds(colorsOut);
}

Create a map of {data:colors}:

  const rg = sh.getDataRange();
  const data = rg.getValues();
  const colors = rg.getBackgrounds();
  const dcMap = data.reduce((mp, row, i) => mp.set(row[col], colors[i]),new Map)

Then after sorting, you could use the sorted values as key and create a new sorted color array:

  dataOut.sort(function(a,b){
    var aE,bE;
    aE=a[col].substring(7); // the numeric part comes at the 7 th position (and following)
    bE=b[col].substring(7);
    return aE - bE
  })
  dataOut.unshift(headers);
  dataOut.unshift(codes);

  const sortedColors = dataOut.map(row=>dcMap.get(row[col]));
  const outRg = sh.getRange(1,1,dataOut.length,dataOut[0].length);
  outRg.setValues(dataOut);
  outRg.setBackgrounds(sortedColors);
}

Sorting background colors in a range of cells using custom comparator function

3 Answers