Create an Issue Tracking System with Google Form and Spreadsheet

Based on the solution in this how-to you will be able to build a Ticketing System, also known as Issue Tracking System, using Google Form, Google Spreadsheet, and Google Script. The solution is very simple to implement and you can easily add some more functionality and use it on the go.

Basically, with this Issue tracking system you can:

• Open a Ticket
• Enter the Issue details
• Assign ticket to a staff member
• Send email to customer and staff with ticket details
• Check the list of Opened Tickets in Sheet/Google Site
• Close a Ticket
• Enter a solution
• Send email to the customer with solution details
• Check the list of Closed Tickets in Sheet/Google Site
• Maintain Staff member list with emails

I have also created a Google Site, where you can access the Form and the analyzed data from the sheets in one place. But before i paste you the link, let me explain how everything works.

How it works

This solution uses Google Form for data entry and a Google Spreadsheet to collect and analyze the tickets. Also, a Google Script is used to update the Form with new data on opened tickets and to send the emails to customers and staff.

Google Form

Google Form contains all the necessary questions that we collect to open or close the tickets, but they are divided into three sections. The first section contains a multiple-choice question with the options to Open and Close a Ticket and based on selection here to redirect to the correct section. This basically is a switchboard.

The next two sections are “New Ticket” and “Close Ticket” section.

In the “New Ticket” section I collect data for the new ticket, which are generally standard fields, and later on, you can add or remove some of them to fit your need. In this section, I have a field to assign the ticket to a staff member, and the information for this field is located in the sheet. So, here I use the Google Script to update the multiple-choice question with the staff names listed in the sheet “Staff”

In the “Close Ticket” section, in the “Select the Ticket ID” multiple-choice question we need to display only the Opened tickets IDs. So, In order to do that, we use the Google sheet to filter out only opened tickets, and then we use the Google Script to update the Google Form. Check the following image.

Google Spreadsheet

All the responses from the Form are collected in the spreadsheet. In this spreadsheet, I use 3 sheets to analyze the responses and one sheet to maintain the list of Staff and their email addresses.

First of all, I need to uniquely identify each ticket. For that, I use a formula to automatically add autonumber value to every response sent from the Google Form. Check this article on how to Add Auto Number to Google Form Response Sheet

In the sheet “Tickets”, I use the Filter function to filter out open tickets IDs, closed tickets IDs, and active ticket IDs. By using the data from this sheet I can populate the “Select the Ticket ID” multiple-choice question with the list of active tickets IDs.

In the next two sheets, “Active Tickets” and “Closed Tickets”, I use the data from the “Form responses 1” and “Tickets” sheets to list the Active and Closed tickets.

Google Script

The script is triggered on every form submit to update the questions in the Form and to send email to the Customer and the Staff member. So, if you add a new ticket, the script updates the “Select the Ticket ID” multiple-choice question in the Form and sends an email with the ticket details to the Customer and the Staff member assigned to the ticket. And, if you close a ticket the script sends an email to the Customer with the description of the solution.

Google Site

To implement the whole solution in one place, I have created a Google Site with the Form and the Sheets embedded in it. You can find the site here: https://sites.google.com/ view/issue-tracking-system/home

How to Implement

The procedure to implement the Ticketing system will be like this:

• First, you will make a copy of the Form in your Drive,
• then you will create a Spreadsheet (from the Form responses tab) to collect the responses,
• next, you will create the sheets and insert the formulas to filter the opened/closed/active tickets
• next, you need to insert the script and do small customization and set up a trigger onFormSubmit
• and the last, you can create a Google Site and embed the Form and the Sheets there.

So, the first step is to create a copy of my form, do that by following this link: Issue Tracking System Google Form. On the new tab just press the “Make a copy” button. The name of your form will have “Copy of” in front of the name of the Form, just delete that “Copy of”.

Issue Tracking Form

Now, you have the form in your Drive and there is no need to change anything there, but first, let me explain how the Form works.

As stated above, the Form is divided into 3 sections. The first section acts as a switchboard and based on what you select it forwards you to the correct section. So, if you want to open a ticket it will direct you to the section “Open Ticket”, after that it will give you the option to submit the form.

In the copy of the form in your Drive, you will have some dummy data, like the name of the staff or the ticket IDs. This is because my form was updated from the script with my data. Don’t worry about this, as soon as you implement and run the script it will update the questions with your data. So leave it as it is.

Now, we need to set the Form to send the responses to a Spreadsheet. So, in the responses tab select to “create spreadsheet” to create a new Spreadsheet “Issue Tracking System (Responses)” with the “Form responses 1” sheet that will collect the responses.

Before we continue to enter the formulas in the spreadsheet, we need to submit one dummy response. So, open the form in view mode, open a new ticket, and submit the response. Now, if you can see the response in the “Form responses 1” sheet, you can start working on the sheet.

Issue Tracking System (Responses) Spreadsheet

First, we need to set up an auto-numbering on the form responses, we need this in order to track the tickets. I have an article on how to Add Auto Number to Google Form Response Sheet, so if you need more info on this just check it.

Before we start, here is the link to my sheet: Issue Tracking System (Responses) Spreadsheet, so you can get the idea of how everything works.

So, insert a new column in front of the timestamp column, name it “Ticket ID” (cell A1), and in the cell A2 insert the formula:

=ARRAYFORMULA ("TID" &  
IF(ROW (B2:INDEX (B2:B, COUNTA (B2:B)))-1<100, 
text(ROW (B2:INDEX (B2:B, COUNTA (B2:B)))-1,"000"), 
ROW (B2:INDEX (B2:B, COUNTA (B2:B)))-1)
)

The auto-numbers will start from TID001, TID002 …

Next, create a new sheet and name it “Tickets”

Tickets Sheet

I use this sheet to filter out the Opened tickets, closed tickets, and based on that the Active tickets, and of course, I get only the Ticket IDs. So, for this I use 3 columns, the first one is the “Opened Tickets”, use this formula to get the opened tickets IDs:

=IFERROR( FILTER( 'Form responses 1'!A2:A, 'Form responses 1'!C2:C = "New Ticket"), "")

In the next column, “Closed Tickets”, insert this formula to get the Closed tickets:

=IFERROR( FILTER( 'Form responses 1'!I2:I, 'Form responses 1'!C2:C = "Close Ticket"), "")

The next column is “Active tickets”, use this formula to compare the two lists and get the active tickets:

=IFERROR( FILTER( A2:A, len(A2:A), iserror( match( A2:A, C2:C, 0))), "")

Your sheet will look like this:

Next, create a new sheet and name it “Active Tickets”

Active Tickets Sheet

This sheet will list all the Active tickets based on the “Tickets” and the “Form responses 1” sheet. In the first column “Ticket ID”, we will list all the active tickets from the “Tickets” sheet.

=IFERROR( FILTER( Tickets!E2:E, len(Tickets!E2:E)), "")

Now, based on the listing of active tickets, we will use a LOOKUP function to get all the info on the ticket. We can’t use more columns for the LOOKUP function, so we will need to enter the formula for every column.

The next formulas are all the same, just we lookup the different column. Here is the formula for the date:

=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!B2:B), ""))

You will probably get some strange numbers, just change the Format of the column to Date.

Here is the next formula for the “Customer Name”:

=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!D2:D), ""))

For the next column, “Customer email”, it will be almost the same, only we get the value from the ‘Form responses 1’!E2:E column:

=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!E2:E), ""))

For the “Issue Title”:

=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!F2:F), ""))

For the “Issue Description”:

=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!G2:G), ""))

And for “Assign ticket to”:

=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!H2:H), ""))

In my sheet I also use a Pre-filled link that you can use to close the ticket, it automatically selects the Close Ticket and the ticket ID on the form when you follow this link.

First, you need to get your own pre-filled link, so go to the form, select “Get Pre-filled link” from the three-dot menu, select “Close Ticket” from the Select option question and then select any ticket ID from the “Select the Ticket ID” question in the Close ticket section. Click on the “Get link” button and copy the link.

Now, paste the link in this formula, but remove the “TID001” from the end of the link, we will insert our own. Et the end, your link should look like this:

"pp_url&entry.525969148= Close+Ticket&entry.1819860752="

=ARRAYFORMULA(IF(LEN(A$2:A), "paste your link here" & $A$2:$A, ""))

Next, create a new sheet and name it “Closed Tickets”.

Closed Tickets Sheet

This sheet will list all the Closed tickets based on the “Tickets” and the “Form responses 1” sheet. In the first column “Ticket ID”, we will list all the closed tickets from the “Tickets” sheet.

=IFERROR( FILTER( Tickets!C2:C, len(Tickets!C2:C)), "")

Next, you can copy all the formulas from the “Active Tickets” sheet. Don’t copy the last one “Close Ticket Link”.

Anyway, here are the formulas for the next 6 columns: Date, Customer Name, Customer Email, Issue Title, Issue Details, and Assign ticket to:

=ARRAYFORMULA( IF(LEN(A$2:A), LOOKUP(A2:A,'Form responses 1'!A2:A, 'Form responses 1'!B2:B), ""))
=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!D2:D), ""))
=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!E2:E), ""))
=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!F2:F), ""))
=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!G2:G), ""))
=ARRAYFORMULA( IF(LEN($A2:$A), LOOKUP($A2:$A,'Form responses 1'!$A2:$A, 'Form responses 1'!H2:H), ""))

The last column is for solution, so use this formula to get the solution from the ‘Form responses 1’ sheet:

=ARRAYFORMULA( IF(LEN(A2:A), VLOOKUP( A2:A, 'Form responses 1'!I2:J, 2, FALSE), ""))

The last sheet that we need to create in order for the solution to work is the Staff sheet

Staff sheet

This is just 2 column sheet. The first column is the Name of the staff and in the second is their email address. The sheet should look like this:

You can add one or more staff members, but don’t leave it empty.

Issue Tracking System Google Script

The basic function of this script is to update the Form with the list of opened tickets and the staff members, and also send mail to Customer and Staff member when a new ticket is opened and also send an email to Customer when the ticket is closed.

Open the Script Editor from the Spreadsheet menu, delete everything there and paste this script:

function updateForm() {
  // Open the Form. Change the ID to the ID of your form
  var form = FormApp.openById("1-LPCbv4gsYhaZoEb7Nk-0OULO9fgDbszIbM4-wg3p1w");
  // Get the active Spreadsheet
  var ss = SpreadsheetApp.getActive();
  
  // Get the Form question by ID
  // Multiple choice Question "Assign ticket to" from the "New ticked" section
  var assignTicketTo = form.getItemById("2048707802").asMultipleChoiceItem();
  // Multiple choice Question "Select the Ticket ID" from the "Close ticket" section
  var tickedID = form.getItemById("1510855938").asMultipleChoiceItem();
  
  // Get the sheet where the Item data is
  var staff = ss.getSheetByName("Staff");
  
  // Get the Staff names and emails from the Staff sheet
  var staffValues = staff.getRange(2, 1, staff.getLastRow()-1).getValues();

  // Update the Multiple choice questions with the Item names (ListItems)
  assignTicketTo.setChoiceValues(staffValues);
  
  // Get the sheet where the tickets id's are
  var tickets = ss.getSheetByName("Tickets");
  
  // Get the open ticket id's from the Tickets sheet, column 5
  var openTickets = tickets.getRange(2, 5, tickets.getLastRow()).getValues();
  
   var ListItems = [];

  // Transfer the ticket ID's (openTickets) data and ignore empty cells
  for(var i = 0; i < openTickets.length; i++)    
    if(openTickets[i][0] != "")
      ListItems[i] = openTickets[i][0];

  // Update the Multiple choice questions with the ticket ID's (ListItems)
  // Check if it is the last remaining ticket (tnx Branden)
  if (ListItems == 0) {
    tickedID.setChoiceValues(["No open tickets"]);}
  
 if (ListItems != 0) {
   tickedID.setChoiceValues(ListItems);}
  
  //Grab the last form response
  var responses = ss.getSheetByName("Form responses 1");
  var lastTicket = responses.getRange(responses.getLastRow(), 1, 1, 10).getValues();
  
  //If new ticket: Send email to Customer and Staff
  if(lastTicket[0][2]=="New Ticket") {
    var customerEmail = lastTicket[0][4];
    var Subject = "New Ticket. Ticket ID: " + lastTicket[0][0];
    
    //Get Staff email from the Staff sheet
    var staffList = staff.getRange(2, 1, staff.getLastRow()-1, 2).getValues();
    for(var i = 0; i < staffList.length; i++)
      if(staffList[i][0] == lastTicket[0][7])
        var StaffEmail = staffList[i][1].toString();
    
    var Message = "Hello " + lastTicket[0][3] + ",<br/>"
    + "Ticket Details:<br/>"
    + "Ticket ID: <b>" + lastTicket[0][0] + "</b><br/>"
    + "Issue Title: <b>" + lastTicket[0][5] + "</b><br/>"
   + "Issue Details: " + lastTicket[0][6] + "<br/>"
    + "Your ticket has been assigned to: " + lastTicket[0][7] + ", email: " + StaffEmail +"<br/>"
    + "We are working to resolve the problem as quickly as we could.<br/>"
    + "To contact the person responsible for your ticket, just reply to this email.<br/>"
    + "Thank you";
    
    var emails = customerEmail + ", " + StaffEmail;
    
    GmailApp.sendEmail(emails, Subject, Message, {
      htmlBody: Message,
      name: "Issue Tracking System",
      replyTo: StaffEmail
    });
    
  }
  
  //If Close Ticket: Send email to Customer
  if(lastTicket[0][2]=="Close Ticket") {
    var allTickets = responses.getRange(2, 1, responses.getLastRow()-1, 10).getValues();
    //Find the ticket by Ticket ID and get the customer name and email
    for (var i=0; i < allTickets.length; i++) {
      if (allTickets[i][0] == lastTicket[0][8]) {
        var customerName=allTickets[i][3].toString() ;
        var customerEmail=allTickets[i][4].toString();  
      }
    }
    
    var Message = "Hello " + customerName + ",<br/>" 
    + "Your Ticket with the TicketID=" + lastTicket[0][8] + " has been resolved.<br/>"
    + "Solution: " + lastTicket[0][9] + ",<br/>"
    + "Thank you.";

    
    GmailApp.sendEmail(customerEmail, "Your ticket has been closed", Message, {
      htmlBody: Message,
      name: "Issue Tracking System",
      noReply: true
    });
  }
}

What to change

To make the script work you just need to change the Form ID on line 3. I thought that you will need to change the Questions IDs too, but it turns out that when you copy the form they remain the same. The good thing is that they are a little bit difficult to locate.

So, you can get the Google Form ID from the link of the Form, it is the part of the link between “https://docs.google.com/forms/d/” and “/edit”. Change the Form ID on line 3 with your Form ID.

Also, you can change the text of the messages for the opened and closed tickets. You can find them on line 54 and 85.

First Run

After you changed the Form ID, save the script and press the Run button from the toolbar. This script will need certain permissions to run, so follow the on-screen instruction to review and accept the permission. Check this guide if you have any problems: Google Script Authorization: Review and Accept the Permission Guide.

If everything is OK, the script will execute and it will insert the data from the dummy response that we send in earlier, before building the spreadsheet.

So, this script will nsert the opened Ticket ID and the list of Staff members into the Form. Also, the script will send an email to the email address specified for the customer and to the email address of the Staff member assigned to the ticket.

To check if everything is working, open the Form in view mode, select the New Ticket option, press Next and you will see the list with the Staff member. Now, go Back, select the Close Ticket option and click Next, you will see the ticket ID of the opened ticket (TID001):

Add a trigger

In order for the script to execute every time you fill in the form, you must set up a script trigger “on Form Submit”. So, click on the “Current project’s triggers” button from the toolbar in the Script editor. A new tab will open with the list of current project triggers, it is going to be empty. So, to add a trigger just click on the button “Add Trigger”, make sure your function “updateForm” is selected, and then select the event type to be “On Form Submit”.

Conclusion

That’s it, you now have a nice ticketing system that you can use for your small business. If you want to have everything in one place I suggest building a Google Site and embed the Form and the two sheets holding the list with Active and Closed tickets.

Here is the link to the Google Site: Issue Tracking System