CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-formsgoogle-sheets-macros
Mike - SMT
Mike - SMT

Reputation: 15226

Problem with function triggered by OnFormSubmit when multiple are submited at once to spreadsheet

I have a Google Script function that works as expected however when receiving many form submissions at once I run into a problem when the data is copied.

If data comes in fast enough the wrong information may be copied to the other sheet.

Here is an example set. I submitted 4 form's at once and the data came in just fine. enter image description here

However when the scrip ran that was triggered by the OnFormSubmit it managed to copy one of the rows of data twice overwriting another.

As you can see below the data 4 and 44 were coppied over twice and is missing the 2 and 22 data.

enter image description here

So with that my question is this:

Is there a way to prevent this kind of duplication of information. Some way to Q up the function if one is already running?

Here is the code:

function Auto_move_form_data() {
  var form_name = "Form Responses 1";
  var open_name = "OPEN";

  var workbook = SpreadsheetApp.getActiveSpreadsheet();
  var form_ws = workbook.getSheetByName(form_name);
  var open_ws = workbook.getSheetByName(open_name);

  var form_last_row = form_ws.getLastRow()
  var open_last_row = open_ws.getLastRow()

  form_ws.getRange(form_last_row,2).copyTo(open_ws.getRange(open_last_row+1,1), {contentsOnly:true});
  form_ws.getRange(form_last_row,3).copyTo(open_ws.getRange(open_last_row+1,3), {contentsOnly:true});

}

Upvotes: 0

Views: 87

Answers (1)

Cooper
Cooper

Reputation: 64042

Try this:

Create an onFormSubmit trigger for the following function.

function Auto_move_form_data(e) {
  var dsh= SpreadsheetApp.getActive().getSheetByName("OPEN");
  dsh.appendRow([e.values[2],'',e.values[3]]);
}

Upvotes: 1

Related Questions