onSubmit Trigger Executed Without Form Submission

Question

I created a Google Form, linked to a Sheet to capture responses, and added an Apps Script that runs each time the Form is submitted. Ran through a bunch of tests and everything was working fine - form responses fed through, onSubmit function working great. Last night, though, we received a few executions of the script even though the Form was not submitted.

Looking at the Responses page on the Form itself, there were no submissions when I got the notification. Also, this is not a public Form in my organization, and only one other person has the link besides myself. He confirmed he didn't submit the form at the time of the executions.

There are two sheets in the Google Sheet: 1> Form Responses and 2> Data. The data sheet uses a few QUERY functions to pull data from the responses sheet, formatting it differently (e.g. putting hyphens in phone numbers, rendering some fields in upper case, etc.). Also, the data sheet headers are labeled differently than the Form questions (e.g. 'homeAdd1' instead of 'Home Address Line 1'). This is because the script creates a PDF, using the Form responses to replace placeholders ('%homeAdd1%') on a template Google Doc. The script then takes the generated PDF and emails it to the submitter.

Again, everything was working great until yesterday's testing. I didn't realize it at the time, but when my colleague was inputting random values to test the Form, for the Home Address Line 2 he only input a 5-digit ZIP code. It generated a PDF fine, and also emailed it to him, but this caused the QUERY function to render a #VALUE error. The functions look like this:

=QUERY(Responses!L2:S,"SELECT UPPER(L) UPPER(M)...

So when Sheets saw a cell with just 5 digits, it automatically rendered it as a number, and UPPER doesn't work on number values. I (stupidly) didn't think to pre-format all of both sheets as plain text, so this occurred.

Would a #VALUE error on a Google Sheet linked to a Form and an Apps Script cause a misfire of the onSubmit function? This is the only thing I can see that could have possibly caused it, but it doesn't make sense. I've fixed the formatting issue, but I don't know if an erroneous execution could mean some other issue.

With the extra submissions, the script just sent the most recent PDF again and again. Within 20 seconds, it fired 5 times, sending the last PDF that was generated via email each time. Looking at the Stackdriver logs, there's nothing different from when we were testing it earlier yesterday. The console.log and console.info commands work fine, and they all come through listed as having been triggered by the onSubmit function.

Here's the script:

Submit function:

function onSubmit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  var list = ss.getRange("A1:A").getValues();
  var row = list.filter(String).length;
  var email = ss.getRange(row,2).getValue();
  var newResponse = ss.getRange(row,3).getValue();
  if (newResponse == 'Generate New') {
    newOne(ss,row,email);
  } else if (newResponse == 'Upload Completed') {
    completed(ss,row,email);
  } else {
  }
}

Function that was executed:

function newOne(ss,row,email) {
  var name = ss.getRange(row,4).getValue();
  console.log('Function Start - ' + name);
  var newType = ss.getRange(row,6).getValue();
  var copyFile = DriveApp.getFileById('[file id]').makeCopy();
  var copyDoc = DocumentApp.openById(copyFile.getId());
  var copyBody = copyDoc.getActiveSection();
  // Replacing variables with values on spreadsheet
  console.log('Create file start - ' + name);
  var newInfo = ss.getRange(row, 1, 1, 29).getDisplayValues();
  var header = ss.getRange(1, 1, 1, 29).getDisplayValues();
  for (var i = 1; i <= 5; i++) {
    copyBody.replaceText('%' + header[0][i] + '%', newInfo[0][i].toString());
  }
  var x;
  if (newType == 'Office 1') {
    x = 6;
  } else if (newType == 'Office 2') {
    x = 15;
  } else {
  }
  for (var i = x; i <= (x + 8); i++) {
    copyBody.replaceText('%' + header[0][i] + '%', newInfo[0][i].toString());
  }
  copyBody.replaceText('%' + header[0][26] + '%', newInfo[0][26].toString());
  // Create the PDF file, rename it, and delete the doc copy
  copyDoc.saveAndClose();
  var newFile = DriveApp.createFile(copyFile.getAs('application/pdf'));
  newFile.setName('New - ' + name + '.pdf');
  copyFile.setTrashed(true);
  console.log('Create file finished - ' + name);
  //Mails PDF to submitter
  console.info('Pre-email log for ' + name);
  MailApp.sendEmail(email,'Email Subject','', {
                    noReply: true,
                    htmlBody: "<body>Hello, and thank you.</body>",
                    attachments: [newFile]
                    });
  console.info('Email sent for ' + name);
  appFile.setTrashed(true);
}

Any insight / help would be appreciated; thanks!

Josh

I've had it multi fire off a single submit before (the solution is a script lock that returns after a few seconds of waiting) but have never seen a completely empty onsubmit. i can't imagine anything held on the results page could possible cause this behavior. — J. G., Apr 30 '19 at 21:13
@J.G. well, glad to know an error on the results page is unlikely to cause random submissions. And yeah, not sure what the empty submissions in the middle of the night are about. — Josh, May 01 '19 at 16:39

score 2 · Accepted Answer · edited Dec 20 '20 at 18:56

2

Spurious unwanted Event Triggers

I've had problems with spurious triggers coming from onFormSubmit event triggers. In my case they were always immediately after a real trigger occurred from a Form Submission. I found that I could identify them because none of my required questions were answered. I discuss it here.

It might be worth your time to capture the e.values array and see if you can find a consistent way to keep them from causing a misfire of your processing function.

edited Dec 20 '20 at 18:56

halfer

19,824
17
99
186

answered May 01 '19 at 04:44

Cooper

59,616
6
23
54

Not sure why I didn't run across your question when I was searching, but thanks for the help! I'm going to put in that ```if(e.values && !e.values[1]){return;}``` logic into the top function to catch any unwanted submissions (all of the responses are required). Hopefully this will fix the issue! These typically happen overnight, so I'm going to test again tonight with that in there. – Josh May 01 '19 at 16:31
Actually, that's intended to be an example that worked for my situation with my set of questions. Basically, what I was seeing was that required questions in the spurious triggers where blank in the e.values array. So I just decided to use that fact to eliminate these unwanted triggers from running my function. So you'll want to take a look at what your's look like. So I would recommend logging the time and the e.values array and see what they look like. – Cooper May 01 '19 at 16:40
Gotcha; yes, I did add a ```console.log``` entry to capture the e.values each time that top function executes (before the ```if(e.values...``` line), and they're coming over just fine when I tested again. I'll keep an eye on those logs to see if the values are empty, and I'm guessing that if they are empty, the ```if``` statement will prevent this from happening again. If they're not empty, I'll have to see what else may be causing the unwanted submissions. – Josh May 01 '19 at 16:46
Well, this looks to have fixed it! In the Apps Script Dashboard, I can see the extra submissions, but the function executions last a fraction of a second as it's caught by that ```if(e.values && !e.values[1]){return;}``` line. ```e.values[1]``` for my form is a required field, and is just a text field. I noticed that sometimes values do come through in these extra submissions, and they're usually dates or just number values. We don't have the option enabled to let people edit their answers after the fact, so I'm not sure what's happening with that. Either way, looks good now! Thanks! – Josh May 03 '19 at 13:57

score 0 · Answer 2 · answered Apr 30 '19 at 21:21

0

As far as I know, onSubmit(e) doesn't work the way you're expecting it to.

I think what you're looking for is an onFormSubmit trigger, try using the following from Class SpreadsheetTriggerBuilder documentation to create a script trigger that executes every time someone submits a response to your linked form:

var sheet = SpreadsheetApp.getActive();
ScriptApp.newTrigger("function name")
  .forSpreadsheet(sheet)
  .onFormSubmit()
  .create();

answered Apr 30 '19 at 21:21

ross

2,684
2
13
22

I'm using an [installable trigger](https://developers.google.com/apps-script/guides/triggers/installable), which allows me to choose which function to run whenever the Form is submitted (from the Apps Script Dashboard). Would creating a new trigger each time have a benefit that using an installable trigger wouldn't have? I'm relatively new to Apps Scripts (~1 year), so I've not heard of the Trigger Builder before. And how would that builder be triggered? Not sure I follow. – Josh May 01 '19 at 16:37
Creating the trigger using trigger builder rather than the way you mentioned really isn't much different. Once you run the trigger builder script it'll add a trigger where you'd usually go to find them anyway. – ross May 01 '19 at 19:59

score 0 · Answer 3 · answered May 23 '19 at 08:58

I have an installation of a spreadsheet and corresponding form where I get frequent duplicate on form submit events, inexplicably. It does not occur in other installations. If this is your situation you can't just check if the event is null because to test it for null you have to have something to test. If it's undefined you will get an error. So first test if it's undefined. Try this code:

`function formSubmitted(e) {
  // Deal with the unusual case that this is a bogus event
  if ((typeof e === "undefined") || (e == null) || (e.length == 0))  { 
    Logger.log("formSubmitted() received a bogus or empty event");
    return;
  }
  ...`

onSubmit Trigger Executed Without Form Submission

3 Answers3

Spurious unwanted Event Triggers