Script running multiple times onFormSubmit

Question

I have the following code set to run based on a onFormSubmit trigger but it will sometimes run multiple times with the same submission. I want to verify if it already copied the row and if so to stop the script.

function toDo(){
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var jobs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jobs");
  var lastrow = responses.getLastRow();
  var col = responses.getLastColumn();
  var row = responses.getRange(lastrow, 1, 1, 19).getValues();

  jobs.appendRow(row[0]);
  //copyValuesOnly(copyFromRange, copyToRangeStart);

  var si = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Jobs');

  var range = si.getRange("A2:R");

  range.sort({column: 5,ascending: true}),({column: 1, ascending:true});

}

Is that necessary? I mean, in google when you submit the form, the responses are being sended to a Google Spreadsheet automatically. — jbra95, Mar 21 '19 at 15:19
So I have it set up to copy to a new sheet that will then be able to remove jobs when they are complete so we are only looking at jobs that need to be completed while maintaining the list of all jobs. — Keith Walker, Mar 21 '19 at 15:24
I figured out that if there was a required question on the form that was either incorrect or skipped and the form was submitted it will submit it multiple times for each time the submit button was clicked. — Keith Walker, Mar 21 '19 at 15:34
Take a look at [this](https://stackoverflow.com/questions/54834837/how-can-i-be-getting-multiple-unwanted-event-blocks-from-the-same-onformsubmit-t). — Cooper, Mar 21 '19 at 15:56
So I confused on how to add this to the code, I'm not sure what the e.values would be I do know that column k in my sheet is the exact same on the redundancies as the original run. — Keith Walker, Mar 21 '19 at 16:58

score 1 · Accepted Answer · answered Mar 21 '19 at 17:58

this is a known problem with GAS + Forms. The way that you solve it is by creating a script lock that rejects (causing them to return early) all other attempts within a period of time.

function toDo(){
     SpreadsheetApp.flush();
     var lock = LockService.getScriptLock();
  try {
    lock.waitLock(5000); 
     } catch (e) {
        Logger.log('Could not obtain lock after 5seconds.');
        return HtmlService.createHtmlOutput("<b> Server Busy please try after some time <p>")
        // In case this a server side code called asynchronously you return a error code and display the appropriate message on the client side
        return "Error: Server busy try again later... Sorry :("
     }
  var responses = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
  var jobs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Jobs");
  var lastrow = responses.getLastRow();
  var col = responses.getLastColumn();
  var row = responses.getRange(lastrow, 1, 1, 19).getValues();

  jobs.appendRow(row[0]);
  //copyValuesOnly(copyFromRange, copyToRangeStart);

  var si = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Jobs');

  var range = si.getRange("A2:R");

  range.sort({column: 5,ascending: true}),({column: 1, ascending:true});
  Utilities.sleep(5000);
  lock.releaseLock)();
}

I've had scripts that do this up to 8 times, and usually do it every 2-3 seconds. With this solution you are making a lock at the beginning and then sleeping at the end to make sure that the process time is greater than the wait time. (Here I used 5 seconds, that should prevent the double entry).

Does this prevent legitimate form submissions from running, like if multiple users are entering at or near the same time, or if one user is submitting multiple similar form entries? — EHLOVader, May 13 '19 at 20:01
Within the next five seconds yes. There are workarounds that involve keeper functions checking on a regular basis for unprocessed items but I only use them occasionally. — J. G., May 13 '19 at 21:20

Atul Mehra · Answer 2 · 2022-07-09T07:18:08.263

How to check: Go to script editor and check under execution logs. If you see multiple instances of on form submit, then you probably have multiple triggers somehow and the trigger is running multiple times legitimately.

To fix:

Go to triggers tab and delete all unwanted triggers.
Check code if you are creating new trigger through code. And comment that out.

Possible Reason: I would have expected the code such as below to overwrite existing trigger. I had 19 triggers created. This was because every time I generated the link, it called Initialize and I got a new trigger added. Thus I could see script running 19 times.

const initialize = () => { const form = FormApp.getActiveForm(); //ScriptApp.newTrigger('onFormSubmit').forForm(form).onFormSubmit().create(); };

This is such a perfect answer. And yet nobody voted for it. Works like a charm and solves the problem from the root. — Hemant Shinde, Jul 02 '23 at 14:08

user11522582 · Answer 3 · 2019-05-19T05:04:47.060

0

I have noticed that if you just make another copy of the sheet with the script, this error goes away. Seems to reset whatever was the issue in the original copy. Also try dis-abling the response receipts on your google forms.

edited May 19 '19 at 05:04

answered May 19 '19 at 04:44

user11522582

1
1

score -1 · Answer 4 · answered Jun 05 '21 at 07:34

I noticed the problem got solved if I renamed the function called by onFormSubmit and saved the script.

If I click "run" in the script editor I get 2 emails per form submit. If I click "run" again I get 3 emails per form submit. I reset to only 1 email per form submit if I change the function name again.

Somehow when I run the script it duplicates the triggers.

Script running multiple times onFormSubmit

4 Answers4

Linked