Auto Sort Google Sheet using onEdit Function and prioritising certain cell values

Question

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!

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.');
  }
  autoSortRanges_(e);
}


/**
* 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) {
    e.source.toast(
      'Unknown sheet or incorrect range in sortSpecs in line '
      + String(error.stack).match(/\d+:/).join(', ').slice(0, -1),
      'Autosort',
      30
    );
    return;
  }
  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;
  });
}