How to extract response items from a Google Form

Question

I have made a form to capture 2 data.

1. Email address.
2. Group which user wants to subscribe to.

Each group has their own spreadsheet. [According to the posted code, each has their own sheet within the same spreadsheet.]

When a user submits the form, the form should capture the email address, and sends the data to the respective spreadsheet which the user subscribed to.

This is what I've done so far. I'm stuck..

Is there a way to retrieve data specifically from a particular textbox/options ..etc?

The only way I know is to loop all the data and retrieve it 1 by 1.. and that makes it difficult for me to link 2 data together... eg: "[email protected]" subscribed to "Group 1"

function onFormSubmit() {
  var form = FormApp.getActiveForm();

  var formResponses = form.getResponses();
  for (var i = 0; i < formResponses.length; i++) {
    var formResponse = formResponses[i];
    var itemResponses = formResponse.getItemResponses();
    for (var j = 0; j < itemResponses.length; j++) {
      var itemResponse = itemResponses[j];

      // Checks if it is multiple choice option
      if (itemResponse.getItem().getType() == FormApp.ItemType.MULTIPLE_CHOICE) {

        // If user chooses group 1, open spreadsheet and store user's email in 1st column
        if (itemResponse.getResponse() == "1") {
          var ss = SpreadsheetApp.openById("id goes here");
          var sheet = ss.getSheetByName("Group subscription email");
        }
      }


    }
  }
}

Answer 1

Is there a way to retrieve data specifically from a particular textbox/options ..etc?

Yes, and it will simplify your task. The relevant documentation is Event Objects.

Your trigger function will be provided with an event object when it is triggered. In your case, a Forms form submission event, the event includes a FormResponse object, accessible by the response attribute. No need to loop through responses, or open the form.

Since we can get the email and the group from a single response, relating the two becomes trivial. You'll see a helper function being used to get a handle on the appropriate sheet to add a subscription to.

Simple approach - all responses required

As long as your questions are required, the array returned by getItemResponses() will contain the item responses in the order they appear in the form.

/**
 * Trigger function for Google Forms "Form submit" event.
 * Simple, assumes all answers are provided, no error checking.
 */
function onFormSubmit(e) {
  var formResponse = e.response;
  // If all questions are required, getItemResponses returns responses in form-order
  var itemResponses = formResponse.getItemResponses();
  var email = itemResponses[0].getResponse();  // returns a string
  var group = itemResponses[1].getResponse();

  var sheet = getGroupSheet( group );         // use helper function to get right sheet

  if (sheet) {
    sheet.appendRow([email]);               // Add subscriber as a single-cell row
  }
}

/**
 * Gets the sheet for the indicated group.
 * Helper function for onFormSubmit().
 *
 * @param {string} group   The ID of the group, from user's response
 *
 * @returns {Sheet}        Sheet object for the given group,
 *                         null if group is unknown.
 */
function getGroupSheet( group ) {
  var ssId = "id goes here";
  switch (group) {
    case "1":
      var name = "Group subscription email";
      break;
//  case "2":                                  // Add cases to handle other groups
//    var name = "Other sheet name";
//    break;
    default:                                   // If the group is unknown, log it
      name = "";
      Logger.log("Unexpected group ID ("+group+")");
      break;
  }

  if (name) {
    var result = SpreadsheetApp.openById(ssId).getSheetByName(name);
  }
  else {
    result = null;                             // Return null for unknown groups
  }
  return result;
}

Adaptable approach #1 - item indexes

Knowing that all response items were present did make things simple, but we can't always rely on that. If there is a chance that response items could be left blank, we will need to get to specific answers.

There are a few ways to do this. We'll look at two. First, using the item indexes:

/**
 * Trigger function for Google Forms "Form submit" event.
 * Flexible - checks for specific response items by getting the item index
 * for each response item. Example: if user answered questions 1 and 3, but not 2,
 * e.response would contain itemResponses for items 0 and 2, but not 1.
 */
function onFormSubmit2(e) {
  var formResponse = e.response;
  var itemResponses = formResponse.getItemResponses();

  for (var i=0; i<itemResponses.length; i++) {
    switch (itemResponses[i].getItem().getIndex()) {
      case 0:
        var email = itemResponses[i].getResponse();  // returns a string
        break;
      case 1:
        var group = itemResponses[i].getResponse();
        break;
    }
  }  

  var sheet = getGroupSheet( group );         // use helper function to get right sheet

  if (sheet) {
    sheet.appendRow([email]);               // Add subscriber as a single-cell row
  }
}

Adaptable approach #2 - item titles (question text)

Using indexes freed us from the requirement that all answers be provided, but is still brittle; it would require maintenance effort if the form were modified, to ensure the indexes remain aligned.

An improvement we can use is to use the text of the questions to pick our responses. We'll still have to careful if questions are reworded - but this approach is resilient to changes in the order or questions or the addition of non-question items such as images, page-breaks, videos, or headers.

/**
 * Trigger function for Google Forms "Form submit" event.
 * More Flexible - checks for specific response items by getting the item title
 * for each response item. No need to know the exact order of questions this way,
 * as long as we know what the question was. (Ideal if the form is
 * created programmatically.)
 */
function onFormSubmit3(e) {
  var formResponse = e.response;
  var itemResponses = formResponse.getItemResponses();

  for (var i=0; i<itemResponses.length; i++) {
    switch (itemResponses[i].getItem().getTitle()) {
      case "Email Address":
        var email = itemResponses[i].getResponse();  // returns a string
        break;
      case "Group":
        var group = itemResponses[i].getResponse();
        break;
    }
  }  

  var sheet = getGroupSheet( group );         // use helper function to get right sheet

  if (sheet) {
    sheet.appendRow([email]);               // Add subscriber as a single-cell row
  }
}