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