CODEWITHSUNDEEP
google-apps-script
Mayank Agarwal
Mayank Agarwal

Reputation: 65

onFormSubmit takes one row earlier

I am trying to run a mailing script using Google sheet and Google Appscript. The script triggers when form is submitted, however in order to protect data - I am not fetching mail values from the Form response sheet, but I am importing the responses to another sheet and fetching values from there.

Howeever, since I made this change - the mail is sending data in the response prior to the latest one. Maybe its because the sheet is not fetching data quickly enough (even though I've even added a 30s delay).

I looked up this issue, and people were advising Event objects - but I was not able to understand it clearly. Can anyone provide any alternative answer or guide me to an easier resource?

Upvotes: 0

Views: 196

Answers (3)

ale13
ale13

Reputation: 6052

Depending on how your script looks like, you might benefit from one of the following options:

1. Using the e event object

The event object e is nothing but an object which contains information about the context that caused the trigger to fire.

The onFormSubmit trigger is installable form submit trigger which runs when a user responds to a form. There are two versions of the form-submit trigger, one for Forms itself and one for Sheets if the form submits to a spreadsheet.

If the onFormSubmit trigger is attached to a spreadsheet, the event objects are:

  • authMode which is a value from the ScriptApp.AuthMode enum

  • namedValues

  • range

  • triggerUid

  • values

In order to access any of these, you will simply have to pass the e event object to the trigger function and access one of its properties in the function, something similar to this:

function sheetsFormTrigger(e) {
   let submissions = e.namedValues;
}

The submissions variable here will contain the question names and values from the form submission.

If the onFormSubmit trigger is attached to a form, the event objects are:

  • authMode

  • response

  • source

  • triggerUid

Accessing these properties can be done in the same manner as before.

2. Use flush

Another reason why you're not retrieving the data you want from your spreadsheet can be due to the fact that the changes haven't been yet applied to the spreadsheet. To fix this, you should add a flush instruction after the pending changes that need to be applied:

function sheetsFormTrigger() {
   // instructions
   SpreadsheetApp.flush();
}

3. Adjust your script variables

Another reason why your script might not yield the expected result is because the variables and parameters you are using are simply not adjusted properly. I suggest you double-check this and make any changes, if needed.

Reference

Upvotes: 0

Cooper
Cooper

Reputation: 64042

There are two onformSubmit triggers one is for the linked spreadsheet and the other is for the form and their event objects are totally different.

event objects

The answer from Goran is talking about the one from the spreadsheet. Which is the one I'm most familiar with as well. This one provides response information in the event object so that if you get submissions that are real close together in time you can keep their responses separate rather than try to search for the last row which may not be the correct response.

Upvotes: 1

GoranK
GoranK

Reputation: 1668

Check the FormSubmit event parameter

You can use namedValues or values property to fetch the data submitted and send your emails.

Upvotes: 0

Related Questions