CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
7H3LaughingMan
7H3LaughingMan

Reputation: 372

Custom sort rows in Google Sheets while preserving formatting

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.

Upvotes: 2

Views: 2539

Answers (1)

user6655984
user6655984

Reputation:

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

Upvotes: 2

Related Questions