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 ;)

Answer 1

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);
}

Answer 2

If you want to keep things ES5, simply use an object as a map and sort colors and values independently of each other.

/**
 * 
 * @param {(string|number)[]} data
 * @param {string[]} colors
 * @returns {(string|number)[][][]}
 */
function sortOnNumbersInCol(data, colors, col) {

  //var codes = data.shift();
  //var headers = data.shift();

  var map = {};

  for (var n = 0; n < data.length; n++) {
    if (data[n][col] != '') {
      map[colors[n][col]] = data[n][col];
    }
  }

  var dataOut = data.sort(function(a, b) {
    var aE = a[col].substring(7);
    var bE = b[col].substring(7);
    return aE - bE;
  });

  var colorsOut = colors.sort(function(a, b) {
    var aC = map[a[col]].substring(7);
    var bC = map[b[col]].substring(7);
    return aC - bC;
  });

  //dataOut.unshift(headers);
  //dataOut.unshift(codes);
  
  return [
    dataOut,
    colorsOut
  ];
}

//testing is done with ES6 syntax

const createGrid = (parentTable, valGrid, colorGrid) => {
  const {
    firstElementChild
  } = parentTable;
  
  valGrid.forEach((r,rowIdx) => {
    const row = document.createElement("tr");
    
    r.forEach((c,cellIdx) => {
      const cell = document.createElement("td");
      cell.textContent = c;
      cell.style.backgroundColor = colorGrid[rowIdx][cellIdx];
      firstElementChild.append(cell);
    });
    
    firstElementChild.append(row);
  });
};

var col = 2;

var values = [
  [5, 6, "MCINPRO13"],
  [1, 2, "MCINPRO2"],
  [7, 8, "MCINPRO24"],
  [3, 4, "MCINPRO9"]
];

var colors = [
  [0, 0, "#D8D9DA"],
  [0, 0, "#007030"],
  [0, 0, "#6D99B4"],
  [0, 0, "#FF6347"]
];

const before = document.querySelector("#before");
createGrid(before, values, colors);

sortOnNumbersInCol(values, colors, col);

const after = document.querySelector("#after");
createGrid(after, values, colors);

<table id="before">
  <tbody>

  </tbody>
</table>

<hr>

<table id="after">
  <tbody>

  </tbody>
</table>

---

1. Note that the snippet below has header shift > unshift flow disabled for ease of testing and setValues removed to make the snippet runnable.
2. For simplicity of the snippet, sort is also performed directly on the data array, so you will have to map over the initial data to keep things pure: data = data.map(function (v) { return v; }).

Answer 3

Use the sort method from Class Filter as this besides including the cells background color will include other cell properties like notes, comments, data-validation and conditional formatting.

As the OP already mentioned this method can't be used directly as the column to be sorted has composed values (string + consecutive numbers without leading zeros). In order to do this we will need to add an auxiliary column which in this case is relatively easy as the range to be sorted is got by using getDataRange().

NOTES:

The following code use an arrow function so it requires to use the new runtime.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();

  // Add the auxiliary column
  var lastRow = sheet.getLastRow();
  var codes = sheet.getRange(1,2,lastRow).getValues();
  var newCodes = codes.map(([value]) => [value.match(/\d{1,}/)]);
  var newColumn = sheet.getRange(1,sheet.getLastColumn()+1,lastRow).setValues(newCodes);
  SpreadsheetApp.flush();

  // Sort
  var range = sheet.getDataRange();
  var values = range.getValues();
  var filter = range.createFilter();
  filter.sort(values[0].length, true);

  // Remove the filter and the auxiliary column
  filter.remove()
  sheet.deleteColumn(values[0].length)

}

Demostration

Before

After

---

If for any reason you decide to use the old runtime, replace

var newCodes = codes.map(([value]) => [value.match(/\d{1,}/)]);

by

var newCodes = codes.map(function([value]){ return [value.match(/\d{1,}/)]});