How to build a simple ticketing system with Google Forms and Google Sheets

What turns a request into a ticket

A ticket is a request with enough structure to be followed until it's closed. Here is one, as it will look once the sheet below is set up:

REQ-0042In progress

Printer on the 3rd floor keeps jamming

Requested by

sam@yourorg.com

Type

IT support

Priority

Normal

Owner

Alex

Due

Oct 28, 10:15

Last note

Replacement roller ordered

Design the request form

Ask for what the owner needs to start working, and nothing more. A typical request form has:

• The requester's email. In Settings › Responses, set Collect email addresses to Verified, which records the respondent's Google account and requires sign-in, or Responder input, which lets people type an address and suits requesters outside your organization.
• Request type, as a required dropdown. It drives routing (see how to send responses to different people) and reporting.
• Location or department, if the work depends on it.
• Priority, with short labels such as Urgent, Normal and Low. Explain them in the question's description (“Urgent: you can't work”) and keep the labels themselves short, so formulas can match them.
• Description, as a required paragraph question.
• Attachments, only if needed: file upload questions require respondents to sign in with a Google account, and the files are stored in the form owner's Google Drive.

Set up the ticket sheet

Link the form to a spreadsheet (Responses › Link to Sheets). Leave the form's columns as they are and add the ticket columns to their right. In this example, the form fills columns A to F (Timestamp, Email Address, Request type, Location, Priority, Description) and the ticket columns start at G. If your sheet already has tracking columns, for example from our first guide, keep them and change the letters in the formulas below to match.

1. Ticket ID (column G). In G1, enter:

   =ARRAYFORMULA(IF(ROW(A:A)=1,"Ticket ID",IF(A:A="","","REQ-"&TEXT(ROW(A:A)-1,"0000"))))

   Each request gets a number such as REQ-0042. The number follows the row, so never sort or delete rows in the response tab: use filter views to change what you see.
2. Owner (H). Type a name, or fill it from a routing table with the lookup formula in the routing guide, adjusting the column letters.
3. Status (I), as a dropdown (Data › Data validation) with four values. A blank status counts as New.
   • New: nobody has started on it.
   • In progress: the owner is working on it.
   • Waiting: blocked on the requester or someone else.
   • Closed: done, with a line in Notes about the outcome.
4. Due (J), set by priority. In J1:

   =ARRAYFORMULA(IF(ROW(A:A)=1,"Due",IF(A:A="","",A:A+IF(E:E="Urgent",4/24,IF(E:E="Normal",1,3)))))

   Urgent requests are due 4 hours after they're submitted, Normal ones a day later and Low ones after 3 days, in calendar time. An empty or unexpected Priority value gets the Low delay. Format the column as Date time.
5. Notes (K), with a short dated line each time something happens: “Oct 28, Alex: ordered a part”.
6. Highlight late tickets. Open Format › Conditional formatting, apply the rule to the range G2:K, choose Custom formula is and enter =AND($I2<>"Closed", $J2<>"", $J2<NOW()).
7. Create queues with filter views (Data › Filter views › Create new filter view): “Open” (Status is not Closed), “Waiting”, one per owner and one per request type. Each person can switch views without changing what the others see.

Formulas like these depend on your regional settings: if Google Sheets rejects a comma, use a semicolon.

Add a small dashboard

A second tab can count what matters. These formulas use the response tab's default name, Form Responses 1:

Add a chart if you like. What matters is that anyone can see the backlog at a glance, without scrolling through rows.

Keep requesters informed

Google Forms doesn't tell requesters when a ticket changes. A few habits cover it:

• Send respondents a copy of their response, so they have a record of what they asked.
• Reply from the owner's own inbox, using the email address the form collected, and add a line to Notes.
• Don't share the spreadsheet with requesters: it holds everyone's requests.

If requesters expect to reply to a shared thread that stays attached to the ticket, that's a help desk feature, not something a spreadsheet does.

Examples by type of request

Where a spreadsheet stops

The sheet stores tickets well. What it lacks are the parts of a ticketing system that act or remember on their own:

• No conversation thread. Replies to requesters live in personal inboxes, so the exchange isn't on the ticket.
• No timeline per ticket. Cell edit history and notes show changes, but not a clear sequence of who did what on one request.
• A fragile structure. Sorting or deleting rows breaks the ticket IDs, and typing in a formula column breaks it for every row.
• Calendar-time deadlines. Formulas ignore working hours unless you build that logic yourself.

For the day-to-day management a sheet asks for as volume grows, see what the spreadsheet method asks of your team.

Choose the right level of tooling

Starting with the sheet is reasonable: you learn which request types and fields you really need, and you can move to a tool later without changing the form.

Where Form Watchdog fits

Form Watchdog is not a help desk: it doesn't host email conversations with requesters or give them a portal. It adds the follow-up layer to the form you already use, so each response is tracked like a ticket:

• An owner for each response, set for the whole form or by an answer such as Request type
• A response deadline for each form, with reminders until the response is handled
• A first and a second overdue alert, to the assignee, the workspace owner, chosen contacts, Slack or a webhook
• One inbox for all your forms, with unhandled, overdue and handled responses
• Internal notes, snoozed reminders and a history of each response
• An outcome when a response is marked handled, such as Resolved, Contacted or No action needed, with an optional closing note
• Analytics on handling time and deadline performance

It keeps three states (unhandled, overdue and handled) rather than custom statuses such as Waiting. Your form stays as it is, and you can keep its spreadsheet for reporting.