GAS – Sort a Single Column

You can do this with a flick of the wrist when working directly on a sheet, but to use a script to do the same thing is slightly more complicated! Google have decided in their infinite wisdom that if you want to sort a range, a column, you will want to sort the data around it as well. This is not always the case. Very often I have a sheet with columns full of unrelated lists: names, locations, dates, etc., and these often need to be sorted alphabetically, by themselves, in order for functions to work further down the line: match / vlookup / filter / index and so on. A few years ago, the clever people on the Google Docs forum came up with a solution, they said it only worked in G Suite for Work or Education, so you mileage may vary when running this in a standalone google sheet?

In essence, you follow the same routine as you would for a range sort in GAS, but you then need to get the values of the range, count them using some javascript, then get the exact range using that number, then get the values again. At this point the second function will sort the values, then write them back to the range.

To use this function in your projects, replace the spreadsheet name in line 5, and adjust the single column range letters and numbers in line 6. For some reason, in my usage, the count of the filter.length is always one short, so I have had to add a 1 ?

Again, not all my own work, so credits to the powerusers on the Google Groups for GAS.

Leave a Reply

Your email address will not be published. Required fields are marked *