0

I have multiple Google Forms that 'On Form Submit' trigger Apps Script to insert the new row of data into a centralized database (Google Sheet).

Example: On Form Submit, Google Form 1 takes the last row of data submitted and pushes this new row of data to a Google Sheet titled 'All Form Data'. Similarly, on Form Submit Google Form 2 takes the last row of data submitted and pushes this new row of data to the same 'All Form Data' spreadsheet.

The Apps Scripts in Google Form 1 and Google Form 2 search the 'All Form Data' Spreadsheet for the last row of data and insert the new row of data into the next row using a script similar to this where 'data' is an array of the values:

allformdata.getRange(allformdata.getLastRow() + 1, 1, 1, 10).setValues(data)

The issue with this approach is that if Google Form 1 and Google Form 2 are submitted at relatively the same time, both Apps Scripts start running and identify the last row of the 'All Form Data' Spreadsheet. If the last row is 100, they will both insert the new row of data into row 101. Whichever script finishes last will be the form's data that appears in row 101. This means that I am missing data that was sent from one of the Google Forms.

What method would be most appropriate to make sure that data from multiple different Google Forms gets added to a centralized Google Sheet database without risking the data not being added to the database?

You help is appreciated in advance!

David

David Steele
  • 1
  • 2
  • If you use the data in the event object you don't have to look for the submitted values because the are in an array in the event object the property name is 'values' – Cooper Mar 18 '22 at 15:02
  • Take a look at this article https://stackoverflow.com/questions/43223774/how-to-understand-lockservice-and-implement-it-correctly – TheWizEd Mar 18 '22 at 15:02
  • @Cooper Thank you for your comment. I do not entirely understand how using the data in the event object vs. storing the submitted values as a variable will prevent the destination database from only storing one of the two rows of data when both forms are submitted simultaneously when using the Apps Script sheet.getrange(sheet.getLastRow()+1,1). Your clarification would be appreciated! – David Steele Mar 18 '22 at 15:16
  • In your situation, you will have fewer problems using importrange. – Mike Steelson Mar 18 '22 at 15:18
  • @TheWizEd Thank you for your response. I have implemented LockService for each unique Google Apps Script that runs on Form Submit. This prevents the script from starting again when it is currently in progress. The script itself is pushing the new entry to a central database. However, when there is another Google Form with another Apps Script and LockService that is also pushing to the same central database. Essentially, one Apps Scripts doesn't know that the other Apps Script is adding a new row and vice versa. Thank you for your help! – David Steele Mar 18 '22 at 15:25
  • [Form Submit Event Object](https://developers.google.com/apps-script/guides/triggers/events#form-submit) has a values array that contains event element of the submitted data in the same order as the columns in the linked sheet. So if you get submissions rather quickly you can always associated the correct data with each submission by look in the event object. – Cooper Mar 18 '22 at 15:30

1 Answers1

0

Append Rows to master sheet

function onFormSubmit(e) {
  Spreadsheet.getActive().getSheetByName("Master Sheet").appendRow(e.values);
}
Cooper
  • 59,616
  • 6
  • 23
  • 54