Getting a script to trigger when only a specific form is submitted - Google Script / Sheets / Forms

Question

I feel like this should be so easy and obvious, but I cannot figure it out...

I am working with 2 forms and 2 spreadsheets. Form 1 submits to Sheet 1. On Sheet 1, for each record, there is a link that takes the user to Form 2, which is half pre-populated with data from Sheet 1. When the user submits Form 2, it populates Sheet 2.

I have a couple of scripts that are supposed to be triggered when Form 1 is submitted. I am using the "From Spreadsheet" "OnFormSubmit" trigger. The scripts, however, are also triggering when Form 2 is submitted.

How can I make it so the scripts only execute when Form 1 is submitted? Also, is there a way to ensure that scripts trigger in a specific order?

If it helps, the scripts are below. They all work properly as is, except for the triggering issue. I know that I can merge the 2nd and 3rd script, and I will, but I'd like to fix this triggering issue first, as I'm getting double the emails every time I test.

1st script:

function onFormSubmit(e) {

  //Uses time in milleseconds to create unique ID #
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("ComplaintLog");
  var d = new Date();
  var n = d.getTime();
  var lastRow = sheet.getLastRow();
  var cell = sheet.getRange("BA" + lastRow);
  cell.setValue(n);
  var cell = sheet.getRange("BB" + lastRow);
  cell.setFormula("=right(BA"+lastRow+",6)");
  sheet.getRange("BB"+lastRow).copyTo(sheet.getRange("BC"+lastRow), + 
    {contentsOnly:true});
}

2nd script:

function formSubmitReply(e) {
//Sends email to certain users when a new complaint has been entered  
  var emailAddresses = '[email protected]';

  MailApp.sendEmail(emailAddresses,
                  "[email protected]",
                  "New Guest Complaint",
                  "A new guest complaint has been entered into the database."
                  + "\n\n To vew the database, click here: http://goo.gl/DI33EC");
}

3rd script:

function createResolutionForm() {

  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName("ComplaintLog")
  var lastRow = sheet.getLastRow();
  var data = ss.getSheetByName("ComplaintLog") +
    .getRange("A"+lastRow+":Z"+lastRow).getValues();
  var form = FormApp.openById('The form's ID goes here.  that part works.');
  var items = form.getItems();

  for (var i = 0; i < data.length; i++) {
    var formResponse = form.createResponse();

    //ID
    var formItem = items[1].asTextItem();
    var response = formItem.createResponse(data[i][0]);
    formResponse.withItemResponse(response);

    //Guest Name
    var formItem = items[2].asTextItem();
    var response = formItem.createResponse(data[i][3]);
    formResponse.withItemResponse(response);

    //email
    var formItem = items[3].asTextItem();
    var response = formItem.createResponse(data[i][4]);
    formResponse.withItemResponse(response);

    // The pre-populated form is being created here.  I didn't include every 
    // form item for brevity's sake.
  }

    //Create Link
    var formUrl = formResponse.toPrefilledUrl();

    //Enable Clickable ID
    var idNum = sheet.getRange("BC"+lastRow).getValues();

    sheet.getRange("A"+lastRow).setFormula +
       ('=HYPERLINK("' + formUrl + '","' + idNum + '")');

    var sheetUrl = "The URL to the spreadsheet goes here - that part works.";


    //Send Email to assigned managers
    var j,tempname=[],name, subject, managername, message;

    managername = sheet.getRange("P"+lastRow).getValue();
    tempname=managername.split(" ");

    Logger.log(managername)

    if (tempname.length==2) {
        name=tempname[0].slice(0,1) + tempname[1] + '@organization.com';
        subject = 'Action Required';
        var message = "<html><body>"
        + "<p> You have been assigned to follow-up on a complaint,"
        + "or your contact information has been given to a customer in"
        + "regards to a complaint."
        + "<p><p>The complaint ID number is " + idNum +"."
        + "<p>To go directly to this complaint," 
        + "<a href="+formUrl+"><b>click here</b></a>."
        + "<p>To vew the database so that you can take action," 
        +<a href="+sheetUrl+">click here</a>."
        + "</body></html>";


      MailApp.sendEmail(name, subject,"",{htmlBody : message});
      }
    }

Mridsole Mridsole · Accepted Answer · 2014-07-09T10:42:29

Setting up the triggers from within the script might be what you're looking for. You can create triggers that respond to form submission events on a per-form basis like so:

function setup () {
  ScriptApp.newTrigger('onForm1ResponseHandler').forForm(form1).onFormSubmit().create();
  ScriptApp.newTrigger('onForm2ResponseHandler').forForm(form2).onFormSubmit().create();
}

where form1 and form2 are Form objects and 'onForm1ResponseHandler' is the name of the handler function.

With a handler function set up like this:

function onForm1ResponseHandler (e) {
  ...
}

e will be an object with properties documented here (for the Form Submit event).

Getting a script to trigger when only a specific form is submitted - Google Script / Sheets / Forms

1 Answers