Custom sort rows in Google Sheets while preserving formatting

Question

I am trying to figure out a way to sort data in a Google Spreadsheet in a specific order besides ascending/descending. I can do this by getting a 2D array using Range.getValues(), sorting it, and than using Range.setValues().

But the problem with this is that it is just copying the data, sorting it, and pasting the data over the old data. So, if I have A1 highlighted red and the data is moved to B2 by sorting the range values than B2 wouldn't be highlighted red and A1 with whatever is put in there instead will be highlighted red.

Is there any way to actually sort a range and move the rows around with the formatting and everything still intact.

Create a sort object and pass it to the range. Your research ( stackoverflow.com/help/how-to-ask ) should have led you to developers.google.com/apps-script/reference/spreadsheet/… — tehhowch
@tehhowch Yeah, the only examples that provides are on how to sort columns in ascending or descending order. As I mentioned I don't want that. — 7H3LaughingMan
Next time be more specific and detailed in your question - sorting on multiple columns (e.g. via sortObject) is not a simple ascending/descending sort (admittedly, it is limited to be a combination), and you did not mention range.sort in your question. I think your only recourse then, to sort on arbitrary fields of the data such as length is to somehow serialize your desired dimension in a manner that it can be sorted ascending or descending, as sftp has indicated. — tehhowch

Unknown Unknown · Accepted Answer · 2018-03-27T02:38:22

A way to force an arbitrary sort with Range.sort is to append a temporary column to the range and use it for sorting. A column can be inserted on the fly and then removed, to avoid corrupting the data placed next to the range that is being sorted.

Here is an example that sorts A2:A7 by the length of content.

function sortRangeByLength() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:A7");
  sheet.insertColumnAfter(range.getLastColumn());
  var keys = range.getValues().map(function(row) {
    return [row[0].length];
  });
  range.offset(0, range.getNumColumns(), range.getNumRows(), 1).setValues(keys);
  var extendedRange = range.offset(0, 0, range.getHeight(), range.getWidth() + 1);
  extendedRange.sort({"column": extendedRange.getWidth(), "ascending": true});
  range.offset(0, 1).clear();
  sheet.deleteColumn(extendedRange.getLastColumn());  
}

The range extendedRange is range with additional column, used for sorting and then removed. Calling clear on the column which is to be removed may look redundant but it prevents unneeded formatting from spilling over to the column to the right (a column inserted to the right of a range inherits its formatting).

For some sorts there may be no obvious key, just a compare function. In that case, a key can be computed from the compare function as follows:

function sortRangeByLength() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A2:A7");
  sheet.insertColumnAfter(range.getLastColumn());

  // begin computing the keys
  var indexed = range.getValues().map(function(row, i) {
    return {"index": i, "content": row};
  });
  indexed.sort(function(a, b) {
    return (a.content[0].length < b.content[0].length ? -1 : (a.content[0].length > b.content[0].length ? 1 : 0));
  });
  var keys = Array(indexed.length);
  for (i in indexed) {
    keys[indexed[i].index] = [i];
  }

  // proceed as before
  range.offset(0, range.getNumColumns(), range.getNumRows(), 1).setValues(keys);
  var extendedRange = range.offset(0, 0, range.getHeight(), range.getWidth() + 1);
  extendedRange.sort({"column": extendedRange.getWidth(), "ascending": true});
  range.offset(0, 1).clear();
  sheet.deleteColumn(extendedRange.getLastColumn());  
}

The approach is standard: created an "indexed" array, which contains the data and the index of each row. Sort this array according to your algorithm. The resulting permutation of indexes shows what row went where. It needs to be inverted to become the "sort by this ascending" key column. This inversion is keys[indexed[i].index] = [i];

Custom sort rows in Google Sheets while preserving formatting

1 Answers