Build a form-based help-desk ticket system

A support request that arrives as a plain email is easy to lose. There is no reference number, no status, and no quick way to see what is still open — so something always slips through, and the customer is left wondering if anyone saw it. Northwind wants every request to become a proper ticket the moment it lands.

This automation turns a Google Form into a lightweight help desk. When someone submits the form, a trigger mints a ticket ID, logs the request to a tracking sheet with a status, and emails the submitter a confirmation. No ticketing software, no licence — just a form, a sheet, and one function.

What you’ll need

• A Google Form for support requests, with fields for the submitter’s Email and a Subject (the field names must match the keys used below).
• A Tickets sheet with the columns id, submitter, subject, status, createdAt in row 1.
• The tracking spreadsheet’s ID, set in TICKETS_SHEET_ID below.
• A Gmail account to send confirmations from — the script uses GmailApp under your authorisation.

The trigger

// The spreadsheet that holds the ticket log.
const TICKETS_SHEET_ID = '1abcTicketsId';

// Studio name used to sign the confirmation email.
const STUDIO_NAME = 'Northwind';

/**
 * Runs on every form submission. Mints a ticket ID, logs the
 * request to the Tickets sheet, and emails a confirmation.
 *
 * Wire this to the form's "On form submit" trigger.
 *
 * @param {Object} e  The form-submit event, with namedValues.
 */
function onFormSubmit(e) {
  // 1. Guard: make sure the submission has the fields we need.
  if (!e || !e.namedValues || !e.namedValues.Email) {
    Logger.log('Submission missing an Email field — skipped.');
    return;
  }

  // 2. Mint a short, unique ticket ID from the current timestamp.
  const id = `NW-${Date.now().toString(36).toUpperCase()}`;

  // 3. Pull the submitter's email and subject from the response.
  const submitter = e.namedValues.Email[0];
  const subject = e.namedValues.Subject ? e.namedValues.Subject[0] : '(no subject)';

  // 4. Log the new ticket as an open row.
  SpreadsheetApp.openById(TICKETS_SHEET_ID).getSheets()[0]
    .appendRow([id, submitter, subject, 'open', new Date()]);

  // 5. Confirm receipt to the submitter.
  GmailApp.sendEmail(
    submitter,
    `Ticket ${id} received`,
    `We've logged your ticket. We'll reply within 4 working hours.\n\n` +
    `— ${STUDIO_NAME}`
  );
  Logger.log('Logged ticket ' + id + ' for ' + submitter);
}

How it works

1. onFormSubmit runs automatically whenever the form is submitted, receiving an event object e with the response in namedValues.
2. It first checks the submission actually has an Email field — without one there is nobody to confirm to, so it logs a note and stops.
3. It mints a ticket ID by taking the current timestamp, converting it to base-36, and upper-casing it — short, human-readable, and unique because no two submissions share a millisecond.
4. It reads the submitter’s email and the subject from namedValues, falling back to (no subject) if the subject field is empty.
5. It appends a row to the Tickets sheet with the ID, submitter, subject, a status of open, and the current time.
6. It emails the submitter a confirmation that quotes the ticket ID and sets a response expectation, signed with the studio name.

Example run

A customer submits the form with email [email protected] and subject “Invoice PDF won’t open”. A new row appears in the Tickets sheet:

id	submitter	subject	status	createdAt
NW-LZP4K9QX	[email protected]	Invoice PDF won’t open	open	2025-07-25 09:14

And the customer receives:

Subject: Ticket NW-LZP4K9QX received

We’ve logged your ticket. We’ll reply within 4 working hours.

— Northwind

Support then works the sheet, changing status from open to closed as tickets are resolved.

Trigger it

The function must be wired to the form’s submit event:

1. Paste the script into the Apps Script project bound to the form (or a standalone project with access to the form).
2. Open the Triggers panel (the clock icon) and click Add Trigger.
3. Choose onFormSubmit, event source From form, event type On form submit.
4. Save, and approve the authorisation prompt the first time.
5. Submit a test response and confirm a new row appears and the email arrives.

Watch out for

• Field names must match exactly. The script reads namedValues.Email and namedValues.Subject — if the form questions are named differently, those lookups return undefined. Rename the questions or update the keys.
• The ID is unique per millisecond, not globally. Two genuinely simultaneous submissions are extremely unlikely to collide, but if absolute uniqueness matters, append a short random suffix.
• GmailApp.sendEmail counts against a daily quota — 100 messages on a consumer account, more on Workspace. A sudden flood of submissions can hit it.
• A confirmation goes to whatever address was typed into the form. A mistyped email simply bounces; the ticket is still logged, so support can follow up.
• This logs and acknowledges tickets but does not assign or escalate them. Add an assignee column and a follow-up script if you need routing or SLA chasing.