Google Form Responses to Different Sheets: Split by Class, Answer or Value

Updated November 11, 2026 · ~10 min read

"Google Form responses to different sheets" is a routing problem: one form collects answers from several classes, campuses or departments, and you want each group's rows to land in its own tab. Google Forms will not do this — a form links to exactly one response sheet. But there are three reliable ways to split the data downstream, with very different trade-offs. This guide gives runnable versions of each and the specific failures people hit.

The hard rule: One form → one linked response sheet. You cannot point a single form at two destination sheets natively. Every split happens after the data lands, either with formulas, a script, or cross-file imports.

What "different sheets" means (three different asks)

People mean	Example	Best method
Different tabs in the same spreadsheet, each filtered	Tab "Class A", tab "Class B"	QUERY / FILTER formulas
Physical rows copied into separate tabs (independent of the source)	Per-class records that editors can annotate	Apps Script router
Different spreadsheets entirely (other accounts / drives)	Each campus owns its own file	IMPORTRANGE or script + Drive permissions

Pick before you build: the formula approach is a live view that changes when the source changes; the script approach writes independent rows that can be edited without touching the raw response tab. They are not interchangeable.

Method 1 — QUERY / FILTER tabs (no code, live view)

Create a new tab named Class A and put this in A1 (assuming the linked response tab is Form Responses 1 and the grouping question is column C):

=QUERY('Form Responses 1'!A:Z,
  "select * where Col3 = 'Class A'", 1)

Or with FILTER:

=FILTER('Form Responses 1'!A:Z,
  'Form Responses 1'!C:C = "Class A")

Repeat with a different literal for each group. QUERY uses Col1, Col2… notation (not A/B/C) and the third argument 1 keeps one header row. Advantages: zero code, always current. Limits: the tabs are read-only views — you cannot type into them without the formula being overwritten — and a large response sheet with a dozen QUERY tabs recalculates slowly.

Mixed-type trap: QUERY infers one type per column from the majority of values. If a column mixes numbers and text, the minority becomes blank. Coerce the column to text in the source, or use FILTER instead, which has no type inference.

Method 2 — Apps Script router (writes real rows)

When each group needs its own editable tab, use an installable form-submit trigger that appends the row to the matching tab. Bind the script to the response spreadsheet (Extensions → Apps Script).

const SHEET_ID  = "YOUR_RESPONSE_SPREADSHEET_ID";
const GROUP_COL = 2;   // 0-based index of the grouping answer in itemResponses
const HEADERS = ["Timestamp", "Email", "Class", "Q1", "Q2"];

// Put this script in the FORM Script editor, then run install() once.
function install() {
  const form = FormApp.getActiveForm();
  const exists = ScriptApp.getProjectTriggers()
    .some(function (t) { return t.getHandlerFunction() === "route"; });
  if (!exists) {
    ScriptApp.newTrigger("route").forForm(form).onFormSubmit().create();
  }
}

function route(e) {
  const ss = SpreadsheetApp.openById(SHEET_ID);
  const res = e.response;
  const items = res.getItemResponses();
  const answers = items.map(function (it) {
    const v = it.getResponse();
    return Array.isArray(v) ? v.join(", ") : String(v == null ? "" : v);
  });
  const group = String(answers[GROUP_COL] || "Unassigned")
    .replace(/[^A-Za-z0-9 _-]/g, "").trim().slice(0, 80);
  const tab = "Class " + group;

  let sh = ss.getSheetByName(tab);
  if (!sh) {
    sh = ss.insertSheet(tab);
    sh.appendRow(HEADERS);
    sh.setFrozenRows(1);
  }

  const id = res.getId();
  const props = PropertiesService.getScriptProperties();
  if (id && props.getProperty("routed_" + id)) return; // no duplicates

  sh.appendRow([res.getTimestamp(), res.getRespondentEmail() || ""].concat(answers));
  if (id) props.setProperty("routed_" + id, "1");
}

Two things to get right. Binding: reading e.response requires a form-bound installable trigger, so put this script in the form's Script editor and open the spreadsheet by ID with SpreadsheetApp.openById(). Run install() once to authorise; the guard prevents duplicate triggers on re-runs. Column index: GROUP_COL counts only questions, not the Timestamp/Email values prepended in appendRow — check your form's question order and set HEADERS to match.

Also note the sanitising regex: Sheets tab names cannot contain : \ / ? * [ ] and are capped at 100 characters, so an answer like "Class A/B" must be cleaned before insertSheet().

Method 3 — IMPORTRANGE across separate spreadsheets

If each campus needs its own file (and own permissions), keep the single response sheet as the source of truth and pull a filtered view into each destination file:

=QUERY(
  IMPORTRANGE("SHEET_ID", "'Form Responses 1'!A:Z"),
  "select * where Col3 = 'Campus North'", 1)

First open the destination file and allow access when prompted (one-time per source/target pair). IMPORTRANGE is read-only and refreshes on its own schedule, not instantly. For a strict permission boundary — where Campus North must not see Campus South rows at all — a filtered import is not enough, because the source sheet still holds everything; use the script to write only that group's rows into a separate file instead.

Which method should you use?

Need	Use
Live filtered views, no editing needed	QUERY / FILTER tabs
Independent editable rows per group	Apps Script router
Separate files with their own sharing	IMPORTRANGE (view) or script (strict isolation)
Strict data isolation between groups	Script writing to separate spreadsheets only

Google Form responses to different sheets in 6 steps

Troubleshooting

QUERY returns #VALUE! or blank cells. Mixed types in a column, or you used A/B/C instead of Col1/Col2/Col3 in the query string.
New questions do not appear in the tabs. Your formula ranges A:Z or HEADERS are fixed; widen the range and update the header row when the form changes.
Typing into a QUERY tab breaks it. Formula tabs are views; edit the source or use the script method for editable copies.
Rows land in the wrong group. GROUP_COL is off by one — it indexes questions only, not Timestamp/Email.
"Invalid sheet name" from insertSheet(). The answer contains a forbidden character or exceeds 100 chars; sanitise as shown.
Duplicate rows. More than one installable trigger, or a manual re-run. Keep the response-ID guard and check the Triggers panel.
IMPORTRANGE shows #REF!. Access not yet allowed, wrong sheet ID, or the source tab was renamed. Re-authorise and verify the exact tab name including quotes.
Someone deleted the linked response tab. Unlink and relink the form; the router will then need its sheet reference repaired too.

Decide split type

Live view, editable rows, or separate files with their own permissions.

Pick the grouping question

A stable dropdown (Class / Campus / Department) — avoid free text.

Choose a method

QUERY for views, Apps Script for rows, IMPORTRANGE for other files.

Build it

Create the formula tabs or install the single form-bound trigger.

Submit one test response per group

Verify tab creation, header row, column order and no duplicates.

Watch the form's evolution

Adding a question changes column count — update ranges and headers together.

Frequently asked questions

Can a Google Form send responses to different sheets?

Not directly. One form links to exactly one response sheet. To split by class, campus or answer, create filtered tabs with QUERY/FILTER, write rows to group tabs with an Apps Script trigger, or pull filtered views into separate files with IMPORTRANGE.

How do I sort Google Form responses into different tabs automatically?

Use =QUERY('Form Responses 1'!A:Z, "select * where Col3 = 'Class A'", 1) in each tab for a live view. If you need editable, independent rows, use a form-bound onFormSubmit trigger that appends the row to the tab matching the grouping answer.

Why does my QUERY tab show blank values?

QUERY infers a single data type per column; when a column mixes numbers and text the minority values become blank. Coerce the column to text or switch to FILTER, which does not infer types. Also remember QUERY uses Col1/Col2 notation, not A/B/C.

Can I send each class's responses to a completely separate spreadsheet?

Yes. Use IMPORTRANGE with a QUERY in each destination file for a read-only view, or have an Apps Script open each destination spreadsheet by ID and append only that group's rows — the latter is required when groups must not see each other's data.

What happens when I add a new question to the form?

The response sheet gains a column. Formula ranges like A:Z may need widening, and a script with a fixed HEADERS array will misalign the new answer. Update ranges and headers together, then submit a test response.

Google Form converters

ExamParser has a dedicated page for each export format. Word for an editable document, PDF for a paginated archive copy, CSV for raw data, Excel for a working spreadsheet, and Google Sheets for a live linked sheet.

GGoogle Form to Word(.docx) GGoogle Form to PDF GGoogle Form to CSV GGoogle Form to Excel GGoogle Form to Google Sheets

Related guides

Keep one clean source of truth

Routing only works when the underlying data is consistent. Export the question structure and responses as structured, deterministic data — columns fixed, answer key included — then split, sync and report on it however you need. No AI rewriting.

Open the Google Form to Word converter →