google app script: on form response, check which tab of the sheet collects the responses

Question

I am trying to automate some extra data generated to a form response using Google App Script; (in particular, a unique identifier). I can trigger a script on a form response, but I have multiple forms connected to this sheet document, so I need to make sure that the correct form is triggering the response, and then I would like to write the values to the correct sheet of the document.

How do I check which form is being triggered, and how can I find the sheet that collects it's responses?

"I need to make sure that the correct form is triggering the response" Could you elaborate more? If you want specific forms to fire the trigger, all you have to do is install a trigger tor those forms. And you probably want to look at documentation for onSubmit() object event. — user8214858
The confusion is with sheets vs spreadsheets; while the script runs on the spreadsheet, a form is connected to a sheet, of which there can be multiple on the spreadsheet. I resolved to the following: var sheetId = "..."; var range = e.range; if(sheetId !== range.getSheet().getSheetId().toString()) { return; } Which is somewhat hackish, but ok, it works. — xor
Glad you got it working. Not hackish, I think that's exactly right. Maybe you can create a global object to keep track of forms and which sheet they are connected to (by sheet id). — e__n

e__n e__n · Accepted Answer · 2018-06-10T05:06:06

I assume you've already set up the function on the sheet to capture the "on submit" event. You can get the [sheet] object from the e (event) argument.

//make sure to set up the form submit trigger from
//your script menu: Edit -> Current Project's Triggers

function onSubmit(e) {
   var range = e.range
   var sheet = range.getSheet();

   //then do something with [sheet] object

}

I'm pretty sure you can only connect one form to a sheet, so knowing which sheet got written to will tell you which form was submitted.

Check out more about event triggers in the documentation.

google app script: on form response, check which tab of the sheet collects the responses

1 Answers