add auto sort to google spreadsheet

Google spreadsheet is a powerful tool free for us, especially when used with google form where the spreadsheet is served as a database.

Google also provide sort function for each column. However it is not that easy to get it auto sortable by column.

I found a app script which seems to work when you edit directly in the sheet.

1. app script way (less convenient)

 * Automatically sorts the 1st column (not the header row) Ascending.
function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:T99"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );

But if it is used as a backend for the form, when a form response is sent, it does not get sorted automatically.


2. sort function way(A better way for me)

Let say you have your form submissions in a sheet called FormInput. Your data is filled from column A to C. Column A is First Name, Column B is Last name.

Because when a user submit the form, a new entry is entered, it will ruin your existing formula on FormInput. So we need a new sheet called Sorted. (create a new sheet)

In cell A2 of sheet Sorted, you enter this function:

=SORT(‘FormInput’!A2:C, 2, 1)

Which is:
‘FormInput’!A2:C – where your form input go in…, (plus a single quote pair ‘…’). The data is filled from column A to column C, from row 2 to infinitive.
2 – column 2 = column B
1 – sort ascending


What’s more, you can hide the original sheet and just show the sorted sheet.

The complete documentation for sort function


Sorts the rows of a given array or range by the values in one or more columns.

Sample Usage

SORT(A2:B26, 1, TRUE)

SORT({1, 2; 3, 4; 5, 6}, 2, FALSE)

SORT(A2:B26, C2:C26, TRUE)


SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])

  • range – The data to be sorted.
  • sort_column – The index of the column in range or a range outside of rangecontaining the values by which to sort.
    • A range specified as a sort_column must be a single column with the same number of rows as range.
  • is_ascending – TRUE or FALSE indicating whether to sort sort_column in ascending order. FALSE sorts in descending order.
  • sort_column2, is_ascending2 ...
    • [ OPTIONAL ] – Additional columns and sort order flags beyond the first, in order of precedence.


  • range is sorted only by the specified columns, other columns are returned in the order they originally appear.


One comment

  1. Craig McMinn · June 26

    This is just what I was looking for except that I need to be able to display the results in ascending order, but ignoring any zero values. Does anyone know how I can do this?

    Position Player 1 Round 1 Round 2 Total
    1 PLayer 1 59 62 121
    2 PLayer 2 67 62 129
    3 PLayer 3 67 72 139
    4 PLayer 4 73 73 146
    5 PLayer 12 0
    6 PLayer 13 0

    Thanks in Advance…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s