Google Sheet Copy Row to New Sheet IF Formula Trigger

Question

I'm trying to use a pretty standard copy row from one sheet to another sheet code when Column B shows 'Start', but Column B has the following formula in it:

=IF(A2 = "Complete","Start","Not Yet")

and this code doesn't seem to recognize the formula changing from 'Not Yet' to Start'. Any help would be greatly appreciated. Thanks!

function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Temp" && r.getColumn() == 1 && r.getValue() == "Complete") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Start");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Henrique G. Abreu Henrique G. Abreu · Accepted Answer · 2018-10-17T18:07:25

The issue is not that it does not "recognize" the formula changing value. If you look at your formula again after it is copied you should notice that the reference changes from A2 to Temp!A2.

moveTo works like if you individually cut-n-paste each cell into their new operation. This results in this "maintaining" of the reference to their original cells.

If this is not desired, maybe you should switch to copyTo. Which might have other side effects in your formulas. But should work fine for this simple one you exemplified.

s.getRange(row, 1, 1, numColumns).copyTo(target); //try copyTo here

Google Sheet Copy Row to New Sheet IF Formula Trigger

1 Answers