Google Sheets Script to Rearrange Rows

Question

Trying to create a Google Sheet that allows selection of row priority with dropdown selection option.

Here's a basic algorithm that I am trying to build out:

1 User changes the priority of the row using the dropdown (Note: That part works fine)

2 The edited row will move up to the position based on the new priority (Note: That part works fine)

3 All other rows will change their numbers based on the edited cell (i.e. Say, the edited cell was originally 13 and changed to 4. I want the old 4 to become 5 and everything from 5 to 12 to increase 1)

The issue I'm having is when the cell is changed to number 4, it goes under the old number 4 - hence a for loop doesn't work.

Anyone done anything similar or have suggestions?

function onEdit(event) {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var editedCell = activeSheet.getActiveCell();


  var columnToSortBy = 1;
  var tableRange = "A2:M22";

  if(editedCell.getColumn() == columnToSortBy){
   var priority =  editedCell.getValue();
   var range = activeSheet.getRange(tableRange);
   var time = new Date();
    time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
   var nextCell = editedCell.offset(0, 13);
    nextCell.setValue(time);


   range.sort({ column: columnToSortBy, ascending: true});

  } 
}

function rearrangeRows() {
  var i;

  var ss = SpreadsheetApp.getActiveSheet();
  var startRow = 1;
  var endRow = ss.getLastRow();
  var getRange = ss.getDataRange();
  var getRow = getRange.getRow();

  var testPriority = 3;

  var cell = ss.getRange("A4");
  var cellValue = cell.getValue();
  if (cellValue > number) {
   Logger.log(cellValue);  
  }
  Logger.log(number);

 // for(i; i > )
}

Answer 1

I think you could put code like what I've written below in the onEdit function, before you sort the spreadsheet. This should increase the value of all the cells with a priority higher than the priority you just set, ignoring the cell you just changed. You may have to adapt it slightly to work also when you decrease the priority of a row, as you'll want to decrease the priority of all the rows in between the row's original priority and its new priority.

var endRow = activeSheet.getLastRow();
for (var i = 1; i <= endRow; i ++) {
    var currentCell = activeSheet.getRange("A" + i);
    if (currentCell.getValue() > priority && i != editedCell.getRow()) {
         currentCell.setValue(currentCell.getValue() + 1)
    }
}