Quickstart: Managing Responses for Google Forms

Complete the steps described in the rest of this page, and in about five minutes you'll have automatically created a Google Form based on data in a spreadsheet, then set it up to email Google Calendar invites and a personalized Google Doc to everyone who responds.

Set it up

  1. Make a copy of the sample spreadsheet Apps Script Quickstart: Managing responses for Google Forms.
  2. From within your new spreadsheet, select the menu item Tools > Script editor. If you are presented with a welcome screen, click Blank Project.
  3. Rename the Code.gs file to forms.gs.

  4. Replace any code in the forms.gs file with the code below:

    sheets/forms/forms.gs
    /**
 * A special function that inserts a custom menu when the spreadsheet opens.
 */
function onOpen() {
  var menu = [{name: 'Set up conference', functionName: 'setUpConference_'}];
  SpreadsheetApp.getActive().addMenu('Conference', menu);
}

/**
 * A set-up function that uses the conference data in the spreadsheet to create
 * Google Calendar events, a Google Form, and a trigger that allows the script
 * to react to form responses.
 */
function setUpConference_() {
  if (ScriptProperties.getProperty('calId')) {
    Browser.msgBox('Your conference is already set up. Look in Google Drive!');
  }
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Conference Setup');
  var range = sheet.getDataRange();
  var values = range.getValues();
  setUpCalendar_(values, range);
  setUpForm_(ss, values);
  ScriptApp.newTrigger('onFormSubmit').forSpreadsheet(ss).onFormSubmit()
      .create();
  ss.removeMenu('Conference');
}

/**
 * Creates a Google Calendar with events for each conference session in the
 * spreadsheet, then writes the event IDs to the spreadsheet for future use.
 * @param {Array<string[]>} values Cell values for the spreadsheet range.
 * @param {Range} range A spreadsheet range that contains conference data.
 */
function setUpCalendar_(values, range) {
  var cal = CalendarApp.createCalendar('Conference Calendar');
  for (var i = 1; i < values.length; i++) {
    var session = values[i];
    var title = session[0];
    var start = joinDateAndTime_(session[1], session[2]);
    var end = joinDateAndTime_(session[1], session[3]);
    var options = {location: session[4], sendInvites: true};
    var event = cal.createEvent(title, start, end, options)
        .setGuestsCanSeeGuests(false);
    session[5] = event.getId();
  }
  range.setValues(values);

  // Store the ID for the Calendar, which is needed to retrieve events by ID.
  ScriptProperties.setProperty('calId', cal.getId());
}

/**
 * Creates a single Date object from separate date and time cells.
 *
 * @param {Date} date A Date object from which to extract the date.
 * @param {Date} time A Date object from which to extract the time.
 * @return {Date} A Date object representing the combined date and time.
 */
function joinDateAndTime_(date, time) {
  date = new Date(date);
  date.setHours(time.getHours());
  date.setMinutes(time.getMinutes());
  return date;
}

/**
 * Creates a Google Form that allows respondents to select which conference
 * sessions they would like to attend, grouped by date and start time.
 *
 * @param {Spreadsheet} ss The spreadsheet that contains the conference data.
 * @param {Array<String[]>} values Cell values for the spreadsheet range.
 */
function setUpForm_(ss, values) {
  // Group the sessions by date and time so that they can be passed to the form.
  var schedule = {};
  for (var i = 1; i < values.length; i++) {
    var session = values[i];
    var day = session[1].toLocaleDateString();
    var time = session[2].toLocaleTimeString();
    if (!schedule[day]) {
      schedule[day] = {};
    }
    if (!schedule[day][time]) {
      schedule[day][time] = [];
    }
    schedule[day][time].push(session[0]);
  }

  // Create the form and add a multiple-choice question for each timeslot.
  var form = FormApp.create('Conference Form');
  form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
  form.addTextItem().setTitle('Name').setRequired(true);
  form.addTextItem().setTitle('Email').setRequired(true);
  for (var day in schedule) {
    var header = form.addSectionHeaderItem().setTitle('Sessions for ' + day);
    for (var time in schedule[day]) {
      var item = form.addMultipleChoiceItem().setTitle(time + ' ' + day)
          .setChoiceValues(schedule[day][time]);
    }
  }
}

/**
 * A trigger-driven function that sends out calendar invitations and a
 * personalized Google Docs itinerary after a user responds to the form.
 *
 * @param {Object} e The event parameter for form submission to a spreadsheet;
 *     see https://developers.google.com/apps-script/understanding_events
 */
function onFormSubmit(e) {
  var user = {name: e.namedValues['Name'][0], email: e.namedValues['Email'][0]};

  // Grab the session data again so that we can match it to the user's choices.
  var response = [];
  var values = SpreadsheetApp.getActive().getSheetByName('Conference Setup')
     .getDataRange().getValues();
  for (var i = 1; i < values.length; i++) {
    var session = values[i];
    var title = session[0];
    var day = session[1].toLocaleDateString();
    var time = session[2].toLocaleTimeString();
    var timeslot = time + ' ' + day;

    // For every selection in the response, find the matching timeslot and title
    // in the spreadsheet and add the session data to the response array.
    if (e.namedValues[timeslot] && e.namedValues[timeslot] == title) {
      response.push(session);
    }
  }
  sendInvites_(user, response);
  sendDoc_(user, response);
}

/**
 * Add the user as a guest for every session he or she selected.
 * @param {object} user An object that contains the user's name and email.
 * @param {Array<String[]>} response An array of data for the user's session choices.
 */
function sendInvites_(user, response) {
  var id = ScriptProperties.getProperty('calId');
  var cal = CalendarApp.getCalendarById(id);
  for (var i = 0; i < response.length; i++) {
    cal.getEventSeriesById(response[i][5]).addGuest(user.email);
  }
}

/**
 * Create and share a personalized Google Doc that shows the user's itinerary.
 * @param {object} user An object that contains the user's name and email.
 * @param {Array<string[]>} response An array of data for the user's session choices.
 */
function sendDoc_(user, response) {
  var doc = DocumentApp.create('Conference Itinerary for ' + user.name)
      .addEditor(user.email);
  var body = doc.getBody();
  var table = [['Session', 'Date', 'Time', 'Location']];
  for (var i = 0; i < response.length; i++) {
    table.push([response[i][0], response[i][1].toLocaleDateString(),
        response[i][2].toLocaleTimeString(), response[i][4]]);
  }
  body.insertParagraph(0, doc.getName())
      .setHeading(DocumentApp.ParagraphHeading.HEADING1);
  table = body.appendTable(table);
  table.getRow(0).editAsText().setBold(true);
  doc.saveAndClose();

  // Email a link to the Doc as well as a PDF copy.
  MailApp.sendEmail({
    to: user.email,
    subject: doc.getName(),
    body: 'Thanks for registering! Here\'s your itinerary: ' + doc.getUrl(),
    attachments: doc.getAs(MimeType.PDF)
  });
}
  1. Select the menu item File > Save. Name your new script and click OK.

Format dates and set locale

The sample spreadsheet uses the U.S. date format (mm/dd/yyyy). To ensure that the examples work correctly in your locale, you may need to:

  1. Select File > Spreadsheet settings.
  2. Set the correct locale and time zone. Click Save settings.
  3. Reformat the dates in the spreadsheet to your local format. For example, in the UK you should use dd/mm/yyyy.

Try it out

  1. Switch back to your spreadsheet and reload the page.
  2. After a few seconds, a new custom menu, Conference, will appear. Click it and select Set up conference.
  3. A dialog box will appear and tell you that the script requires authorization. Click Continue. A second dialog box will then request authorization for specific Google services. Read the notice carefully, then click Allow.
  4. The script will take a few seconds to run. You'll know it's complete when the yellow bar at the top of the screen disappears.
  5. The Conference menu will now be gone, but you will see a new Form menu instead. Click it and select Go to live form.
  6. Fill out the form to indicate which sessions you'd like to attend at this fictional conference. If you have more than one Google account, use a different email address for this form than the account you used to create the script. (You don't need to log in to the second account; just type in the email address at the top.) When you're done, click Submit.

See the results

  1. Open your Google Calendar and navigate to the conference dates: May 15 and 16, 2013. You'll see an event for each session. If you used a different email address when you responded to the form, that address will have been invited as a guest for the sessions you selected.
  2. Open your Google Drive. You'll see a new Google Doc that lists the sessions you selected. If you used a different email address when you responded to the form, that address will be listed as an editor. Users other than the script owner will find a copy of the document in their Shared with me view.
  3. Try sharing the link to the form with other people. As they respond, you'll see the Google Calendar invites and Google Doc itineraries start to add up. If you were organizing a real conference, think about how much time this would save!

That's it! With only a small amount of code, you've added a custom menu item, programmatically built a Google Form, created Google Calendar events, and told a script to send Calendar invites and personalized Google Docs on your behalf.

Learn more

To continue learning about how to extend Google Forms, Calendar, and Docs with Apps Script, take a look at the following resources: