OnEdit not working with pasted values in google sheets

Question

I am using a repurposed script to copy rows to a new spreadsheet. Works fine when I type in the correct cell value ("One Time Service" into column 3). But I have my spreadsheet linked with Zapier and the row is not getting copied across when the value is Zapped/pasted in.

function onChange(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() == "Booking completed" && r.getColumn() == 4 && r.getValue() == "One Time Service") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Copy of One Time");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
  }
}

Ed Nelson Ed Nelson · Accepted Answer · 2016-12-24T14:38:09

Try this query in A1 of "Copy of One Time":

=Query('Booking completed'!A:Z,"Select * where D contains 'One Time Service' and D !=''")

Adjust A:Z to you column needs or

Try putting this formula in A1 of "Copy of One Time".

=iferror(if('Booking completed'!$D1="One Time Service",'Booking completed'!$A1:$Z1,""))

Copy the formula down. Then apply a filter (Data>Filter) to Column D to exclude blank rows.

OnEdit not working with pasted values in google sheets

1 Answers