How to sort rows based on background color in google sheets

Question

I have a google sheets file. In this file I set the background color for every row which meets the following criteria:

ie: if column 8 is empty, set the entire row background color to red.

I have been able to achieve this. But now what I want to do is to push all the red colored rows down after the none colored rows/rows with column 8 full.

Can you please assist me with this? I have the following code but am totally lost as far as how to do this sorting thing.

Thanks.

function SelectRecords() {
   const ss=SpreadsheetApp.getActive();
   const sh=ss.getSheets()[0];
   const rg=sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn());
   let vs=rg.getValues()
   var rows=vs.map(function(r,i){
   if(r[8]=='') {
      sh.getRange(i+3,1,1,sh.getLastColumn()).setBackground('#ff0000'); 
      return r;
   }
   }).filter(function(e){return e;});
 }

 SHEET_NAME = "CURRENT MONTH";
 SORT_DATA_RANGE = "A4:J999";
 var SORT_ORDER = [{column: 8, ascending: false}];

function sorting(){
   var ss = SpreadsheetApp.getActiveSpreadsheet(); 
   var sheet = ss.getSheetByName(SHEET_NAME);
   var SORT_DATA_RANGE = "A4:I"; 

   var range = sheet.getRange(SORT_DATA_RANGE);

   range.sort(SORT_ORDER);
   ss.toast('Sort complete.');
} 

Answer 1

I believe that you already asked this question earlier as a part of another question.

Read the comments they tell you pretty much what each step does.

In summary I took one column of the background color and concatenated it with the data array so that I could sort both of them together so that once they were sorted I would know the final color of each row. I flattened the color column, past it through a set to get a unique color array and used the colors.indexOf(the last column of the combined array) to sort the data array in the custom sort function. The final two steps involved separating the combined array into it’s constituents to use setValues() and setBackgrounds() to display the final result.

 function sortOnColors() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheets()[0];
  const rg=sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn());
  let vA=rg.getValues();
  let cA=sh.getRange(3,1,sh.getLastRow()-2,1).getBackgrounds();
  let vC=vA.map(function(r,i){return r.concat(cA[i][0]);});//concat of vA andd vC adding the color of the row as a value to each row
  let S=new Set(cA.map(function(r){return r[0]}));
  let colors=[...S];//this creates an array of unique colors
  vC.sort(function(a,b){return colors.indexOf(b[b.length-1])-colors.indexOf(a[a.length-1])});//this sorts the array on the colors index of the value in the last column which is the background color of the row and keeps the row and color tied together
  vD=vC.map(function(r,i){return r.slice(0,-1);});//this removes the last column
  vE=vC.map(function(r,i){let t=r.slice(0,-1);return t.fill(r[r.length-1],0);});//this create a 2d array of background colors for each row based upon the color value in the last column which is removed at the same time
  sh.getRange(3,1,vD.length,vD[0].length).setValues(vD);
  sh.getRange(3,1,vE.length,vE[0].length).setBackgrounds(vE);
}

Before:

After: