Automatic Sorting on Sheets

问题:

I'm trying to figure out how to sort my sheet alphabetically automatically. Whenever I put a new entry under columns A-C, I would like it to automatically sort together with the rest of the data.

I've heard that this must be done using Google apps scripts. Can anyone help me with that?

Thanks in advance!

https://docs.google.com/spreadsheets/d/1XH4mrKa6W4se5WwM6oKqh959gG2kAQVtAmOnml13VoE/edit#gid=0

回答1:

Spreadsheets are easy to sort from a script and a script can easily be triggered by a spreadsheet "event".

onEdit is one of these events that should fit your demand. Doc here and here.

then the sort process is shown in the doc, I reproduce the code below :

var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var range = sheet.getRange("A1:C7");

 // Sorts by the values in the first column (A)
 range.sort(1);

 // Sorts by the values in the second column (B)
 range.sort(2);

 // Sorts descending by column B
 range.sort({column: 2, ascending: false});

 // Sorts descending by column B, then ascending by column A
 // Note the use of an array
 range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);

 // For rows that are sorted in ascending order, the "ascending" parameter is
 // optional, and just an integer with the column can be used instead. Note that
 // in general, keeping the sort specification consistent results in more readable
 // code. We could have expressed the earlier sort as:
 range.sort([{column: 2, ascending: false}, 1]);

 // Alternatively, if we wanted all columns to be in ascending order, we would use
 // the following (this would make column 2 ascending)
 range.sort([2, 1]);
 // ... which is equivalent to
 range.sort([{column: 2, ascending: true}, {column: 1, ascending: true}]);

回答2:

You can also create function and sort by column name. Enter the column name as a string when invoking the function.

 var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange("A1:F30");
var values = range.getValues();

var sortByCol = function Sort(sort) {
      var sort = sort.toLowerCase();
      for (var i=1; i <= range.getNumColumns(); i++) {
        if (String(values[0][i]).toLowerCase() === sort) {
          range.sort({column: i+1, ascending: false});
           break;
        }
      }  
}