1

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.

1

2 Answers 2

1

Have you run a script before? If not, open App Script in this menu: App Script 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)
}
9
  • Oh cool, thanks! Where do I put such a thing, and how do I use it (like how do I regularly sort again and again with it)?
    – Lance
    Commented Oct 26, 2022 at 14:56
  • How often do you need it?, every time you open?, do you want a menu to click when you need it?, or you need it each and every time you edit a value?
    – Martín
    Commented Oct 26, 2022 at 14:59
  • I want to run it occasionally after I add a few new values, maybe a menu or button would be nice.
    – Lance
    Commented Oct 27, 2022 at 1:18
  • I've edited the answer so you can have a personalized menu. Let me know if you can do it 😉
    – Martín
    Commented Oct 27, 2022 at 1:49
  • Hey @Lance , yesterday I saw a new question of yours and remembered this one. Could you make it work??
    – Martín
    Commented Nov 4, 2022 at 11:08
0

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!

2
  • How would I trigger it, do you have a working example perhaps :D
    – Lance
    Commented Oct 26, 2022 at 12:17
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Oct 26, 2022 at 16:58

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.