// Forms · Sheets · Apps Script

Route responses to different sheets by answer in Google Forms.

Use a form-bound onFormSubmit trigger and getItemResponses() to read answers and append rows to different sheets based on the value — without the undefined e.namedValues error that catches everyone the first time.

“I want to automatically send Google Form submissions to different tabs in a spreadsheet depending on what the respondent answered.”

The script

copy · paste · trigger

routeFormResponses.gs

Apps Script

// Form-bound onFormSubmit: route rows to sheets by department answer
// Trigger: from the Form editor > Extensions > Apps Script > Triggers (not the Sheet)
function onFormSubmit(e) {
  var responses = e.response.getItemResponses();
  var dept = '';
  var row = [];

  for (var i = 0; i < responses.length; i++) {
    var item = responses[i];
    if (item.getItem().getTitle() === 'Department') {
      dept = item.getResponse();
    }
    row.push(item.getResponse());
  }

  var ss = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID');
  var sheet = ss.getSheetByName(dept) || ss.getSheetByName('Other');
  sheet.appendRow(row);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why e.namedValues is undefined in a form-bound trigger

Google Apps Script offers two different trigger contexts for form submissions, and they expose different event objects. A sheet-bound trigger (installed from the spreadsheet's script editor) gives you e.values, an array of strings, and e.namedValues, an object keyed by question title. A form-bound trigger (installed from the form's script editor) gives you e.response, which is a FormResponse object. The two do not overlap.

If you copy a routing snippet written for one context and run it in the other, e.namedValues is simply undefined. There is no error about a missing property until you try to read a key off it, at which point you get 'Cannot read properties of undefined'. The fix is not defensive coding — it is using the right event object for the context you are actually in.

For routing by answer, the form-bound trigger is the right choice. It fires before the response even lands in the summary sheet, and e.response gives you programmatic access to every item with full metadata: question title, item type, and the raw response value all accessible without relying on column order.

Reading the routing answer with getItemResponses()

e.response.getItemResponses() returns an array of ItemResponse objects, one per question. Each has three methods you actually need: getItem().getTitle() to get the question text, getResponse() to get what the user answered, and getItem().getType() to distinguish a multiple-choice item from a text item when that matters.

In the snippet above, the loop does two things in one pass: it picks out the 'Department' answer to decide which sheet to write to, and it builds a flat row array from all responses. You could also call e.response.getResponseForItem(item) if you already have a reference to the specific form item, but title-matching is simpler when you just want one field for routing.

The first time I wired this up, I matched on getItem().getId() instead of the title, which meant I had to hard-code an opaque numeric ID and update it every time the form changed. Title matching is more brittle if questions get renamed, but for most forms it is the practical choice — just document the exact expected title string as a constant at the top of the script.

Installing the trigger correctly and handling missing sheets

Open the form (not the spreadsheet) in Google Forms, go to Extensions > Apps Script, and install the trigger there. In the trigger dialog, set the event type to 'On form submit'. This is what makes e.response available. If you install the same function as a trigger from the linked spreadsheet's script editor instead, you get the sheet-bound event object and e.response will be undefined.

The line ss.getSheetByName(dept) || ss.getSheetByName('Other') handles the case where a respondent picks an answer that does not match any sheet tab name. Without the fallback, getSheetByName returns null and appendRow throws. Create an 'Other' tab in the spreadsheet, or replace the fallback with a Logger.log call and an early return if you want unmatched responses to fail loudly during development.

Note that openById requires the spreadsheet ID from the URL, not the form ID. The form and its response sheet are separate files with separate IDs. If the script lives in the form, you cannot use SpreadsheetApp.getActiveSpreadsheet() — there is no active spreadsheet in that context. You must use openById with the explicit spreadsheet ID, which you can find in the sheet's URL between /d/ and /edit.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does my onFormSubmit trigger fire but e.namedValues is undefined?: You installed the trigger from the form's script editor, which gives you a form-bound event object. e.namedValues only exists on the sheet-bound event object. Use e.response.getItemResponses() instead — it gives you the same data through the FormResponse API.
Can I use SpreadsheetApp.getActiveSpreadsheet() inside a form-bound script?: No. When the trigger fires from the form context, there is no active spreadsheet. You must call SpreadsheetApp.openById('YOUR_SPREADSHEET_ID') with the ID from the linked spreadsheet's URL.
What happens if the respondent picks an answer that does not match any sheet name?: getSheetByName() returns null, and calling appendRow on null throws a TypeError. Add a fallback: ss.getSheetByName(dept) || ss.getSheetByName('Other'). Make sure the fallback tab actually exists in the spreadsheet.
Does this script work if the form has multiple-choice questions that allow more than one answer?: For checkbox questions (multiple selections), getResponse() returns an array, not a string. When you push that into the row array, it will appear as a comma-joined string in the cell. For the routing field specifically, use a multiple-choice (radio) or dropdown question so getResponse() reliably returns a single string you can match against a sheet name.