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

SORT

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)

Syntax

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.

Notes

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s