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.

Note

Don’t change or delete the questions and their options, it will break the script. You can do that later, but you will need to adjust the script too.

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.

Note

Before entering the formulas in the spreadsheet make sure that you have at least one response from the Form. Responses coming in from Google form tend to insert a new row, so it will break your formulas.

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" & text (ROW (B1:INDEX (B:B, COUNTA (B:B)-1)), "000"))

The auto-numbers will start from TID001, TID002 …

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

Note

Don’t change the names of the sheets, use the one I use. If you do, you will need to change them in the script too.

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.

Note

You need to insert this script in the Spreadsheet, not in the Form. This script updates the Form with the filtered data from the sheets.

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 replay 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.

Tip

If you want to create a Google Site, the best way to insert a sheet is by embedding. So, go to the spreadsheet, select “Publish to the web” from the File menu, and on the “Link” tab select the Sheet that you want to embed, and “Web Page” from the next dropdown list. Next, click the “Publish” button to get the link. You can now use the “Embed” option in Google Sites to embed the link.

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

SavoE

I'm an IT Manager and a big Google fan. In my free time, I like to write articles for How to GApps and help people on Google help communities (My Profile) and Quora. Check the About page for more. If you appreciate my work, maybe you can help me pay my hosting bill on Buy Me a Coffee.

View Comments

  • Hello! Thank you so much for this :) I am having trouble with the close ticket Google form. It has stopped updating the Google form with my tickets, even when I close or create new ones. For example I closed TID043, still shows on the google form. I opened a ticket TID144, but it does not show on the Google Form when I select the close ticket option. Thank you!

    • Hi Jocelyn,

      Looks like the script isn't working. Check the logs and post the error here and maybe I can help you.

      Best regards.

      • I reauthorized the script with my gmail account and all seems to be working again. Thank you again for everything!

      • 9:53:55 AMNoticeExecution started9:53:58 AMErrorException: Invalid

        email:
        updateForm@ Code.gs:70

        I have only one staff member me, and it has always been just one staff member. Thank you for all of your help.

  • Thank you for this. For the "Tickets" sheet, I wanted to show the date of when the ticket was opened beside the "opened tickets" column. What formula would you use? Much appreciated.

    • Sorry, also, next to the "closed tickets" column, I would like to see the date it was closed as well. Helps to know how long a ticket has been open. Once again, appreciate it

      • Hi Ron,

        For the "opened tickets" the column Date is the date when the ticket was opened. For the "Closed Tickets" I have a formula, but it must be added manually on each row, here is the formula:

        =INDEX('Form responses 1'!B2:I, MATCH(A2:A, 'Form responses 1'!I2:I, 0),1)
        

        Check the sheet, I have added the formula there.
        Anyway, I think is best to use the script for this.

        Best regards.

  • Hi SavoE,

    I see your google site has the form already embedded and set to the "New Ticket" section. When I try the prefilled form, it shows the first section with the radio dot selected but makes you click the next button. How can we get it show like you have it already to the next section?

    Thanks for the site!

    • Hi EJ,

      It is the same with my Form too, it makes you click the Next button. Can you explain more if I'm missing something?

      Best regards.

      • OK... so its not possible to get the embedded form to prefill with the click. I was hoping to allow end users (customers) to create tickets also. But did not want them to be able to close tickets.

        Thanks

        • Yes. This solution was intended to be used like an in-house ticketing system, not for the customers. I'm working on a different approach for a ticketing system for customers, so check back.

          Best regards.

  • Hello! I have implemented this ticketing system and my company LOVES it. It has been running smoothly for a few weeks, but I am now receiving a script failure notification email (screenshot attached).

    • "We're sorry, the JavaScript engine reported an unexpected error. Error code INTERNAL." Trigger: onFormSubmit
    • Does anyone know what could be triggering this when nothing has changed? Does this mean someone is trying to submit a ticket and it's failing? If so, what do they see in return? I have no record in my response excel of new submissions.

    Any insight is appreciated! Thanks in advance :)

    • Hi Kristin,

      I have also noticed the same error on my script, so I guess is some Google JavaScript engine problem. It's working for now.

      Best regards.

  • Hello SavoE,

    How would I go about modifying the script so that it would send an email all staff listed as opposed to just the one that got assigned? Essentially I'd like to remove the assigning section. Any help would be much appreciated.

    • I ended up replacing the first section under "//Get Staff email from the Staff sheet" (lines 52-56) with this code:

      //Get Staff email from the Staff sheet
          var staffList = staff.getRange(21staff.getLastRow()-12).getValues();
          var StaffEmail = "";
          for(var i = 0i < staffList.lengthi++){
              if(i == 0){
                var StaffEmail = staffList[i][1].toString();
              }
              else {
                var StaffEmail = StaffEmail + ", " + staffList[i][1].toString();
              }

      and that seemed to do the trick. Now the code will email everyone on the Staff list and I have removed the "Assign to" question from the form (I've kept the "assigned to" columns in the sheet hidden instead of deleting them so none of the rest of the code gets messed up).

    • If you can't remove the assigning section, here's another idea:
      You can add multiple emails all in one cell separated by commas and no space -- that's worked for me to have tickets send to multiple people in one department. I think we have 6 people successfully receiving them currently.
      Hope this helps!

  • Hi I'm Having Problem for this one. How can I do this one? Thanks

    ErrorTypeError: Cannot read property 'asMultipleChoiceItem' of null
    updateForm@ Code.gs:9

  • Hello,

    I am unable to copy the form as I see it only as a form to respond to. Is there a link I missed?

    Warm regards
    Pooja

  • Hello, I implemented the code in my form and it is running ok, the only problem is that I have different sections based on the answers chosen and every time I run the script the option "go to section based on answer" is reset and I have to recreate it again. Does anyone know how to fix it and avoid the options to be reset?

    • Hi Carlos,

      The only code that will change the sections in Google Form is to get the question PageBreakItem and then set them to something else. This is nowhere to be found in this script. And also, this must be part of the Form script to function correctly.
      If you don't have this kind of code in the script, I can't see anything else that can do that.

      • Hi SavoE,
        Thank you for following up. I was able to fix the issue. The problem was that I was updating the var assigned_to and it was reseting my 'go to section based on answer' after the staff members. Now everything is working fine.

        • I'm glad to hear that, so you were talking about the script and not the form, I misunderstand that part.

          Best regards.

  • Hello, This has been very helpful. However, when I run the script, it says that it runs successfully, yet I am not receiving the email in my inbox. Shouldn't the person the issue gets assigned to receive an email with the issue?

    • Hi Christine,

      Yes, it should receive an email. So, if you don't get an error when the script runs check the emails in the Staff sheet.

Recent Posts

Google Form to Slides + Save to Drive and/or send it in email as a PDF

On your request, as an addition to the how-to: Google Form to PDF (save to Drive and/or send it in…

1 day ago

Google Form Script to Automatically Add New Choices from “Other:” Form Responses

Whit this Google Form Script you can Automatically Add the "Other:" response from the multiple-choice question or checkbox question to…

2 months ago

Google Form to PDF (save to Drive and/or send it in email)

This solution will create a PDF file from a Google Form submission using a Google Doc as a template. You…

3 months ago

Automated e-card sender – Google Spreadsheet Script to Send Birthday Cards via Email

Sending an email to wish your customers a happy birthday it's usually a thing that big companies do. There is…

4 months ago

Embed Google Form, Sheet, Chart and Doc in WordPress

You have a plugin for that too, but adding more plugins to your WordPress site can make it slower, and…

6 months ago

Google Form Script to AutoFill and Email a Doc Template

I have used a similar solution for creating documents from a template, but it was a spreadsheet script and used…

10 months ago