Explicit correspondence between Google spreadsheet cells and Google form input fields

Question

I am making a web form for people to sign up for an event. I found that I can use Google form and Google spreadsheet for this task, and I started to learn how to use these web applications. I would also like to send the automatic confirmation e-mail to those who have signed up. For this task, I am also looking into the Google Apps Script. As far as I understand, I should define a function to send e-mail in a script in the spreadsheet and trigger this function at 'Form Submission Event'. I would like to identify the e-mail address of a person who signed up from the data he/she submitted, and I would like to include all the submitted data as well as the time stamp in the confirmation e-mail.

My questions are the following.

• How can I identify the cell in the spreadsheet into which the value of an input field in the Form is stored?
• Or, is there any way that I can read the values of the respective input fields from a Google Apps Script?

I would be glad if you could kindly refer me to an unambiguous API reference related to these questions.

So far I learned about the applications from the help pages provided in Google Drive, e.g.

https://developers.google.com/apps-script/overview

However, I feel documents there are too concise.

I am learning how to send confirmation e-mail from this Google Apps Script: FormSubmissionResponseEmail

I could not find a help document that explicitly relates an input field in a Google form and a cell in the Google spreadsheet. From my limited number of experiment cases, it seems that the time stamps are always stored in the first column of the spreadsheet. Is this guaranteed? It seems that 'namedValues' member of the 'Spreadsheet Form Submit Events' class is said to contain "the question names and values from the form submission." (https://developers.google.com/apps-script/understanding_events) However, when I modified the Google form, the 'namedValues' member still held the elements corresponding to deleted input fields. Is there any way to loop over only those elements in 'namedValues' that corresponds to the fields actually input by a user?

I would also be glad to hear about alternative tools to replace Google form and Google spreadsheet.

Answer 1

This answer applies to the "new Forms", the 2013 version, not "Legacy Forms" which have been discontinued.

How can I identify the cell in the spreadsheet into which the value of an input field in the Form is stored?

You can identify the column that will collect answers to a form question by the label in row 1. Armed with that knowledge, you can reference the answers by column number in functions such as getRange().

...is there any way that I can read the values of the respective input fields from a Google Apps Script?

There are multiple ways to reference input values:

• As you found in Understanding Events, using a function triggered by Form Submission you can retrieve input values from the event itself. Two options here; you get a set of values in an array, and namedValues that you can reference using the question text as a name.

• You can read the data from the spreadsheet; within that same trigger function mentioned earlier, you could use e.range.getValues() to get an array with all the submitted values, which you could then reference by index. Remember that this would be a 0-based array, while the column numbering starts at 1.

From my limited number of experiment cases, it seems that the timestamps are always stored in the first column of the spreadsheet. Is this guaranteed?

Unless you modify the spreadsheet, the timestamp will be in the first column. It is possible to insert columns to the left of the data table created by Forms, which will affect where you would find your form results in the sheet (although e.range will adjust). The order of all following values will be the order that the questions were created in the form. Note that if you delete a column (because you removed the question from the form, say), the column for the "deleted" question will be recreated at the end of the row.

...when I modified the Google form, the 'namedValues' member still held the elements corresponding to deleted input fields. Is there any way to loop over only those elements in namedValues that corresponds to the fields actually input by a user?

There are reasons for remembering past questions, although they are just a bother when they weren't used to collect any real data. Better planning can be used to avoid the problem!

Unanswered questions will be empty strings in the event (e.g. e.namedValues["Dead Question"] == ''). So you could skip them like this:

for (var ans in e.namedValues) {
  if (e.namedValues[ans] != '') {
    doSomethingWith(e.namedValues[ans]
  }
}

Note, too, that you can get array of "headers", or the form questions, like this:

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var headers = sheet.getDataRange().getValues()[0];

...and then use a search of headers to find the column containing the answer you're looking for.