Google-sheets – Google form adding more than one row every time

google sheetsgoogle-apps-scriptgoogle-apps-script-triggersgoogle-forms

I know that when a new response is submitted on Google form, a new row is auto-added to the linked spreadsheet. I use several onformsubmit triggers for my form and linked spreadsheet. But I do not know why more than one row (sometimes more than 100) are being added, which is unnecessary. I have searched Google and Youtube but I think I could not find anything helpful. why is that happening? and how to avoid it?

Best Answer

It's very likely that having several form summit triggers be part of the problem. Another thing that could be causing the problem is having form submissions while a form submit triggered script is running. One more thing that could be causing the problem is a know bug that that a single form submission trigger is triggered multiple times by a single form submission.

The workaround for the the last one is to check if the event object includes the responses or not, if the responses are included, continue otherwise terminate immediately.

Related Solutions

Google-sheets – Google Spreadsheet: Adding formulas to Google Forms responses rows

If you add the following formula in cell D2:

=ARRAYFORMULA(FILTER(C2:C;C2:C<>"")*1.2)

then it will take on the complete column, via the array. The FILTER function, is to make sure no empty cells are taken into account.

I've added the result into the file you shared with us.

Google-sheets – Autoincrement in Google Sheets based on Google Form submission

This can be done with the following script: set a trigger "on form submit" for this function.

The customizable parameters are: the column number where to insert Id, and the initial Id. The function uses Script Properties to keep track of the used Id, so they will not be repeated.

function generateId(e) {
  var columnNumber = 5;
  var initialId = 42;
  var sp = PropertiesService.getScriptProperties();
  var id = sp.getProperty("Event Id") || initialId;
  e.range.offset(0, columnNumber - 1, 1, 1).setValue(id);
  sp.setProperty("Event Id", id + 1);
}

Best Answer

Related Solutions

Google-sheets – Google Spreadsheet: Adding formulas to Google Forms responses rows

Google-sheets – Autoincrement in Google Sheets based on Google Form submission

Related Topic