Using onFormSubmit Trigger With Multiple Forms For One Sheet

Question

I'm attempting to develop a function for a Google Sheet with multiple forms; each form has an individual sheet in the spreadsheet book, and I am using a separate function to add data to the master sheet. The code is container-bound to the sheet file.

In particular, I am attempting to use a single onFormSubmit trigger, and then identifying the form, and therefore the appropriate function and variable passes run by onFormSubmit, by the title of the first question in each form submission that doesn't include the time-stamp. The function, as currently coded, is as follows;

//Selects onFormSubmit action based on contents of form submission.  Regex compliance is enforced by forms.
function onFormSubmit(e) {
  Logger.log(e);
  var responses = e.response.getItemResponses();
  var keyResponse = responses[1].getItem().getTitle();
  Logger.log(keyResponse);
  if (keyResponse == "CUNY ID") {
    var cunyID = responses[1].getResponse();
    cunyIDQuery(cunyID);
  }
  else if (keyResponse == "First Name") {
    var firstName = responses[1].getResponse();
    var lastName = responses[2].getResponse();
    var gpa = responses[3].getResponse();
    infoSubmit(firstName, lastName, gpa);
  }
  else if (keyResponsew == "Q1") {
    var q1 = responses[1].getResponse();
    var q2 = responses[2].getResponse();
    var q3 = responses[3].getResponse();
    gradeSubmit(q1, q2, q3);
  }
  else {
    wrongFormError();
  }
}

Unfortunately, since this is container-bound, and I'm testing as an add-on, the logger functions have not been very helpful, but I do believe that, back when I was using e.namedValues instead of e.response.getItemResponses, I was able to determine that, at the very least, the form submission seems to be passed into the function correctly. If anyone might be able to figure this out, that would be great.

Answer 1

Apps Script has two types of form submit triggers

As specified in the documentation:

An installable form submit trigger runs when a user responds to a form. There are two versions of the form-submit trigger, one for Google Forms itself and one for Sheets if the form submits to a spreadsheet.

• Both triggers have the event type On form Submit, but depending on either your script is bound to the form itself or the destination spreadsheet, the selectable event source will be either From form or From spreadsheet
• This difference becomes very important when you want to use event objects.
• You can see that in spite of the same name the On form Submit trigger will have different event objects depending on either your script is bound to the form or to the spreadsheet.

• For event source From spreadsheet the available event objects are
  • authMode
  • namedValues
  • range
  • triggerUid
  • values
• For event source From form the available event objects are
  • authMode
  • response
  • source
  • triggerUid

Conclusion

• If you want to bind your script to a spreadsheet, so you can use it for more than one form, you cannot use the event object e.response, because this is not a valid event object for From spreadsheet.
• You will need to use values or namedValues instead - depending on your preference, which will give you the same information just in a different way.
• If the different forms have different questions, than namedValues does not make so much sense, so use e.values instead and skip e.values[0] hich will be the timestamp.

Answer 2

Alternative to use one script to handle multiple forms

The Google Sheet formSubmit event fires for any form that is connected to the sheet. I managed to use the range of the formResponse results to determine which sheet it came from to know which form it came from

function myFunction(form) {
  const formSheetName = form.range.getSheet().getName();

Credit: IMTheNachoMan comment