CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsformula
Ricky Adams
Ricky Adams

Reputation: 175

Google Sheet Copy Row to New Sheet IF Formula Trigger

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);
  }
}

Upvotes: 2

Views: 671

Answers (1)

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17792

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

Upvotes: 2

Related Questions