How to sort by word length, then alphabetically, in Google Sheets?

Question

I have this local JavaScript function to sort the way I'm describing:

const lines = ['foo', 'bing', 'world', 'baz', 'hello', 'bar', 'blank']
  .sort((a, b) => {
    const len = a.length - b.length
    if (len) return len

    return a.localeCompare(b)
  })

It will return:

['bar', 'baz', 'foo', 'bing', 'blank, 'hello', 'world']

How do I forcefully sort the Google Sheet by a specific column (say column C), by length then alphabetically like this? Not a function which I plug into a cell, but like selecting the Sort A->Z option, how can I reorganize the sheet like this?

I have seen this, which says:

=INDEX(SORT(A2:A; LEN(A2:A); 1))

But that will simply render the input in another column, which is not what I want. I want the whole spreadsheet to sort (every row) based on the specific column sort order as described.

Answer 1

Have you run a script before? If not, open App Script in this menu: Paste the code there, save it and run it a first time to accept the permissions. I suggest you follow the instructions from 8:30 in this video: https://youtu.be/8RnOW4z7t2Q After that when you open the spreadsheet again you'll have a personalized menu with that option ;)

 function onOpen() {
 var ui= SpreadsheetApp.getUi()
 ui.createMenu("Sort")
  .addItem("Sort Range", "sortRange")
  .addToUi() }

function sortRange() {

  var range = SpreadsheetApp.getActive().getDataRange()
  var array = range.getValues()
  array.sort((a, b) => { //first you order alphabetically by index 2 (which means ColC, you may change to the column you need)
      var xp = a[2];
      var yp = b[2];
      return xp == "" ? 2: yp == "" ? -2: xp == yp ? 0 :  xp < yp ? -1 : 1; //the first two conditions leave the empty cells at the bottom
    });

  array.sort((a, b) => { //then you order by length. You need to order in the reverse order of the conditions you want, first alphabetically, then length
      var xp = a[2].length;
      var yp = b[2].length;
      return xp == "" ? 2: yp == "" ? -2: xp == yp ? 0 :  xp < yp ? -1 : 1; //las dos primeras condiciones son para que mande los vacíos al final, en otros casos no van a hacer falta
    });
  
  //Logger.log(array)
  range.setValues(array)
}

Answer 2

I think you may import the table as an array in the script (.getValues()), then adapt the formula you have pasted but changing "a.length - b.length" with "a[2]length - b[2].length", because C is the third column of the spreadsheet but index 2 of the array Then you can .setValues(new_array) in the Spreadsheet Tell me if you try it or maybe give some link and we can work it a little further!