Google-sheets – How to sort by merged cells in Google Sheet

google sheetsgoogle-apps-scriptsorting

I have been using this script for sorting and so I know it works. Now I'm trying to use it on a new Google Sheet project. Except that the column I want to sort the entire sheet by isn't just one column but a merge, which I derived by merging columns 3 to 6. How do I modify my script to sort by the derived column? Here is my script:

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 3; // doing 3:6 doesn't work
  var tableRange = "A11:R82";

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy } );
  }
}

After some trials and error, I did the following, but it's still not working:

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = [3,4,5,6];
  var tableRange = "A11:R82";

  if(columnToSortBy.includes(editedCell.getColumn()) ){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy[0] }, { column : columnToSortBy[1] },{ column : columnToSortBy[2] },{ column : columnToSortBy[3] } );
  }
}

Best Answer

Normally it's just:

range.sort([{column: 3}, {column: 4}, {column: 5}, {column: 6}]);

So based on this wouldn't it be column: 3[0] , column 3[1] etc ? I dont know, I'm just kibitzing. Give it a go!

Related Solutions

Google-sheets – How to apply a script to only one specific sheet within a spreadsheet

There are a couple of ways to do that. First, you need to declare the spreadsheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();

Secondly, declare the first sheet like this:

var sh0 = ss.getSheets()[0];

or like this:

var sh0 = ss.getSheetByName("Robin");

Then set the active cell and retrieve the column index:

var editedCell = sh0.getActiveRange().getColumnIndex();

And do the logic for the sort:

var columnToSortBy = 1;
var tableRange = "a2:I30";

if(editedCell == columnToSortBy){   
  var range = sheet.getRange(tableRange);
  range.sort( { column : columnToSortBy } );
}

Complete code, a bit shortened:

function onEdit(){
  var sh0 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var editedCell = sh0.getActiveRange().getColumnIndex();

  if(editedCell == 1) { 
    var range = sh0.getRange("A2:I30");
    range.sort({column: 1});
  }
}

Google-sheets – Auto sort data but by sheet not range

The sort method belongs to the class Range, not to the class Sheet. Simply put, it always operates on a range. If you want that range to include all the data in a sheet, use the method getDataRange of the class Sheet.

var range = sh0.getDataRange();
range.sort({column: 2, ascending: false});

For completeness, I present a customizable script for automatic sorting.

  function onEdit() {
    var sheetToSort = "Sheet Name"; // enter name of sheet 
    var columnEdited = 1;           // sort when this column is edited
    var columnSort = 2;             // sort by this column
    var sortAscending = false;      // true or false here

    var sheet = SpreadsheetApp.getActiveSheet();
    var editedCell = sheet.getActiveRange().getColumnIndex();
    if (sheet.getSheetName() == sheetToSort && editedCell == columnEdited) { 
      var range = sheet.getDataRange();
      range.sort({column: columnSort, ascending: sortAscending});
    }
  }

Related Topic