How to move a row to bottom of sheet (GOOGLE SHEET) when “Status” is changed to a COMPLETED from OPEN

Question

How to move a row to bottom of same sheet when “Status” is changed to a complete.

I'm trying to figure out how to get a row moved to the bottom once the Status B column value is changed from OPEN to CLOSED.

Sheet name is: Sheet1, Colume in which status drop down menu is at Column B, Drop down menu contain: OPEN, HOLD, PENDING, CLOSED

The trigger is need when the status changed to CLOSED

I found this code but this isnt working:

function onEdit(e) {

  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();
  if(as.getName() == "Sheet1" && col == 2  && row > 1 && !as.getRange(row,col).getValue()=='') {  
    const row_new = as.getRange(row,1,1,col);
    row_new.copyTo(as.getRange(as.getLastRow()+1,1,1,col));
    as.deleteRow(row);
  }
} 

Answer 1

If I understand correctly, you want to move the entire row if the CLOSED status is selected in column B. However, there is no check for the CLOSED status in the IF function. In addition, not the entire row is copied, but only 2 cells:

const row_new = as.getRange(row, 1, 1, col)

If you change the code like this, then everything works well:

Update

You have added an additional condition about HOLD in the comments. Here's the updated code that handles this condition as well:

function onEdit(e) {
  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();
  const lc = as.getLastColumn();
  if(as.getName() == "Sheet" && col == 2  && row > 1 && as.getRange(row,col).getValue() == 'CLOSED') {
    const row_new = as.getRange(row, 1, 1, lc);
    row_new.copyTo(as.getRange(as.getLastRow() + 1, 1, 1, lc));
    as.deleteRow(row);
  } else if(as.getName() == "Sheet" && col == 2  && row > 1 && as.getRange(row,col).getValue() == 'HOLD'){
    as.insertRowAfter(1);
    const row_new = as.getRange(row + 1, 1, 1, lc);
    row_new.copyTo(as.getRange(2, 1, 1, lc));
    as.deleteRow(row + 1);
  }
}