CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
web1connoisseur
web1connoisseur

Reputation: 106

How can I keep the font color of a row the same after sorting?

I am sorting multiple rows. Each row has multiple cells. Cell B always contains a Leetcode question title. I am doing sorting based on how long it took me to finish the question.

Each question is color-coded based on if it is a graph question, array question, linkedlist question, etc.

I now have a working sorting function in my Google App Script, however the colors of specific problems are not staying the same after the sort occurs.

Any advice?

SHEET_NAME = "Problem Tracker";
SORT_DATA_RANGE = "A2:F59";
SORT_ORDER = [
{column: 3, ascending: false},  // 3 = column number, sorting by descending order
{column: 1, ascending: true} // 1 = column number, sort by ascending order 
];

function onEdit(e){
  multiSortColumns();
}

function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
  ss.toast('Sort complete.');

  SpreadsheetApp.flush();
  const values = range.getValues().reduce((o, e, i, a) => {
    o[e[2].toString() == "" ? "blanks": "values"].push(e);
    if (i == a.length - 1) o.result = o.blanks.concat(o.values);
    return o;
  }, {blanks: [], values: []});
  
  range.setValues(values.result);
}

To sort everything and put empty cells on top, I referred to this post:

Google Apps Script to sort empty cells in a particular column to the top

Upvotes: 0

Views: 385

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14502

With all due respect to Tanaike, his excellent solution is not for average coders like me. I almost broke my weak brain when I tried to sort out his reduce magic.

So I'd propose the less glorious path:

SHEET_NAME = "Problem Tracker";
SORT_DATA_RANGE = "A2:F59";
SORT_ORDER = [
{column: 3, ascending: false},  // 3 = column number, sorting by descending order
{column: 1, ascending: true} // 1 = column number, sort by ascending order 
];

function onEdit(e){
  multiSortColumns();
}

function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);

  // get the column with empty cells
  var range_with_empty_cells = ss.getRange("C2:C59");

  // change the empty cells with '_' or with something that will be sorted correctly
  range_with_empty_cells.createTextFinder('^$')
    .useRegularExpression(true).replaceAllWith('_');

  // sort everything as usual
  range.sort(SORT_ORDER);

  // change the '_' back by '' in that column
  range_with_empty_cells.createTextFinder('^_$')
    .useRegularExpression(true).replaceAllWith('');

  ss.toast('Sort complete.');
  return;
}

I haven't tried this on your real date, since you didn't provide the one (and I'm too lazy). But I hope it will work. At the very least this solution is simple enough for anyone to correct and use.

Upvotes: 1

idfurw
idfurw

Reputation: 5852

Do batch operations on font colors also:

getFontColors()

setFontColors(colors)

You may want to replace range.sort(SORT_ORDER) and the values part by range.setValues(o.map(e => e.v))

function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  var v = range.getValues();
  var c = range.getFontColors();
  range.sort(SORT_ORDER);
  ss.toast('Sort complete.');

  SpreadsheetApp.flush();
  const values = range.getValues().reduce((o, e, i, a) => {
    o[e[2].toString() == "" ? "blanks": "values"].push(e);
    if (i == a.length - 1) o.result = o.blanks.concat(o.values);
    return o;
  }, {blanks: [], values: []});
  
  range.setValues(values.result);
  const o = c.map((c, i) => {
    return {'c': c, 'v': v[i]};
  });
  o.sort((a, b) => {
    if (a.v[2] === '' & b.v[2] !== '') { return -1; }
    else if (a.v[2] !== '' & b.v[2] === '') { return 1; }
    else if (a.v[2] > b.v[2]) { return -1; }
    else if (a.v[2] < b.v[2]) { return 1; }
    else { return a.v[0] - b.v[0]; }
  });
  console.log(o);
  const colors = o.map(e => e.c);
  range.setFontColors(colors);
}

Upvotes: 2

Related Questions