Specifying form for 'on form submit' trigg

问题:

I have two google forms, which send data to two tabs in a single spreadsheet.

I have set up a script with two functions. I would like the first function to run on submission of the first form. And, the second function to run on submission of the second form.

Problem is: when setting up a trigger onformsubmit, it does not allow you to specify which form.

Therefore, whenever completing the first form, it runs both functions.

Question: how can I restrict the functions from running only when the specific form is submitted?

回答1:

I was just looking at some documentation and found this:

Documentation forForm

It states:

Creates and returns a FormTriggerBuilder tied to the given form.

Also:

var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz');
 ScriptApp.newTrigger('myFunction')
     .forForm(form)
     .onFormSubmit()
     .create();

onFormSubmit

That code seems to be tied to a specific form.

回答2:

As I suggested in the comments, you can determine the origin of the form submission by analyzing the response object's content.

Here is a basic example to illustrate : it will send you an email to tell which form has been submitted but you can of course use the same condition test to select the action you want to run.

function formSubmitOriginTest(e){
//  Logger.log(JSON.stringify(e));
// example value : {"namedValues":{"Untitled Question 2":["test"],"Timestamp":["8/17/2014 11:22:47"]},"values":["8/17/2014 11:22:47","test"],"source":{},"range":{"rowStart":3,"rowEnd":3,"columnEnd":2,"columnStart":1},"authMode":{}}
  if(e.namedValues["Untitled Question 2"]!=undefined){// only form B has one question with this exact title, that's enough to identify it.
    MailApp.sendEmail(Session.getActiveUser().getEmail(),'form B has been submitted','');// optionally include the answers in the email body if you want
  }else{
    MailApp.sendEmail(Session.getActiveUser().getEmail(),'form A has been submitted','');// optionally include the answers in the email body if you want
  }
}

回答3:

Another way this can be done is by looking at the sheet name that comes in the response's event object via the range property. Here's an example of how I determined the form that triggered the submission:

// set the sheet name that stores the form responses you care about here:
function getSourceSheetName() { return 'Form Responses 1'; }

function submit(eventObj) {
    var range = eventObj.range;

    var eventSourceSheetName = range.getSheet().getName();
    var givenSourceSheetName = getSourceSheetName();

    // if the source sheet (from form response sheet) is not the same as the specified form response sheet, quit (return) so extra forms don't trigger the code
    if (eventSourceSheetName.toUpperCase() !== givenSourceSheetName.toUpperCase() ) {
        Logger.log('A different form just submitted data - quit code');
        return;
    }

    // you now know which form submitted the response, so do whatever you want!
    // ... your code goes here
}

Basically, this code grabs the name of the sheet that the Form response comes from via range.getSheet().getName(). Next, it checks if that name is a specified name we're looking for (specified in the function getSourceSheetName().

I think it's also possible to get the active sheet name via:

eventObj.source.getActiveSheet().getName();

In case you don't want to use "Range".

I hope this helps!