Form answer Spreadsheet - onChange trigger

Question

I would like to ask about running onChange trigger after change is made in spreadsheet by Google Form.

I have this source code for sending email after change is made:

function onChange(e){

    var sheet = SpreadsheetApp.getActiveSheet();

    var helpRange = sheet.getRange(2, 16);
    var debugRange = sheet.getRange(2,17);
    var startRow = helpRange.getValue();
    var numRows = sheet.getLastRow() - startRow + 1;   // Number of rows to process
    debugRange.setValue(numRows);
    var dataRange = sheet.getRange(startRow, 1, numRows, 11);
    var data = dataRange.getValues();
    var cal = CalendarApp.getDefaultCalendar();
    for (i in data) {
      var row = data[i];
      var title = row[3];
      var desc = "Description: " + row[4] + " Requested Accounts: " + row[5] + " Marketing Support: " + row[6] + " Responsible Salesman " + row[8] + " Email: " + row[9] + " " + row[10];      // Second column
      var tstart = row[1];
      var tstop = row[2];
      var loc = row[7];

      cal.createEvent(title, new Date(tstart), new Date(tstop), {description:desc,location:loc});


      helpRange.setValue(sheet.getLastRow() + 1);
      var emailAddress = sheet.getRange(sheet.getLastRow(), 10).getValue(); 
      var subject = "New Event Created: " + title;
      Browser.msgBox(emailAddress + " " + subject + " "+ desc);
      MailApp.sendEmail(emailAddress, subject, desc); 

    }
}

This spreadsheet should be automatically filled by Google Form (when user submit an answer) and no other user should do any change to it. This onChange trigger is fired every time I made some change physically (as expected but this wouldn't happen) but when I submit answer from Google Form, it is displayed also in spreadsheet but onChange trigger isn't fired.

Why?

Can I write it so that trigger will be fired after change is made by Google Form?

You want the "Form Submit" trigger. See https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events — eddyparkinson, Sep 29 '14 at 02:15
Also, SpreadsheetApp.getActiveSheet(); will only work if a spreadsheet is open, maybe better to open by ID — eddyparkinson, Sep 29 '14 at 03:38

score 2 · Answer 1 · edited Oct 20 '20 at 20:00

There are 4 major points to understand why it's not working:

Like ryan-roth said, the method to use in this case is onFormSubmit().
This method in particular has to be used in a Installable Trigger, so you must create it through the Apps Script Dashboard or programmatically using the ScriptApp service, wich I recommend because it's more simple to understand what's going on. The following code is an example of a function to create a trigger for this case:

function createTriggerFunction(){
  //Get the target spreadsheet
  var ss = SpreadsheetApp.openById('XXXXXXXXXXXX');
  //Create a new trigger using ScriptApp service passing your function as parameter
  ScriptApp.newTrigger("yourMailFunction")
  .forSpreadsheet(ss)
  .onFormSubmit()
  .create()
  
}

Now, for this new trigger to install, in your Google App Script Editor you have to select this createTriggerFunction() in the Select Function list and Run it once, otherwise you're creating the same trigger for each time you run it.
This point is important for this to work: Overwritting functions is for Simple Triggers, in wich case onChange() and onFormSubmit() are not. So in this case your code will work using it in a new function called yourMailFunction for the sake of this example.

function yourMailFunction(e){

    var sheet = SpreadsheetApp.openById('XXXXXXXXXXXX');

    ...

    }
}

IMPORTANT

Note that i've used SpreadsheetApp.openById('XXXXXXXXXXXX') instead SpreadSheetApp.getActiveSheet(). This is very important because when the Form submits and inserts a new row in the associated spreadsheet, there is no active spreadsheet so you have to call it by it's ID.

score 1 · Answer 2 · answered Oct 23 '14 at 17:14

As stated by eddyparkinson, you definitely want a form submit trigger, not a change or edit trigger.

It is possible to set up a form submit trigger in either the Google Form, or in the Google Sheet that receives the form's responses. The trigger event in each case is slightly different; the Google Sheets form submit event contains the form entry values in simple arrays (e.values, e.namedValues), whereas the Forms form submit event contains a FormResponse object that can be queried for all the information about the form submission.

Form answer Spreadsheet - onChange trigger

2 Answers2

IMPORTANT

Linked