Route form responses to the right team

Northwind has one general enquiry form on the website, but three teams behind it: design, dev, and ops. When everything lands in a shared inbox, the first ten minutes of every morning is spent forwarding messages around. By the time the right person sees them, the easy ones could already have been answered.

The trick is to ask the respondent to self-route. A “What’s this about?” dropdown carries the routing decision, and this script reads that answer the moment a response comes in and emails the matching team directly. The shared inbox keeps a copy for the audit trail; the right person gets the work the moment it arrives.

What you’ll need

• A Google Form with a question titled What's this about? whose options match the keys in the ROUTES map below. Add or rename entries to match your own teams.
• The form must be linked to a response sheet — the trigger is set up on the sheet, not the form, so changes to the form itself don’t break it.
• Each routing address needs to be a real distribution list or shared inbox the team monitors. Sending to a single person’s address creates a single point of failure when they go on holiday.

The script

// Routing table. Keys MUST match the dropdown options on the form exactly —
// extra spaces or different capitalisation will fall through and the
// submission will land nowhere.
const ROUTES = {
  'Design help': '[email protected]',
  'Dev question': '[email protected]',
  'Ops or billing': '[email protected]',
};

// Title of the question that carries the routing decision. Keep this in
// sync with the form.
const ROUTING_QUESTION = "What's this about?";

/**
 * Installable form-submit trigger. Reads the routing question and sends
 * the full submission to the matching team.
 *
 * @param {GoogleAppsScript.Events.SheetsOnFormSubmit} e The submit event.
 */
function onFormSubmit(e) {
  // Guard against being run by hand from the editor — there is no event
  // object then, and nothing useful to do.
  if (!e || !e.namedValues) {
    Logger.log('No event payload — run this from a form-submit trigger.');
    return;
  }

  // 1. Pull the routing answer. namedValues maps question title -> array
  //    of answers; for single-choice questions there is one entry.
  const topic = e.namedValues[ROUTING_QUESTION]?.[0];
  const owner = ROUTES[topic];

  // 2. If the answer didn't match any route, log it and stop. This
  //    catches typos in the routes table and any future dropdown
  //    options nobody remembered to add here.
  if (!owner) {
    Logger.log('No route for topic: ' + topic + ' — submission left in the sheet only.');
    return;
  }

  // 3. Build a plain-text body from every answer. One line per
  //    question, in the order the form delivered them.
  const body = Object.entries(e.namedValues)
    .map(([k, v]) => k + ': ' + v[0])
    .join('\n');

  // 4. Send it. Subject leads with "New form submission" so any
  //    inbox filter can spot these at a glance.
  GmailApp.sendEmail(owner, 'New form submission: ' + topic, body);
  Logger.log('Routed submission to ' + owner + ' (topic: ' + topic + ').');
}

How it works

1. The installable trigger fires on every form submission and receives the event object with the answers in e.namedValues.
2. The script guards against being run manually from the editor, where the event object is missing.
3. It reads the answer to the routing question and looks it up in ROUTES. Any topic with no matching key is logged and ignored — the row stays in the sheet, but no team gets pinged for a topic the script doesn’t know.
4. It builds the email body by joining every key: value pair on its own line. Plain text keeps it scannable on mobile.
5. GmailApp.sendEmail delivers it to the matching team address. The shared inbox the form normally writes to still receives its own copy, so nothing is lost.

Example run

A respondent submits the form with:

Field	Value
Name	Priya Patel
Email	[email protected]
What’s this about?	Dev question
Message	The webhook returns 502 every Friday at 5pm.

The script reads Dev question, finds [email protected] in ROUTES, and sends:

To: [email protected] Subject: New form submission: Dev question

Name: Priya Patel Email: [email protected] What’s this about?: Dev question Message: The webhook returns 502 every Friday at 5pm.

Design and ops get nothing. The Friday-evening 502 lands with the team that can actually fix it.

Trigger it

This is an installable form-submit trigger, set up on the response sheet — not on the form itself. Installable triggers can send mail; simple ones cannot.

1. From the response sheet, open Extensions → Apps Script.
2. In the editor, open Triggers (the clock icon).
3. Add trigger, choose onFormSubmit, event source From spreadsheet, event type On form submit.
4. Approve the authorisation prompt the first time. Send a test submission and check both the team inbox and the execution log.

Watch out for

• The routing key has to match the dropdown option exactly — character for character. Spelling changes on the form must be mirrored in ROUTES, or submissions silently stop routing.
• Send to team addresses, not individuals. Holidays, handovers, and people leaving Northwind will otherwise drop submissions on the floor.
• Submissions with no routing answer (the question is optional, or the topic doesn’t match any route) are logged but not emailed. Skim the execution log occasionally to spot a dropdown option you forgot to add.
• For more nuanced routing — say, urgency from the message text — classify the message with an AI step before looking up the owner, rather than hand-coding more rules into ROUTES.
• GmailApp.sendEmail counts against your daily quota (100 for free accounts, 1500 for Workspace). High-traffic forms should batch into a digest or use MailApp with a service account.