In this article:

Google Forms Ticketing System: How to Create one in 2024

In this article, we will show you how to set up a Google Forms ticket system using Google Apps Script. Simply follow the process below. 

Setting up the Google Forms Ticketing System 

Follow the steps below to create a Google Forms ticketing system from form creation to section segregation and user navigation.

1. Start by Creating Your Google Form

Click the "+" button to initiate a new form. Name your form, for example, "Ticketing System".

google forms ticketing system

2. Insert a Multiple-Choice Question

Add a key multiple-choice question to determine user paths. For example, "Do you want to create a new ticket or close an existing ticket?" Include options for creating and closing tickets. Ensure this question is set as mandatory.

google forms ticket system

3. Segregate into Two Functional Sections

Click the icon with two overlapping rectangles to add new sections. Divide the form into two sections: "Create a New Ticket" and "Close an Existing Ticket."

Link each option to its respective section using "Go to section based on answer" in the first question.

4. Detail the 'Create a New Ticket' Section

In this section, insert input fields like 'Ticket Title', 'Customer Name', 'Email', 'Issue Description', and 'Assigned Agent'.

Configure the section to end with a submission of the form.

5. Configure the 'Close an Existing Ticket' Section

Set up fields for choosing an existing ticket to close and a text area for resolution details. You may need to manually update ticket IDs or use scripting for dynamic updates. 

6. Connect the Form with a Google Sheets Document

Link the form to a Google Sheets document for response tracking. Click the "Responses" tab in your form followed by the 'Link to Sheets.'

Review the linked spreadsheet.

Automating the Ticketing System with Google Sheets

Follow the process below to automate your ticketing system using formulas with Google Sheets. 

1. Implement Auto-Generated Ticket IDs in Google Sheets

In the linked Google Sheets, add a column for 'Ticket ID'. Use the formula `=ARRAYFORMULA(IF(ROW(B2:B)-1, "TID" & TEXT(ROW(B2:B)-1, "000"), ""))` in the first cell of this column to automatically generate ticket IDs.

2. Establish a Ticket Tracking System in Sheets

Create a new sheet within the same document named "Ticket Tracking Sheet".

Set up columns like 'Ticket ID', 'Opened Tickets', 'Closed Tickets', and 'Active Tickets'.

3. Apply Status Tracking Formulas for Tickets

Use FILTER formulas in the 'Ticket Tracking Sheet' to categorize tickets.

In 'Opened Tickets' (B2), use =FILTER('Form Responses 1'!A2:A, 'Form Responses 1'!C2:C = "Create a new ticket").

In 'Closed Tickets' (C2), use =FILTER('Form Responses 1'!A2:A, 'Form Responses 1'!C2:C = "Close an existing ticket").

In 'Active Tickets' (D2), use =FILTER(A2:A, ISNA(MATCH(A2:A, C2:C, 0))).

Automating the Form with Google Apps Script

Follow the process below to identify unique IDs for your form and questions, and create dynamic form updates based on data from Google Sheets.

1. Access the Scripting Area of Google Forms

In your Google Form, click the three vertical dots in the top right corner and select "Script editor" to open Google Apps Script.

2. Identify Unique IDs for Form and Specific Questions

'YOUR_FORM_ID': Located in the URL of your Google Form, between '/d/' and '/edit'.

'YOUR_QUESTION_ID':

In the Apps Script editor, type and save the logQuestionIds function:

  ```javascript

  function logQuestionIds() {

    var form = FormApp.getActiveForm();

    var items = form.getItems();

    for (var i = 0; i < items.length; i++) {

      Logger.log(items[i].getTitle() + ': ' + items[i].getId());

    }

  }

  ```

To run, select the function 'logQuestionlds' and click the play button.

Confirm its success in the execution log below and find the execution marked 'Execution completed'. Find and note the question ID to your specific question. In this case, note the ID of 'Which ticket would you like to close'.

3. Script Creation for Dynamic Ticket Form Updates

Use the following script template in the Apps Script editor to dynamically update form elements based on the data in Google Sheets:

  ```javascript

  function updateTicketForm() {

    var form = FormApp.openById('YOUR_FORM_ID'); // Replace with your form's ID

    var sheet = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID').getSheetByName('Form responses 1'); // Replace with your spreadsheet's ID

    var data = sheet.getDataRange().getValues();

    var ticketIds = data.map(function(row) { return row[0]; });

    var ticketQuestion = form.getItemById('YOUR_QUESTION_ID').asListItem(); // Replace with your question's ID

    ticketQuestion.setChoiceValues(ticketIds);

  }

  ```

Replace 'YOUR_FORM_ID', 'YOUR_SPREADSHEET_ID', and 'YOUR_QUESTION_ID' with the actual IDs. Save the script and run it by selecting `updateTicketForm` and clicking the play icon.

While the script is running, you will see a small dialog box with a progress spinner indicating that the script is executing. The logs will display the progress and any output or error messages generated during script execution.

4. Set Up a Trigger for the Script

In the Apps Script editor, click the clock icon to open "Triggers."

Click "+ Add Trigger." Configure the function to 'updateTicketForm', set the deployment as 'Head', event source as 'From form', and event type as 'On form submit'. Click "Save."

5. Test the Entire System

Submit a test entry and verify if the script updates the dropdown question with new ticket IDs.

We hope that you now have a better understanding of how to create a Google Forms Ticketing System using Google Apps Script. If you enjoyed this article, you might also like our article on how to create a sign up sheet in Google Forms or our article on setting up Paypal in Google Forms. If you want to know how to set up email notifications of each response to Google Forms, we also suggest checking out our detailed guide. 

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!