I have a populated table where I add a prioritisation queue number in the final column. The identification of each row is defined be email OR SMS and the prioritsation queue is labeled as such.

I would like the rows shifted based on the queue number. If 'email - 1' is entered I would like this row automatically shifted to the top (above email - 2 and email - 3). I would also like the same for SMS but those would always sit below the email list.

To show a working example - a table completed as normal and the desired end result directly below (currently manually sorted).

  1. Is it even possible to have this function using column F when it contains both text and numbers?
  2. Is it possible to order by both the importance of number and then email / SMS as a secondary sorting metric, or would Email - 1 followed by SMS - 1 etc have to suffice?

Any help appreciated GREATLY!

enter image description here


1 Answer 1


You are in luck — Email - 1 sorts lexicographically before Email - 2 and SMS - 1, so no custom sort function is required, and you can simply use the Range.sort() method.

Try this simple trigger:

* Simple trigger that runs each time the user hand edits the spreadsheet.
* @param {Object} e The onEdit() event object.
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.');

* Sorts ranges by criteria.
* @param {Object} e The onEdit() event object.
function autoSortRanges_(e) {
  //  - see https://webapps.stackexchange.com/a/159851/269219
  try {
    var sortSpecs = [
        range: e.source.getRange('Sheet1!A2:F'),
        column: 6, // column F = 6
        ascending: true, // true: ascending, false: descending
  } catch (error) {
      'Unknown sheet or incorrect range in sortSpecs in line '
      + String(error.stack).match(/\d+:/).join(', ').slice(0, -1),
  sortSpecs.some(spec => {
    if (e.range.getSheet().getSheetId() !== spec.range.getSheet().getSheetId()
      || e.range.columnStart > spec.column
      || e.range.columnEnd < spec.column
      || e.range.rowStart < spec.range.getRow()
      || e.range.rowEnd > spec.range.getLastRow()) {
      return false;
    spec.range.sort({ column: spec.column, ascending: spec.ascending });
    e.source.toast('Sorted range ' + spec.range.getA1Notation() + '.', 'Autosort');
    return true;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.