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

user_18176107 0
Viewed 290

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;
}
google-forms google-apps-script google-sheets javascript
2 Answers

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

Thanks Tanaike, I was just figuring this out as you posted. The event object passed by the On Form Submit trigger does not values for .getActiveSheet() and .getActiveRange() because there is not an active user.

My solution isn't as elegant as yours but it did the trick. I appreciate how you bypass SpreadsheetApp altogether by getting the sheet directly from the event object.

function onFormSubmit(e) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName("main");
   var response = e.values;
  
   if(response[3] == "Subset") {
     var row = sheet.getLastRow();
     var numColumns = sheet.getLastColumn();
     var targetSheet = ss.getSheetByName("sub");
     var column = targetSheet.getRange("B:B").getValues();
     var lastRow = getLast(column);
     var target = targetSheet.getRange(lastRow + 1, 2);
     sheet.getRange(row, 1, 1, numColumns).copyTo(target);
   }
}

On further inspection and inspired by your solution, I combined your approach with my use of e.values in the if statement to land on a lean and functional approach. Thanks a lot, Tanaike!

function onFormSubmit(e) {
  var response = e.range;
  
  if(response.getValues()[0][3] == "Subset") {
    var targetSheet = e.source.getSheetByName("sub");
    var col = targetSheet.getRange("B:B").getValues();
    var lastRow = getLast(col);
    var target = targetSheet.getRange(lastRow + 1, 2);
    response.copyTo(target);
  }
}
user_181761070
Related