Simple Ticketing System With GForm & Gsheet for Request Management
As my beloved department grow bigger, the needs and requests for data to make decisions increased. As a normal person, it is so norms that I somehow forgot what was requested and what metrics needed to answer their problem statement. I found it difficult to manage and set which one to do first as the demands of data rapidly increasing.
So earlier this year, me and my colleague creating a simple ticketing system using google form, google sheet, and google app script. Kudos to my colleague Dimas Andika for creating the script, if you have question, please approach him :DD.
Here’s how we do it:
- Create Google Form (This will be used for our user to fill their requests). Click here for google form example
- Set-up the Google Sheets to adding new information.
- Add ticketID using arrayformula → =arrayformula(if(len(B2:B)>0,”SCID — “&text(row(B2:B)-1,”00000"),””))
- PIC → who’s responsible for the request.
- Ticket Status → under review / on process / done / rejected / etc.
- Attachment / Link Reference → can upload file to gdrive so requestor can download the result or just put the link for requestor reference
- Click here for google sheets example
3. Don’t forget to activate “Collect email addresses” feature for recipient in e- mail.
4. Build the auto e-mail script using Google app-scripts, can go to tab “Extensions → Apps Script”.
- Create function that will sent e-mail if requestor finish submit data in Google Form.
- Create function that will sent e-mail if the status have changed to under review / on process / done / rejected / etc.
5. Activate the script by create triggers
Voila! your ticketing system is ready to use. Please don’t hesitate to give comments if you have idea to improve this management system.
Here’s some glimps showing the results: