Script to move an entire row to from a Google form responses sheet to another sheet based on cell value

user_16814035 0
Viewed 64

I'm trying to use the following code to move an entire row from a Google form responses sheet to another sheet based on a cell value, however for some reason it won't work. If I run it on another sheet, it works perfectly fine. Any recommendations?


 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var s = event.source.getActiveSheet();
 var r = event.source.getActiveRange();

 if(s.getName() == "Jobs" && r.getColumn() == 6 && r.getValue() == "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);
 }
}```
google-sheets forms
1 Answers

When running triggers, the active sheet often defaults to the first sheet of the spreadsheet

Solution:

Instead of defining

 var s = event.source.getActiveSheet();
 var r = event.source.getActiveRange();

use the event object range:

 var r = event.range;
 var s = r.getSheet();

Also, your if condition is if(s.getName() == "Jobs"...). So obviously you will only enter the if code block for the sheet that is actually called "Jobs" and not for the other sheets.

user_115997890
Related