0

I'm using Google Forms and I have an "on form submit" trigger that runs a function that takes an event, e. I access the submitted form values with e.namedValues inside the function. Most of the time everything works fine, but occasionally e.namedValues is null, or at least all values are empty and a notification email is sent with missing info. I know it's not because an empty form was submitted because most values are required and these blank emails correspond to valid form submissions that are inserted in a Google Spreadsheet. Is there any reason why I would sometimes not have access to the submitted form values in that function?

regularmike
  • 1,167
  • 10
  • 22
  • 2
    We have seen similar behavior recently and typically we have found that the way to prevent these triggers from affecting your code is not to block them inside of the function with something like `if(e.values && !e.values[1])return;` Typically, the offending triggers only send the timestamp. – Cooper Nov 14 '19 at 03:30
  • You can read more about them [here](https://stackoverflow.com/questions/54834837/how-can-i-be-getting-multiple-unwanted-event-blocks-from-the-same-onformsubmit-t) – Cooper Nov 14 '19 at 03:42
  • @Cooper great, thanks. You mean "to block them" and not "not to block them," right? – regularmike Nov 14 '19 at 14:17
  • Yes. I find that all to often my fingers don’t deliver the message that my brain intended. – Cooper Nov 14 '19 at 14:46

1 Answers1

4

This seems to be a recent Apps Script bug where the trigger falsely fires multiple times without valid data, but Stack Overflow users J.G. and Cooper provided a useful workaround: check the value of a required question. So simply include if (e.values && !e.values[1]) { return; } at the very beginning and it should prevent extra executions.

Please keep in mind that e.values[1] is assuming that the associated question in the form is required. If that question is not required, and a response is submitted without an answer to that question, then your script would prematurely exit. So change that index as necessary to match with your form.

function onFormSubmit(e) {
  // Reject spurious triggers by checking the value of a required question.
  // In this case, we assume  e.value[1] is required.
  if (e.values && !e.values[1]) { return; }

  // Otherwise, do your thing
}
Diego
  • 9,261
  • 2
  • 19
  • 33
  • awesome. So I guess we are pretty sure that the trigger will also fire with the correct data? This solution is great as long as I'm just getting an extra execution instead of a single execution without data. – regularmike Nov 14 '19 at 14:18
  • @regularmike Yes. If you take a look at the Cooper's answer (referenced by both him and me above), you'll see that the extra executions aren't completely blank. `e.values` returns something like `[timestamp, "", "", ""]` for a 3-question form. That's why you want to check if the required question is blank, because the _only_ way it could ever be blank in the response is if it was a spurious trigger. – Diego Nov 14 '19 at 14:38
  • yes, that's what it sounded like, just wanted to confirm that it always does the correct thing in addition to the extra execution. – regularmike Nov 14 '19 at 18:31