Auto sorting records in spreadsheet as soon as value is entered

Question

I want to auto sort values in Google Sheets as soon as I enter value in a cell. Below is an example:

| S. No. | Task   | Value |
| 1      | Task 1 | $$    |
| 2      | Task 2 | $$$   |
| 3      | Task 3 | $$$$  |
|        |        |       |

In the above table, as soon as I enter Value field for Task 3, I want it to go to top and the first one should come to the end. I don't want to achieve this manually by sorting every time.

Answer 1

Similar to Chris Hick's suggestion, you might enter your data in any order and have a copy sorted to suit. Since your example appears well ordered (ascending) I have assumed you would like it ordered descending (by S. No.) and that that is in A1:

=query(A:C,"Select * where A is not NULL order by A desc")  

Add a couple of entries9 in A5, 8 in A6 and the resulting list will be ordered 9,8,3,2,1.

Answer 2

You can use a script to automatically sort your table. To do this, go to tools > script editor. This will open a new window.

Delete the code that you see and paste the below in:

function onEdit(event) {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cellColumn = sh.getActiveRange().getColumnIndex();
  var currentSheet = sh.getName();
  if ((cellColumn == 3) && currentSheet == "enter sheet name here") { 
    var range = sh.getRange("A2:C");
    range.sort({column:2, ascending:false});
  }
}

You will need to change the "enter sheet name here" to the name of the sheet that you want to be sorted. Make sure to leave the quote marks in.

If you want to change the sort so that it is ascending, change the last line from

ascending:false

to

ascending:true

If you want to change the range of the data that is sorted, then you can do that on the row above. At the moment it is set to sort the range A2:C.