Google Sheets Move on Edit if - Move on Edit if Not

Question

I have 2 google sheets with column E being a status drop down with multiple status entries. My sheets are called "Sheet1" and "Completed". Upon selecting "complete" in the status dropdown, I want the row to be moved to the "Completed" sheet. After being moved and making the "Completed" sheet active I'd like for the row to be moved back to "Sheet1" if I select anything other than "complete" in the status drop down.

Here is what I have so far. It will move my row from sheet1 to completed but not the other way. Any help is appreciated:

    function onEdit(event) {
      // assumes source data in sheet named Sheet1
      // target sheet of move to named Completed
      // test column with "10 Complete" is col 5 or E
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = event.source.getActiveSheet();
      var r = event.source.getActiveRange();
      if(s.getName() == "Sheet1" && r.getColumn() == 5 && 
    r.getValue() == "10 Complete") {
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        var targetSheet = ss.getSheetByName("Completed");
        var target = 
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
        s.getRange(row, 1, 1, numColumns).moveTo(target);
        s.deleteRow(row);
      if(s.getName() == "Completed" && r.getColumn() == 5 
    && r.getValue() != "10 Complete") {
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        var targetSheet = ss.getSheetByName("Sheet1");
        var target = 
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
        s.getRange(row, 1, 1, numColumns).moveTo(target);
        s.deleteRow(row);
        }
    }
    }

Ron M Ron M · Accepted Answer · 2021-02-15T22:48:02

The reason why your code is not working is because you put your 2nd IF statement inside the 1st IF statement. The 2nd IF statement will never be executed.

Your current code:

function onEdit(event){
  
  if(s.getName() == "Sheet1" && r.getColumn() == 5 && 
    r.getValue() == "10 Complete") {

    .....

    if(s.getName() == "Completed" && r.getColumn() == 5 
    && r.getValue() != "10 Complete") {
    ......
    }
  }
}

SOLUTION:

The 2 IF statements should be on the same level.

Working Code:

function onEdit(event) {
  // assumes source data in sheet named Sheet1
  // target sheet of move to named Completed
  // test column with "10 Complete" is col 5 or E
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if(s.getName() == "Sheet1" && r.getColumn() == 5 && 
    r.getValue() == "10 Complete") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Completed");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
    
  if(s.getName() == "Completed" && r.getColumn() == 5 
  && r.getValue() != "10 Complete") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Sheet1");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

}

Google Sheets Move on Edit if - Move on Edit if Not

1 Answers

SOLUTION: