Automatically Move Google Form Response by Ticking Check Box

Question

I have 2 google Forms. User fills either of the google forms and it populates 2 "active" tabs within the same sheet to record user responses. I would like to use a script to move rows to a "completed" tab if I check a box in column 1.

This script works with non-form responses; but I'm not having no success with live form responses.

Thanks!

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

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

Answer 1

The "problem" is that a onEdit simple triggers only is activated by user edits done using the Google Sheets UI. Instead of using a simple on edit trigger use a form submit trigger.

Add this function to the script bounded to the spreadsheet

function onFormSubmit(event){
   onEdit(event)
}

Create a form submit trigger on the script bounded to the spreadsheet.

In this case, the form submit function could call the onEdit function because the edit event object and the form submit object have the same properties used on the onEdit function, souce, range, and apparently the checkbox is set on the first column so Range.getColumn() === 1 and Range.getValue() will return the same on both triggers. If the checkbox were on a different column, then some changes should be done or use two different functions. Related

• Auto Move Google Form Response by ticking check box

Reference

• https://developers.google.com/apps-script/guides/triggers