CODEWITHSUNDEEP
sortinggoogle-sheetsgoogle-sheets-api
Chetan Sachdev
Chetan Sachdev

Reputation: 718

Auto sorting records in spreadsheet as soon as value is entered

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.

Upvotes: 6

Views: 691

Answers (2)

Ben Currer
Ben Currer

Reputation: 305

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.

Upvotes: 1

pnuts
pnuts

Reputation: 59485

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.

Upvotes: 1

Related Questions