Apps Script trigger routing Google Form responses into different Google Sheets tabs by answer

Route Google Form Responses to Different Sheets by Answer

by

If a Google Form question decides which team should see a response — department, request type, priority — you don’t have to sort rows by hand. An Apps Script trigger bound to the response spreadsheet can read that one answer the moment the form is submitted and file the whole row into the matching sheet tab automatically, creating the tab the first time it’s needed.

This only works reliably with a script, not a formula: Sheets has no native “if this answer, then write to a different tab” setting, and FILTER/QUERY formulas can show a filtered view but can’t physically move a row into another tab. Below is a script that does this, tested against 15 assertions in a sandbox before publishing, plus the exact trigger setup and the errors you’re most likely to hit.

Which Method Should You Use?

There are three real ways to do this. Pick based on whether you need checkbox (multi-select) questions handled, whether you need a separate spreadsheet file (not just a tab), and how much setup you’re willing to do.

This is one of several trigger-based automations you can wire into a response spreadsheet. If you need something simpler — watching a single cell and emailing yourself the moment it changes, rather than routing whole form rows — see an onEdit trigger that emails you when a specific cell changes.

Approach Setup time Handles checkbox / multi-select answers Can target a separate spreadsheet file Cost
Apps Script installable trigger (this guide) ~10 min, one-time Yes — files into every matching sheet Yes, via SpreadsheetApp.openById() Free
FILTER / QUERY formulas ~5 min Awkward — formulas evaluate the whole range and can’t cleanly split comma-joined checkbox values No — reading across files needs IMPORTRANGE, and formulas can’t write rows into another file at all Free
Marketplace add-on (e.g. “Answer Router,” “Form Response Control”) ~2 min install Varies by add-on — check its listing Some do, check current listing Free tier limited on most; paid tiers vary — check current pricing in Google Workspace Marketplace before installing

Use the script if you need checkbox answers to file into more than one sheet, want no recurring cost, or need rows to land in a completely separate spreadsheet file. Use a formula view if you only need a live filtered look at the data, not physically separate tabs, and don’t want to touch the script editor. Use a marketplace add-on only if you won’t touch code at all and the vendor’s free tier already covers your response volume — you’re trading setup time for giving a third party edit access to your spreadsheet.

This table only compares routing methods for this one task. For a broader decision framework across all of Sheets’ automation options — not just form routing — see the full Sheets automation checklist.

Set Up the Installable Trigger

This only works as an installable trigger — a function simply named onFormSubmit in a script bound to a Sheet does not run automatically the way onOpen or onEdit do. You have to wire it up once from the Triggers page.

  1. Open the spreadsheet that collects your form’s responses (not the form itself).
  2. Go to Extensions > Apps Script.
  3. Delete any starter code and paste in the script from the next section.
  4. Edit the three constants at the top — ROUTING_QUESTION, ROUTING_MAP, DEFAULT_SHEET — to match your form’s actual question title and the sheet names you want.
  5. Save the project (Ctrl/Cmd+S) and give it a name.
  6. In the left sidebar, click the clock icon (Triggers).
  7. Click + Add Trigger in the bottom-right corner.
  8. Set: Function to run = onFormSubmitRouter, Event source = “From spreadsheet”, Event type = “On form submit.” Click Save.
  9. Google will show an authorization screen the first time — choose your account, click Advanced, then “Go to [project name] (unsafe),” then Allow. This warning appears for any script you write yourself; it’s expected, not a sign something’s wrong.
  10. Submit a real test response to your form and confirm the destination tab appears with your row in it.

The Routing Script

Paste this into the script editor as described above. The routing decision is split into its own function, pickDestinationSheets, deliberately — see “What I Verified” below for why that split matters.

/**
 * Routes each new Google Form response to a sheet tab chosen by one of
 * the form's answers. Bind this script to the spreadsheet that collects
 * your form responses (Extensions > Apps Script from the Sheet, not the Form).
 */

// Exact title of the form question used to decide the destination sheet.
var ROUTING_QUESTION = 'Department';

// Map of answer text -> destination sheet name. Matched case-insensitively
// and with whitespace trimmed, so 'Sales', ' sales ', and 'SALES' all match.
var ROUTING_MAP = {
  'Sales': 'Sales Leads',
  'Support': 'Support Tickets',
  'Billing': 'Billing Issues'
};

// Where blank or unmapped answers go.
var DEFAULT_SHEET = 'Uncategorized';

// The sheet Google Forms writes raw responses into. This is the default
// name unless you renamed it.
var SOURCE_SHEET_NAME = 'Form Responses 1';

function onFormSubmitRouter(e) {
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = ss.getSheetByName(SOURCE_SHEET_NAME);

  // Read the header from the real sheet rather than from
  // Object.keys(e.namedValues) — namedValues key order isn't documented
  // to match column order, but a fresh getRange() read always does.
  var headerRow = sourceSheet.getRange(1, 1, 1, sourceSheet.getLastColumn()).getValues()[0];

  // e.values IS documented to match the sheet's column order, so it's
  // safe to copy straight into the destination row.
  var rowValues = e.values;

  var rawAnswer = (e.namedValues[ROUTING_QUESTION] && e.namedValues[ROUTING_QUESTION][0]) || '';
  var destinations = pickDestinationSheets(rawAnswer, ROUTING_MAP, DEFAULT_SHEET);

  destinations.forEach(function (name) {
    var destSheet = ss.getSheetByName(name);
    if (!destSheet) {
      destSheet = ss.insertSheet(name);
      destSheet.appendRow(headerRow);
    }
    destSheet.appendRow(rowValues);
  });
}

// Pure function, no Google services — this is what got unit tested
// outside Apps Script. See "What I Verified" below.
function pickDestinationSheets(answer, routingMap, defaultSheet) {
  if (answer === undefined || answer === null) return [defaultSheet];
  var raw = String(answer).trim();
  if (raw === '') return [defaultSheet];

  var tokens = raw.split(',')
    .map(function (s) { return s.trim().toLowerCase(); })
    .filter(function (s) { return s !== ''; });

  var matched = [];
  Object.keys(routingMap).forEach(function (key) {
    if (tokens.indexOf(key.toLowerCase()) !== -1) {
      var dest = routingMap[key];
      if (matched.indexOf(dest) === -1) matched.push(dest);
    }
  });

  return matched.length ? matched : [defaultSheet];
}

How This Handles Checkbox (Multi-Select) Questions

If ROUTING_QUESTION points at a checkbox question instead of a dropdown or multiple-choice question, Google Forms writes every selection into one cell as a comma-joined string — for example, a response of “Sales” and “Billing” both checked lands in the sheet as Sales, Billing. pickDestinationSheets splits on commas, trims and lowercases each piece, and matches each one independently against ROUTING_MAP. A response that matches two rules gets appended to both destination sheets, in full — it isn’t split or truncated. An answer that matches nothing recognizable, or an empty answer, falls back to DEFAULT_SHEET so no response silently disappears.

Routing to a Separate Spreadsheet Instead of a Tab

The queue for this only needed different tabs within one spreadsheet, but the same script extends to separate spreadsheet files with one change: replace ss.getSheetByName(name) / ss.insertSheet(name) with calls against SpreadsheetApp.openById('DESTINATION_SPREADSHEET_ID') instead of ss. Because this trigger is installable, it runs with the authorization of the account that created it — Google’s own documentation confirms installable triggers “run with the authorization of the user who created the trigger” — so it can write to any spreadsheet that account can already edit, not just the one the script is bound to. I have not tested this variant against a live second spreadsheet in this article; treat it as a documented extension, not a verified one.

Common Errors and Fixes

What you see Why it happens Fix
Nothing happens after you submit a test response — no new tab, no new row Naming a function onFormSubmit doesn’t make it run automatically in a script bound to a Sheet. Only onOpen and onEdit are automatic “simple triggers” there — form submissions need the installable trigger you add manually. Open the Triggers page and confirm an entry exists with Function = onFormSubmitRouter, Event source = “From spreadsheet,” Event type = “On form submit.”
Authorization is required to perform that action. in the Executions log This is a documented Apps Script error: a trigger running in the background can’t display an authorization prompt. It shows up if the script started needing a new permission (for example, after you edited it) after the trigger was already created. Open the script editor and run onFormSubmitRouter manually once to go through the consent screen again, then resubmit the form.
Rows you expected to route correctly all land in Uncategorized ROUTING_QUESTION doesn’t exactly match the live question title. Capitalization and surrounding spaces are normalized by the script, but a reworded question (e.g. “Team” vs. “Department”) is not. Copy the exact question title from the form, or from the header cell in “Form Responses 1,” into ROUTING_QUESTION.
It worked for weeks, then rows stopped arriving with no visible error on your end Apps Script emails “Summary of failures for Apps Script” from noreply-apps-scripts-notifications@google.com when an installable trigger fails repeatedly, and a trigger that keeps failing can end up effectively disabled. Check that address in your inbox first — the email links directly to the failing trigger so you can reconfigure or re-enable it.
Service invoked too many times: ... A daily Apps Script quota was exceeded for some service the project uses. This script’s own sheet writes don’t touch a metered quota, so this is more likely if you’ve extended it to also call MailApp or similar — check developers.google.com/apps-script/guides/services/quotas for current limits.

What I Verified (and What I Didn’t)

I ran pickDestinationSheets and the full onFormSubmitRouter orchestration in a Node.js sandbox against 15 assertions across two test files: routing a single exact-match answer, a comma-joined checkbox answer matching two rules, an unmapped answer, a blank answer, an undefined answer, case and whitespace noise, duplicate matches collapsing to one destination, a routing question missing from the event object entirely, a new destination sheet being created with the correct header row sourced from the real sheet (not from object-key order), and an existing destination sheet being reused without a duplicate header row. All 15 passed.

I also re-confirmed today (August 7, 2026) against Google’s current Apps Script documentation: the shapes of e.namedValues and e.values in the form-submit event object, that a Sheets-bound script needs the installable “On form submit” trigger rather than a simple trigger, that installable triggers run with the authorization of whoever created them, the exact wording of the “Authorization is required to perform that action” and “Service invoked too many times” errors from Google’s troubleshooting guide (last updated 2026-07-22 per that page), and current quota numbers — 20 triggers per user per script, 90 minutes/day of trigger runtime on consumer accounts versus 6 hours/day on Workspace accounts, and a 6-minute cap per script execution.

What I could not verify: I don’t have a Google Workspace or consumer account wired to a live Form, so I never watched the Triggers page register a real “On form submit” event, never saw the OAuth consent screen render, and never confirmed a new tab physically appears in a live spreadsheet after a real submission. The sandbox tests prove the routing and orchestration logic is correct against a mock of the Sheets API; they don’t substitute for watching it run once in your own account, which is why the setup steps above tell you to submit a real test response before trusting it.

FAQ

Can I route responses to a completely different spreadsheet file, not just a different tab?
Yes, conceptually — swap the ss.getSheetByName() / ss.insertSheet() calls for the same calls against SpreadsheetApp.openById('OTHER_SPREADSHEET_ID'). See “Routing to a Separate Spreadsheet” above; this variant wasn’t tested live in this article.

What happens if a checkbox answer matches two of my routing rules at once?
The full row is appended to every matching destination sheet — it’s duplicated across sheets, not split or truncated. This is covered by the “checkbox multi-select” test case above.

Does this approach work if my Apps Script project is bound to the Form instead of the Sheet?
Not as written — a Form-bound script reads answers differently, through FormResponse.getItemResponses() rather than the e.namedValues/e.values event shape used here. That’s a different code path this article doesn’t cover or test; if your script lives on the Form, you’d need to adapt the routing logic (which is still reusable) to that API instead. For more Apps Script patterns beyond routing — scheduled digests, data cleanup, and more — see the Apps Script automation hub.

Leave a Comment