Automatically Copy Rows to Another Sheet Based on Cell Value

Question

I'm pretty new to scripts so I apologize for my potential errors in explaining my issue. I'm working on a spreadsheet that will copy a row to another sheet when it meets a certain criteria (or a certain value in a column) and a second function which will move the row to another sheet upon meeting a certain criteria.

Using related questions/answers, I was able to write a script that deletes a row upon meeting criteria "Outbound" in column 4, see myFunction1 below. For myFunction2, I'm trying to instead just copy the row to another sheet when it meets criteria "M" in column 15, but when I enter that value in column 15 it copies the wrong row information.

Hopefully that makes sense! Code below:

function onEdit(event) {
  myFunction1(event);
  myFunction2(event);
}
function myFunction1(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Trailers in Yard" && r.getColumn() == 4 && r.getValue() == "Outbound") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Outbound");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}
function myFunction2(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Trailers in Yard" && r.getColumn() == 15 && r.getValue() == "M") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Moves");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);  
  }
}

Appreciate the assistance!

Welcome to StackOverflow. "it copies the wrong row information." What information does it copy instead? Please share a copy of your spreadsheet (excluding an private or confidential information). — Tedinoz

Cooper Cooper · Accepted Answer · 2019-11-08T02:19:29

Try it this way:

function onEdit(e) {
  //myFunction1(e);
  myFunction2(e);
}
function myFunction2(e){
  //e.source.toast('Flag1');
  var sh=e.range.getSheet();
  if(sh.getName()=="Sheet2" && e.range.columnStart==15 && e.range.getValue()=="M") {
    //e.source.toast('Flag2');
    var targetSheet=e.source.getSheetByName("Sheet1");
    var target=targetSheet.getRange(targetSheet.getLastRow()+1,1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).copyTo(target);
  }
}

You'll have to change the sheet names.

Automatically Copy Rows to Another Sheet Based on Cell Value

1 Answers