Trigger different Google Script functions after di

Question

I currently have five different forms connected to the same Google Sheet. My end goal is that after someone submits a response to any of the forms, they will receive an email with their responses.

I know that Google Forms has that ability through response receipts, but it's not optional. If I have that feature on, they cannot submit the form without filling in their email (even if they don't want the receipt).

The code I have at the moment is:

function ActivityFunction() {
  var spreadsheet = SpreadsheetApp.openById("FORM ID");
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[4]);
  var sheet = spreadsheet.getActiveSheet();

  var lastRow = sheet.getLastRow();

  //Email Info

  //Name
  var range = sheet.getRange(lastRow,2,1,1);
  var Name = range.getValues();

  //Email Address
  var range = sheet.getRange(lastRow,7,1,1);
  var Email = range.getValues();

  //Date
  var range = sheet.getRange(lastRow,3,1,1);
  var Day = range.getValues();

  //Time
  var range = sheet.getRange(lastRow,4,1,1);
  var ExerciseTime = range.getValues();

  //Activity
  var range = sheet.getRange(lastRow,5,1,1);
  var Activity = range.getValues();

  //Intensity
  var range = sheet.getRange(lastRow,6,1,1);
  var Intensity = range.getValues();

  var msg = "Hello "+Name+"!
  Thank you for filling out today's activity form. Here are your responses.
  Name: "+Name+"\n\Date: "+Day+"\n\Time: "+ExerciseTime+"\n\Activity: "+Activity+"\n\Intensity: "+Intensity+"\n\n\Have a great day!"

  MailApp.sendEmail(Email, 
                    "Activity Form: "+Day, msg);
}

This code works when I have a form submit trigger on, however, if someone submits with another form (which routes to another worksheet in the same Google Spreadsheet), it won't send them their new submission.

I found a comment somewhere with code like this:

function FormSubmitFunction() {
  var form = FormApp.openById('FORM ID');
 ScriptApp.newTrigger('ActivityFunction')
     .forForm(form)
     .onFormSubmit()
     .create();
}

But I get an error every time it runs.

TL;DR How do I set the active spreadsheet to be the worksheet that the user's form routes to and trigger it's respective function?

Thanks!

Answer 1

As per the answer before me, I would also recommend to read up on event objects. One thing I dislike and would like to point out in your code is that regardless of what you wanted to acomplish, the following lines are nonsense:

SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[4]);
var sheet = spreadsheet.getActiveSheet();

It's like giving someone 50 bucks so they could lend you 50 bucks. Both of those lines are exactly the same as

var sheet = spreadsheet.getSheets()[4]

With that out of the way, going by this

//Email Address
var range = sheet.getRange(lastRow,7,1,1);
var Email = range.getValues();

that means that it does not matter what form is used, you expect the email address to always be in the last row column 7. Instead, by using the event object, you can get the range of the new submission by having function ActivityFunction(event) and a trigger start this function on form submit and then just accessing event.range.

In addition, I see a lot of var range = sheet.getRange(lastRow,2,1,1) so first of all, I would recommend to declare 1 var at the begining and then just assign the new value to it. That makes it easier to find problems should you change variable names etc. And the .getRange(lastRow,2,1,1) is also the same as writing .getRange(lastRow,2) so you are also writing unecesary code. While not a problem in of itself, it does make it easier to make mistakes.

Answer 2

The "on form submit" trigger function is passed an event, and this event in turn contains the Range of the form submission in the responses sheet. You can then use range.getSheet().getName() to find out the name of the responses sheet written to and hence which form the submission came from.

Answer 3

This might help you understand better. Whenever a google function is triggered by an event (onFormSubmit, onOpen ..etc) it is passed an event object as an argument (The argument "e" in the below example). This event Object has attribute which can be accessed as mentioned here: https://developers.google.com/apps-script/guides/triggers/events

The below function is triggered when a form is submitted (details of trigger setup below), we access the values submitted in the form use the values attribute (again refer the link above)

function ActivityFunction(e) {

  //Name
  var Name = e.values[2];
  //value 2 represent the second field entered in your form.

  //Email Address
  var Email = e.values[7];
  //similarly, value 7 represent the seventh field entered in your form.

  //Date
  var Day = e.values[3];

  //Time
  var ExerciseTime = e.values[4];

  //Activity
  var Activity = e.values[5];

  //Intensity
  var Intensity = e.values[6];

  var msg = "Hello "+Name+"!
  Thank you for filling out today's activity form. Here are your responses.
  Name: "+Name+"\n\Date: "+Day+"\n\Time: "+ExerciseTime+"\n\Activity: "+Activity+"\n\Intensity: "+Intensity+"\n\n\Have a great day!"

  MailApp.sendEmail(Email, 
                    "Activity Form: "+Day, msg);
}

Now for setting up the trigger, make sure the above code is written in the script editor of the spreadsheet accepting the form. And setup up a trigger for the activity function from the resources > current project trigger> add> from spreadsheet> on form submit

Immaterial of which form is submitted it will create an event object "e" and should have all the detail you want and you don't have to worry about accessing the sheet to get the responses.

The below code setups trigger specific for a given form (.forForm) so only triggers when that specific form is submitted, hence your code was not triggered when other forms were submitted:

function FormSubmitFunction() {
  var form = FormApp.openById('FORM ID');
 ScriptApp.newTrigger('ActivityFunction')
     .forForm(form)  //This sets the trigger for a specific form.
     .onFormSubmit()
     .create();
}