Sorting, also with Script
CONTENTS
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.
Sort by Surname
Nice little equation that I can’t take credit for, can’t remember who provided it in the first place.
Say you have a list of peoples names in the format:
Bob Jones
John Smith
Ann Ableson
Greg Wilson
Steve Dennis
Phil Richards
Google Sheets by default will sort then by the first letter on the left:
Ann Ableson
Bob Jones
Greg Wilson
John Smith
Phil Richards
Steve Dennis
But very often we want to sort by the surname. Now it is easy enough to apply split() and do it that way but this equation will do it for you, producing the results in adjacent cells (assumes your list is in A1:A6):
=ArrayFormula(SORT( A1:A6 , REGEXREPLACE(A1:A6 , "(.* )(.*$)" , "$2") , TRUE))
The equation gets a little unhappy if you have empty rows in your range, and puts them at the top, which if you have a long list of names, might make them disappear off the bottom of the screen. To account for this, you can do a count of the items in the list, then substitute this number in your equation (assumes count formula is in C1):
=ArrayFormula(SORT( indirect("A1:A"&C1) , REGEXREPLACE( indirect("A1:A"&C1) , "(.* )(.*$)" , "$2") , TRUE))
This will now list as follows:
Ann Ableson
Steve Dennis
Bob Jones
Phil Richards
John Smith
Greg Wilson