Race condition between Google Form submission trigger and spreadsheet population?

Question

I have a Google Form that records responses to a spreadsheet. I wrote an Apps Script that is triggered when the form is submitted, which fills in other columns in the spreadsheet based on the response.

I assume that the last row of the spreadsheet is the one that contains the response that is being recorded:

function onFormSubmit(event) {
  const form = FormApp.getActiveForm();
  const spreadsheet = SpreadsheetApp.openById(form.getDestinationId());
  const sheet = spreadsheet.getSheetByName("Parts List");
  const row = sheet.getLastRow();
  ...

This worked well in testing, but today I saw an instance where a submission occurred and the script ran on the row of the previous submission, as if the trigger executed before the new response was recorded in the spreadsheet.

Is there a way to guarantee that my script executes only after the spreadsheet is updated?

As a workaround I can record responses to the spreadsheet myself, rather than using that function of Forms.

An "On Form Submit" trigger can be installed into an Apps Script project bound to the spreadsheet instead of the Form. Then the trigger can use the event object to make sure that the response that was just submitted matches up to whatever processing that the code is doing. The code can get the row number from the event object. Plus you might want to consider using Lock Service if there are multiple simultaneous submissions happening. — Alan Wells

Rubén Rubén · Accepted Answer · 2021-02-24T19:04:41

From the question

Is there a way to guarantee that my script executes only after the spreadsheet is updated?

Use a on form submission installable trigger for a spreadsheet instead of using the one for a form. The spreadsheet should be the one that is linked to the form.

I assume that the last row of the spreadsheet is the one that contains the response that is being recorded:

That might not be always true. If you need to grab the row of the responses use event.range.getRow() (where event is the on form submission event from a spreadsheet installable trigger)

Race condition between Google Form submission trigger and spreadsheet population?

1 Answers