How to create Ticketing Systems with Google Forms & Sheets | How to setup Complaint Helpdesk [Hindi]

Introduction

This tutorial outlines how to create a ticketing system using Google Forms and Google Sheets, enabling you to automate your helpdesk efficiently. This setup is ideal for managing complaints and requests in a structured manner, ensuring that all tickets are tracked and resolved promptly.

Step 1: Create a Google Form for Ticket Submission

1. Open Google Forms.
2. Click on the "+" icon to create a new form.
3. Title your form (e.g., "Help Desk Ticket Submission").
4. Add the following fields:
   • Name (Short answer)
   • Email (Short answer)
   • Issue Type (Multiple choice or dropdown)
   • Description of Issue (Paragraph)
   • Priority Level (Multiple choice: Low, Medium, High)
5. Ensure that the "Collect email addresses" option is enabled to track submitters.

Practical Advice

• Use clear and concise questions to avoid confusion.
• Consider adding a section for users to upload files if needed.

Step 2: Link Google Form to Google Sheets

1. After creating the form, click on the "Responses" tab.
2. Click on the green Sheets icon to create a linked spreadsheet.
3. Name your spreadsheet (e.g., "Help Desk Tickets").

Practical Advice

• This linked sheet will automatically update with each new ticket submission, allowing for real-time data tracking.

Step 3: Set Up Notifications for New Tickets

1. In your Google Sheets, go to "Extensions" > "Notifications" > "Set notification rules."
2. Choose to be notified when "Any changes are made."
3. Select the delivery method (email or mobile).

Practical Advice

• This ensures you are promptly informed about new submissions, enabling quicker responses.

Step 4: Organize and Track Tickets in Google Sheets

1. Use filters to categorize tickets by priority, status, or type.
2. Create additional columns:
   • Status (Dropdown: Open, In Progress, Resolved)
   • Assigned To (Short answer)
   • Resolution Notes (Paragraph)

Practical Advice

• Regularly update the status to keep track of progress and ensure accountability.

Step 5: Automate Follow-Up Responses

1. Use Google Apps Script to send automated email responses upon ticket submission.
2. Example code snippet to send an acknowledgment email:

function sendAcknowledgment(e) {
  var email = e.values[2]; // Assuming email is the third field
  var subject = "Ticket Received";
  var message = "Thank you for your submission. We will get back to you shortly.";
  MailApp.sendEmail(email, subject, message);
}

3. Set a trigger for this function to run on form submission.

Practical Advice

• Test the script to ensure emails are sent correctly.

Step 6: Analyze Ticket Data

1. Use Google Sheets’ built-in charts and pivot tables to analyze ticket trends.
2. Identify common issues and peak times for ticket submissions.

Practical Advice

• Regular analysis can help improve your service and identify areas for improvement.

Conclusion

By following these steps, you can create a fully functional ticketing system using Google Forms and Sheets. This setup will enhance your ability to manage and resolve issues efficiently. As a next step, consider exploring additional automation tools like Google AppSheet to further streamline your processes.