How to get form values ​​in a submit event handler?

Question

I'm trying to get started with a very simple Google form containing just a couple of questions (multiple choice with two options and short text). Having created it, I opened the script editor and typed

function onSubmit(e) { Logger.log("onSubmit(%s)", JSON.stringify(e)); } 

and configured onSubmit as the submit submit trigger handler using the Current Project Triggers from the Edit menu.

Filling out the form and submitting it now leads to a call to the handler, but I see this only in the log:

 [17-04-15 18:56:23:584 CEST] onSubmit({"response":{},"source":{},"authMode":{},"triggerUid":1870249629}) 

i.e. response field is empty. I also tried using FormApp.getActiveForm().getResponses() , but it also returns an array of several empty objects (OTOH, FormApp.getActiveForm().getTitle() returns the header that I gave the form).

I suspect that I need to grant the script additional permissions to access the form data, but I do not know how to do this, and even if this is really a problem.

Does anyone know why I am not getting form values ​​and what should I do to get them? Thanks in advance!

Answer 1

There are two patterns for retrieving the presented values. For both templates, the function for extracting values ​​from the form view must be set as a trigger. Details on Installable Triggers https://developers.google.com/apps-script/guides/triggers/installable .

1. Script opens in a spreadsheet.

In this case, by setting the trigger, you can get the passed values ​​using the script. Detailed information on event objects https://developers.google.com/apps-script/guides/triggers/events#form-submit .

Script:

 function onSubmit(e){ Logger.log("%s", JSON.stringify(e)); } 

Result:

 { "values": [ "date and time", "test" ], "namedValues": { "fromtestform": [ "test" ], "timeStamp": [ "date and time" ] }, "range": { "columnStart": 1, "rowStart": 2, "rowEnd": 2, "columnEnd": 2 }, "source": {}, "authMode": {}, "triggerUid": ##### } 

2. Script opens in form.

In this case, the presented values ​​can be obtained by following the script. Detailed information https://developers.google.com/apps-script/reference/forms/form-response .

Script:

 function onSubmit(e){ Logger.log("authMode=%s, source.getId()=%s", e.authMode, e.source.getId()); var items = e.response.getItemResponses(); for (i in items){ Logger.log("getItem().getTitle()=%s, getResponse()=%s", items[i].getItem().getTitle(), items[i].getResponse()); } } 

Result:

 authMode=FULL, source.getId()=### form ID ### getItem().getTitle()=## item title ##, getResponse()=test 

If I misunderstand your question, sorry.