Google Apps Script successfully triggers On Form Submit, but function will not execute

I’m trying to copy a row from the linked form responses sheet “main” to another sheet “sub” if a value in the form response matches a particular criteria, Column 4 = “Subset”

I have onFormSubmit set up as a trigger with Deployment – Head, Source – Spreadsheet, Event – On form submit. When I submit the form, the trigger for onFormSubmit logs the execution as Completed but the row does not copy to the “sub” sheet.

The getLast function allows me to move the row to the next available position in the “sub” sheet starting in the second column because I have checkboxes in the first column all the way down the sheet.

The code works as is when using the onEdit function and manually entering “Subset” into column 4. I can’t seem to figure out why nothing happens with onFormSubmit even though the trigger is successfully executed. Please help!

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

 if(s.getName() == "main" && r.getColumn() == 4 && r.getValue() == "Subset") {
  var row = r.getRow();
  var numColumns = s.getLastColumn();
  var targetSheet = ss.getSheetByName("sub");
  var column = targetSheet.getRange("B:B").getValues();
  var lastRow = getLast(column);
  var target = targetSheet.getRange(lastRow + 1, 2);
  s.getRange(row, 1, 1, numColumns).copyTo(target);
 }
}

function getLast(range){
 var rowNum = 0;
 var blank = false;
 for(var row = 0; row < range.length; row++){

  if(range[row][0] === "" && !blank){
   rowNum = row;
   blank = true;
  }else if(range[row][0] !== ""){
   blank = false;
  }
 }
 return rowNum;
}

Answer

I thought that from your question, when your script is run by OnSubmit trigger, the if statement of if(s.getName() == "main" && r.getColumn() == 4 && r.getValue() == "Subset") {} might be always false, because in this case, the range is not the column “D”. By this, your script doesn’t work. I thought that this might be the reason of your issue.

When the OnSubmit trigger is fired, in order to run the script in the if statement, how about the following modification?

Modified script:

function onFormSubmit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var srcRange = sheet.getRange(range.rowStart, 1, 1, sheet.getLastColumn());
  if (srcRange.getValues()[0][3] == "Subset") { // srcRange.getValues()[0][3] means the column "D".
    var targetSheet = e.source.getSheetByName("sub");
    var column = targetSheet.getRange("B:B").getValues();
    var lastRow = getLast(column);
    var target = targetSheet.getRange(lastRow + 1, 2);
    srcRange.copyTo(target);
  }
}