The default Form Responses sheet that Google generates automatically works fine for a quick look, but it rarely matches the exact layout, column order, or derived fields a real workflow needs downstream.
This script attaches an onFormSubmit trigger that reads the event object's namedValues, pulls out only the fields you care about, and writes them into a separate Processed sheet in the order and format you choose.
Working from namedValues rather than the raw response range means the script keeps working correctly even if someone reorders questions in the form later, since fields are looked up by their question title rather than by column position.
By the end you will have a processed sheet that is decoupled from the form's exact structure and ready to feed other automations like a confirmation email or a task queue.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Form item | Processed column | Role |
|---|---|---|
| Timestamp | A | Copied from event |
| B | Respondent | |
| Topic | C | Routing field |
| Notes | D | Free text |
What It Does
On every form submission, the trigger function receives an event object containing namedValues, a map from each question's title to the array of answers submitted for it, and copies chosen fields into a new row on the Processed sheet.
Fields are read defensively with a helper that returns an empty string when a question was left blank or skipped through form branching logic, so a partially filled-out form never causes the trigger to throw.
Prerequisites
Create the Processed sheet ahead of time with a header row matching the fields you plan to extract, and make sure the form is already linked to a response spreadsheet in the same project.
You need to add the trigger through the Triggers menu as an installable On form submit trigger rather than a simple trigger, since simple triggers cannot access services like SpreadsheetApp in this context reliably.
Walkthrough
Paste the onFormSubmitToSheet function into the script editor, update the FIELD_MAP array to list the exact question titles you want pulled from namedValues, and save the project.
Open Triggers from the clock icon, add a new trigger for onFormSubmitToSheet set to run On form submit, and choose the correct form-linked spreadsheet as the event source.
Submit a test response through the live form and confirm a new row appears on the Processed sheet with the correct values in the correct columns.
Edge Cases
Checkbox questions return an array of selected values inside namedValues, so the script joins them into a single comma-separated string rather than writing an array object directly into a cell.
If a question's title is edited in the Form after the trigger is already set up, the FIELD_MAP entry referencing the old title stops finding a match and that column comes through blank until the map is updated to the new title.
Testing
Submit two test responses with different combinations of answered and skipped optional questions, and confirm the Processed sheet handles missing fields as empty strings rather than throwing an error.
Rename a question in the Form temporarily, submit another response, and confirm you can identify the resulting blank column and trace it back to the renamed field.
Hardening
Wrap the row-building and appendRow logic in try/catch and log failures to a separate error sheet, since a trigger failure on form submit does not surface an error to the person who just submitted the form.
Add a Processed timestamp column populated from the event object's own timestamp value rather than a fresh new Date() call, so the recorded time matches exactly when the form was actually submitted.
Variations
Chain this trigger directly into the send-confirmation-email tutorial so a personalized email goes out the moment the Processed row is written.
Write to more than one destination sheet based on an answer to a routing question, splitting submissions into different processed tabs depending on which option the respondent chose.
onFormSubmitToSheet.gs
onFormSubmitToSheet reads namedValues from the form submission event, resolves each mapped question defensively, and appends a row to a separate Processed sheet.
// Write selected form fields into a custom Processed sheet
function onFormSubmitToSheet(e) {
var PROCESSED_SHEET_NAME = 'Processed';
var FIELD_MAP = ['Full Name', 'Email Address', 'Department', 'Request Type'];
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(PROCESSED_SHEET_NAME);
var namedValues = e.namedValues;
var row = [new Date(e.values[0])];
for (var i = 0; i < FIELD_MAP.length; i++) {
var answers = namedValues[FIELD_MAP[i]];
if (!answers) {
row.push('');
} else if (answers.length > 1) {
row.push(answers.join(', '));
} else {
row.push(answers[0]);
}
}
sheet.appendRow(row);
}- Line 4: Listing question titles instead of column positions means the mapping keeps working even if someone reorders questions on the form.
- Line 7: namedValues maps each question's exact title to an array of submitted answers, which is the structure the rest of the function reads from.
- Line 9: Reading the timestamp from e.values[0] captures the moment of the actual submission rather than whenever the trigger happens to run.
- Line 12: A missing entry in namedValues means that question was skipped, so the row gets an empty string instead of the function throwing.
- Line 15: Joining multiple answers with a comma is what turns a checkbox question's array of selections into a single readable cell.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: on form submit to sheet
- 1Processed sheet created with the desired column layout
- 2Form already linked to a response spreadsheet
- 3FIELD_MAP updated with exact question titles from the form
- 4Installable On form submit trigger added through the Triggers menu
- 5Test submission confirmed to produce a correctly filled row
- 6Checkbox-question handling verified for comma-joined values
Frequently asked questions
The default sheet mirrors the form's exact question order and rarely matches the layout other automations downstream actually need.
The FIELD_MAP entry referencing the old title stops matching, so that column comes through blank until you update the map to the new title.
Yes, checkbox answers arrive as an array inside namedValues, and the script joins them into a single comma-separated string for the sheet cell.
Yes, branch on an answer to a routing question and pick a destination sheet accordingly, as described in the variations section.
Simple triggers cannot reliably access services like SpreadsheetApp in this context, so an installable On form submit trigger is required.
The field-reading helper returns an empty string for any missing question rather than throwing, so partially completed forms are handled gracefully.