Let's say I want Google Sheets to automagically sort some data, for example column C
.
How do I do that?
I know that I can manually sort data by right-clicking and selecting Sort data, but this is not what I'm looking for.
Let's say I want Google Sheets to automagically sort some data, for example column C
.
How do I do that?
I know that I can manually sort data by right-clicking and selecting Sort data, but this is not what I'm looking for.
You can use the sort()
function for that, but you have to have your data in one place, and an automatically sorted copy of that data in another place.
For example, say I have Sheet1 with my data:
| A | B | C
=====================
1 | This | this | 2
2 | Is | is | 1
3 | Test | test | 3
Then in Sheet2, cell A1, I would put this function:
=sort(Sheet1!A:C, 3, TRUE)
This would show my data, but sorted by column C (the third column), ascending.
| A | B | C
=====================
1 | Is | is | 1
2 | This | this | 2
3 | Test | test | 3
;
e.g. = sort(Sheet1!A:C; 3; TRUE)
– user17634
Feb 25 '12 at 12:20
It is also possible to use Google Apps Scripts to achieve automatic in-place sorting of the data.
This may be more difficult to achieve and more error-prone (I'd still go for William Jackson's solution, +1 BTW), but I thought it was interesting enough to show.
I have a sheet that looks like this:
I added a new script, using these steps:
in the empty code window that appears, paste the following code, which will be run automatically whenever a cell is edited:
function onEdit(event){
var sheet = event.source.getActiveSheet();
var editedCell = sheet.getActiveCell();
var columnToSortBy = 4;
var tableRange = "B3:E9";
if(editedCell.getColumn() == columnToSortBy){
var range = sheet.getRange(tableRange);
range.sort( { column : columnToSortBy } );
}
}
go back to the sheet and start playing around with the values to see the table sort automatically each time
Note:
In the above script,
4
represents the index of column D (the Value
column - the on that's going to be sorted)"B3:E9"
represents the table range (excluding the header row)Your table will most likely differ from mine, so these values should be adjusted accordingly.
null
for the event
parameter, which is otherwise properly populated by the Google Spreadsheet infrastructure when a real event is triggered.
– Cristian Lupascu
May 3 '12 at 15:52
range.sort
line like this: range.sort( { column : columnToSortBy, ascending: false } );
. For more sorting options see developers.google.com/apps-script/class_range#sort
– Cristian Lupascu
May 3 '12 at 15:54
Here's a generic script that will autosort based on the 1st column, and assumes a Header row.
To create a script:
In the empty code window, paste the following code, which will run automatically whenever a cell is edited:
// LinkBack to this script:
// http://webapps.stackexchange.com/questions/7211/how-can-i-make-some-data-on-a-google-spreadsheet-auto-sorting/43036#43036
/**
* 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 } );
}
}
Another option without a script is:
=QUERY(A1:C3,"SELECT * ORDER BY C")
The range is restricted (A1:C3) because where the order is ascending blank entries will appear first.
SELECT * WHERE C <> '' ORDER BY C
to ignore blank entries, then you can use a large enough range to include all current and future rows.
– Guss
Dec 27 '17 at 11:39
I wanted to sort by a dropdown menu column and then by date.
To do so, modify the "range.sort" line of either of Cristian or geekspotz's code snippets like so:
// Sorts descending by edited column, then ascending by column 1
// Note the use of an array
range.sort([{column: columnToSortBy, ascending: false}, {column: 1, ascending: true}]);
The difference is adding the straight brackets around your whole statement (array) and separating the sorts by commas.
Sort code modification pulled from Serge's Stack Overflow answer here: Automatic Sorting On Sheets
Google Sheets has Filters for this very reason and doesn't require you to make a new sheet in your document.
Documentation: https://support.google.com/docs/answer/3540681