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.
Follow the steps below to create a Google Forms ticketing system from form creation to section segregation and user navigation.
Click the "+" button to initiate a new form. Name your form, for example, "Ticketing System".
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.
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.
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.
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.
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.
Follow the process below to automate your ticketing system using formulas with 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.
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'.
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))).
Follow the process below to identify unique IDs for your form and questions, and create dynamic form updates based on data from Google Sheets.
In your Google Form, click the three vertical dots in the top right corner and select "Script editor" to open Google Apps Script.
'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'.
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.
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."
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.
