Google Apps Script - How to get corresponding record in destination spreadsheet in onFormSubmit (Google Forms Event)?

2
votes

I catch this event when a respondent sends a response

https://developers.google.com/apps-script/guides/triggers/events#form-submit_4

Inside this function, I want to add some information into the corresponding row (Google creates this row automatically) in the destination spreadsheet

In order to do that, first I need to find this row

The best way I can find at the moment is by using sheet.getLastRow()

https://developers.google.com/apps-script/reference/spreadsheet/sheet#getlastrow

But there is concurrency problem with this method: Serge insas said it here: https://stackoverflow.com/a/17751544/2667212

There are a few risks to use that simple "strategy" as there might be concurrency issues when 2 or more people send a form simultaneously

Is there any correct way to achieve this?

1
google-apps-scriptgoogle-formsgoogle-apps-script-addon

1 Answers

2
votes

Solution:

Easiest way is to disable automatic spreadsheet linking provided by Google and use custom linking.

Snippet:

function formSubmitInForm(e){
 const formResponse = e.response;
 const id = formResponse.getId();
 const timestamp = formResponse.getTimestamp();
 const sheet = SpreadsheetApp.openById(/*SPREADSHEET ID*/)
    .getSheetByName(/*FORM RESPONSE SHEET*/);
 const responses = formResponse.getItemResponses().map(function(itemRes){
    return itemRes.getResponse();
 })
 sheet.appendRow([id,timestamp].concat(responses))
}

Reference: