Google-sheets – Auto sort a sheet so that entries remain in their categories

google sheetssorting

So I want to sort my Spreadsheet on Google but it remain in the Categories that it's laid out. I can get to it sort double columns but the categories get messed up as they move away from where they should be. I'm constantly adding to this document and want the additions to auto sort into their needed locations.

This the document, so you can get the idea of what I'm looking for.

Best Answer

The following script sorts parts of the active sheet by the first column. The parts are separated by blank entries in the first column. So, for example 

+-------+
| Title |
+-------+
| sss   |
| bbb   |
|       |
| eee   |
| wwww  |
| ccc   |
+-------+

becomes 

+-------+
| Title |
+-------+
| bbb   |
| sss   |
|       |
| ccc   |
| eee   |
| wwww  |
+-------+

See also the comments included in the code.

It only does the "sorting within each category" part, not "analyze input and move it to the correct category" part; I felt the latter was under-specified and would warrant a separate question. 

function sortRanges() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var width = range.getWidth();  
  var values = range.getValues();
  var j = 1;                       // assuming the header row; otherwise j=0
  var len = values.length;          
  for (var i = 1; i<len; i++) {    // assuming the header row; otherwise i=0
    if (!values[i][0]) {
      if (i>j+1) {
        range.offset(j, 0, i-j, width).sort(1);   // (1) is the column to sort by
      }
      j = i+1;
    }
  }
  if (j<len-1) {
    range.offset(j, 0, len-j, width).sort(1);     // (1) is the column to sort by
  }
}
Related Topic