CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
Karim_K
Karim_K

Reputation: 15

Automatic sorting code is google script is not working as intended

I am trying to sort my spreadsheet when any edits occur in column (Col X); based on the new value entered in the (Col X), a simple formula calculates a value in (Col Y).

I am trying to sort the spreadsheet rows based on the output of that formula (Output can be any number between 1 and 5)

The code does not work properly as it seems that it swaps rows randomly, but when I insert numbers manually in (Col Y) instead of the formula, it sorts properly.

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  
  if(editedCell.getColumn() == 8)
  {   
    var range = sheet.getRange("A2:H25");
    range.sort( { column : 1, ascending: true } );
  }

}

Your help is appreciated.

Upvotes: 0

Views: 871

Answers (1)

iansedano
iansedano

Reputation: 6481

Sorting triggered by onEdit

You want to sort the values in a column when they are updated. The sort order is to be determined by the value in another column which is calculated by a formula, based on the changed column.

So if you had a table like this:

A B
1 =RAND()*A1
2 =RAND()*A1
3 =RAND()*A1

When you change a value in A, B would recalculate, and the script would take the recalculated values in B and sort it based on those new values.

Your current Script

function onEdit(event){
  // Gets the active sheet (which may not be the edited sheet!)
  var sheet = event.source.getActiveSheet();
  // Gets the currently selected cell (which may not be the edited cell!)
  var editedCell = sheet.getActiveCell();
  
  // If the currently selected cell is in column 8 (Column H)
  if(editedCell.getColumn() == 8)
  { 
    // Select the range A2:H25 and sort it according to column 1
    var range = sheet.getRange("A2:H25");
    range.sort( { column : 1, ascending: true } );
  }
}

While this may work sometimes, usually onEdit has a small delay, so if you select another cell while it is not running, then the script may not work as expected. You need to get the range from the event object.

Modified Script

I can't see your data so I have made a sample sheet with the data as shown in the example using RAND above.

function onEdit(event){
  // Gets the edited range
  var editedRange= event.range;
  // Gets the edited sheet from the edited range
  var editedSheet= editedRange.getSheet()

  // Checks if the edit appeared in the right sheet and column
  if(editedRange.getColumn() == 1 && editedSheet.getName() == "Sheet1")
  { 
    // Sorts the range
    var range = editedSheet.getRange("A1:B5");
    range.sort( { column : 2, ascending: true } );
  }
}

You will need to change the sheet name check and the column number and the range (depending on your data)

Now if Column A is edited, the range will be sorted according to the value in Column B.

Reference

Upvotes: 1

Related Questions