google apps script - How to get form values in the submit event handler?

Question

I'm trying to get started with a very simple Google Form containing just a couple of questions (a multiple choice with just 2 options and a short text). After creating it, I opened the script editor and typed in

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

and configured onSubmit as the handler for "form submit" trigger using the "Current project's triggers" from the "Edit" menu.

Filling in the form and submitting it now does result in the handler being called, but I only see this in the log:

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

i.e. the response field is empty. I've also tried using FormApp.getActiveForm().getResponses(), but it returns an array of several empty objects too (OTOH, FormApp.getActiveForm().getTitle() does return the title I gave the form).

I suspect I need to give the script some extra permissions to access the form data, but I have no idea how to do it, nor even if this is really the problem.

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

Answer 1

There are 2 patterns for retrieving submitted values. For both patterns, the function for retrieving the values from form submission has to be installed as a trigger. The detail information of Installable Triggers is https://developers.google.com/apps-script/guides/triggers/installable.

1. Script is opened on spreadsheet.

In this case, by installing a trigger, you can retrieve the submitted values by your script. The detail information of Event Objects is 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 is opened on form.

In this case, the submitted values can be retrieved by following script. The detail information is 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's title ##, getResponse()=test

If I misunderstand your question, I'm sorry.