onFormSubmit() not working in Google Form?

Question

[UPDATE] I had a look at add-ons and I am afraid this won't work. So let me take a step back and describe what I am trying to achieve. I have a spreadsheet A, with a list of individual events. Each event is a line item in the spreadsheet. The spreadsheet is very long for one, and has many fields that I don't need to expose to event owners (different events different owners). Which means if I allow all these different people edit access to the sheet, it becomes really chaotic.

The solution I came up with is to generate unique IDs programmatically for each event, which I've done. Then for each event, I create an individual form and a pre-filled link, with pre-filled answers that is pulled from the cell values. I intend to give the pre-filled links to event owners when they need to make any updates.

The issue is now I have 100+ forms, and I don't want to have 100+ corresponding tabs set as destinations of these forms. These 100+ forms need to submit responses to one same sheet (tab). Instead I wrote a function for submitted responses to find the right event (the event unique ID is the title of the form) and updates the right cell. This is what you see below processSubmission().

I have tried to write the processSubmission() in the spreadsheet where the events are listed. If I don't set this spreadsheet as destination of these 100+ forms then the spreadsheet doesn't know there is a "submission" event. Therefore the setting the trigger onFormSubmit() in the spreadsheet doesn't work.

Then I moved onFormSubmit() -> processSubmission() and it doesn't set the trigger because as you all pointed out, it's an installable trigger.

What I did manage to to write an onOpen() -> create the onFormSubmission() trigger. That means I had to manually open 100 forms and close them to create that trigger. The triggers are created alright. But turned out for the trigger to actually run I need to manually grant permission!

When I looked at add-on triggers, it says "Add-ons can only create triggers for the file in which the add-on is used. That is, an add-on that is used in Google Doc A cannot create a trigger to monitor when Google Doc B is opened." So I think that also rules out the add-on triggers. So now I am out of ideas.

[ORIGINAL] I made a custom function for the processing of submission responses. I use the form title as a key, and the response answers are written to the corresponding headers in the row with the right key.

My first try was something like this. But it simply didn't execute when the form was submitted:

function onFormSubmit(e){
  var form = FormApp.getActiveForm();
  var key = form.getTitle();
  var responses = e.response;
  
  var ss= SpreadsheetApp.openById(ss_id);
  var sheet = spreadsheet.getSheetByName('Launch list');
  var frozenRow = sheet.getFrozenRows();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();

  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  var keyCol = headers.indexOf(key_header) + 1;
  var header1Col = headers.indexOf(header_1) + 1;
  var header2Col = headers.indexOf(header_2) + 1;
  var header3Col = headers.indexOf(header_3) + 1;

  var keysRange = sheet.getRange(frozenRow+1, keyCol , lastRow - frozenRow, 1);
  var allKys = keysRange.getValues();
  
  for (i=0; i<allKys.length; i++){
    var keyValue = allKys[i][0];
    if (keyValue === key){
      var rowNum = l + frozenRow + 1;
      break;
    }
    else {
      continue;
    }
  }
  
  var dataRow = sheet.getRange(rowNum, 1, 1, lastColumn).getValues()[0];

  var lookUp = {};
  lookUp[item_title_1] = header1Col ;
  lookUp[item_title_2] = header2Col ;
  lookUp[item_title_3] = header3Col ;

  var items = form.getItems();
  var cnt = 0;
  var changes = [];

  for (i=0; i< items.length; i++){
    var item = items[i];
    var title = item.getTitle();
    var itemResponse = responses.getResponseForItem(item);
    var existingValue = dataRow[lookUp[title] -1];
    if ((itemResponse.getResponse() !=='' || itemResponse.getResponse() !== undefined) && itemResponse.getResponse() != existingValue){
      cnt++;
      var cell = sheet.getRange(rowNum, lookUp[title], 1, 1);
      cell.setValue(itemResponse.getResponse());
      changes.push(title);
    }
    else {
      continue;
    }  
  }
 Logger.log('Made ',cnt,'changes for launch ',featureID,': ',changes);
}

I also tried a slightly different approach but also didn't work:

function onFormSubmit(){
  processSubmission();
}

// Processing form submission
function processSubmission() {
  var form = FormApp.getActiveForm();
  var key = form.getTitle();
  var responses = form.getResponses()[form.getResponses().length-1];
  
  // The rest is the same.
}

Manually running the function in the second approach proved my function processSubmission() works. Manually add a onFormSubmit() trigger via the Apps Script Dashboard is not going to be possible because I am generating hundreds of forms (one for each key) programmatically so I chose to have onFormSubmit(e) in the template and every new form is a copy of the template which should also have copies of these functions. But it just doesn't work! Any insight?

Answer 1

The onFormSubmit trigger is an installable trigger which means that it requires to be set up before being able to use it.

It's also important to keep in mind the following, according to the installable triggers documentation:

Script executions and API requests do not cause triggers to run. For example, calling FormResponse.submit() to submit a new form response does not cause the form's submit trigger to run.

What you can do instead is to create the trigger programmatically, something similar to this:

function createTrigger() {
    ScriptApp.newTrigger('onFormSubmit')
          .forForm('FORM_KEY')
          .onFormSubmit()
          .create();
}

Reference

• Apps Script Installable Triggers;

• Apps Script FormTriggerBuilder Class.