Automate Help Desk Ticket Closing

Advanced

About this Tutorial

Create a simple automated help desk ticketing system.

Create tickets
Closed dropdown
Calculate time to close
Create dashboard

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Apps Script APIs used

Logger SpreadsheetApp

Apps Script methods used

.log() .getActiveSpreadsheet() .getColumn() .getRange() .getRow() .getSheetByName() .setValue()

Featured Formulas

AVERAGE() COUNTA() COUNTIF() FILTER() IF()

Video Transcript

<div>00:00 We're going to create an automatic help desk ticketing closing system here. It's going to be very quick because we're going to create a Google Form . 00:08 We're going to create a closed picker. This is going to be an automatic thing we do through Apps Script .

And then when we close our tickets, we're going to add a timestamp . 00:16 So that we have a timestamp from the form when we get the tickets, when they open, and a timestamp when they close. 00:23 That means we can create a dashboard that shows both how many open tickets are there, plus what is the average time to close. 00:30 So we can monitor this kind of number . So let's go to tools, create a new form, and just get a very simple ticket. 00:41 So, help, fill in this help, and we'll help you.

You may have some other things you want to ask here, but we're just going to ask a basic short answer, or even paragraph of what's the issue. 01:00 You probably have some other things here, but let's publish this. Make sure it's published to anyone. Done. And we can fill this out as well. 01:13 And so we have our form responses. We're going to call this Tickets. So that's going to be the name of the sheet, and we'll add a few here. 01:31 Some issue, submit, Now let's look at that. We have this.

We'll actually change this to Unformatted Data . 01:43 We're going to add a column here of Closed and Closed Time. How do we add this? Here, uh, actually, drop down menu. 01:55 Well, let's go up to Extensions, Apps Script . We're going to need to do two things. We're going to need to write a function , and then we're going to need to trigger that function when the form is submitted. 02:07 So, what we're going to a function called setClosedDropDown. We'll have an event here.

We can shorten this to just e, so we don't have to type the word event all the time. 02:20 We get some very interesting information from this. We get the row that we're on. e. range .getRow. We have the sheet that we're on. 02:34 e. range .getSheet. And we're going to end up having to set sheet. getRange , the row, uh, but the column will be C, so 3. And we need to set data validation rule. 02:56 And a rule here. Well, what's going to be our rule? We need two things. We need a rule, and we need a drop-down. 03:08 Like, items.

So, spreadsheet , app, .newDataValidation, .requireValueInRange, and our range , we're going to go get that actually, over here. 03:28 We're going to create a new sheet called Settings, CloseStatus. So in case you want to add more things here, so maybe closed, closed, open or in progress, that kind of thing. 03:50 We'll have these options here. So let's go get those. Drop down status range equals spreadsheet app dot get active spreadsheet dot get sheet by name. 04:06 Our name is going to be settings dot get range is going to be A to colon A.

So anything in that A column but not the header and we're going to require value in range drop down status range and we want it to be true that it needs to be a Thank one of those options And we'll need to build this okay, so 04:39 What we're doing here is we're setting this closed drop down anytime.

We're on a row that row gets created When a Form is submitted. 04:49 So let's go create the trigger again Our two steps are we create the function and then second we create the trigger Let's go over to triggers on the left and then on the bottom right add trigger choose which function to run We'll use set closed drop down We'll have the from spreadsheet and the select 05:06 event type is on form submit We'll click save And now let's go submit another response One more, oh we do need to authorize before we actually get this triggered. 05:21 Let's click allow.

And there this trigger should show up there. And so if we get any errors we'll see the error rate here. 05:35 So let's add another one. One more issue. Let's click submit. We'll go back to our tickets. And here one more issue we have a drop down menu here. 05:46 Now we can click closed. So our next automation is going to be if we click closed in the C column on tickets. 05:52 Put in a timestamp in D. So that we can compare these two timestamps A and D. So let's go back to our script. 06:03 And add another function . Function on tickets. Edit.

This time, we're using a built-in function . Not an installable one. This is a very simple trigger . 06:19 This trigger will happen. We don't need to create the trigger ourselves. It'll be here automatically. We need a few things. Variable, we need to know what row we're on. 06:28 E. range .getRow. We need to know what column we're on. We need to know what sheet we're on.

And we can then say if sheet is equal to, we need two equal signs. 06:56 I think is tickets in all caps, ampersand, let's move this up a little bit so you can see it, now ampers, double ampersand means and, column equals three, we're in this set C column, we also need another value, variable value equals E dot value two equal signs, and value is equal to all caps close closed 07:31 , then what are we going to do?

We're going to go to spreadsheet dot app dot get active spreadsheet dot get sheet by name, tickets. 07:44 Dot get range row, same row we're on, and in the fourth column, set value new date. This sets a timestamp , this new date. 08:00 And this will run when there's an edit, so let's check that that's correct. And if there's an error , we'll see over here in executions. 08:14 This is completed. Let's double check all this, logger.log row. 08:32 Maybe this sheet is not correct. What else do we need, huh? Value. Let's log those.

And that should trigger it as well. 08:54 Let's go back to our executions and see what log shows up. Oh, it's just sheet. So I think we need to sheet.getName. 09:14 Now let's try it. So we'll go here, we'll submit another response. And more issues. Submit. We have our drop-down menu and we want to close it. 09:29 And we have a timestamp . Perfect. So now we can tell what is the close time. So in our, time to close in our sheet here. 09:39 Time to close. I'm going to write a function .

If C2 equals closed then we're going to take the closed time and minus the time, the original timestamp to get the difference. 10:03 And if not, we'll just have it blank. So let's form the format this to duration, I think. There we go. 10:16 So this is three minutes, seven minutes, this is five seconds. There you go. Uhm. We can change these to something like this. 10:29 We can also change this format here, but let's add this value when we set closed dropdown .

Sheet. getRange , row, and column 5, setValue . 10:51 Our value is going to be this formula , but we want to make sure that, uh, the 2s and 2s here, or whatever row we're on. 10:58 So we're going to actually change these to, to backticks. Create a backtick here, and a backtick here. And instead of 2, we'll use a little bit of interpolation. Dollar sign , and curly brackets. 11:12 This means every time we enter the number , it'll be whatever the row is from our variable up here.

So now when we create a new help ticket, help me please submit, we have our formula already there. 11:40 So we can hit closed, get timestamp , and a duration. Again, this format of this duration can probably be fixed to some extent. 11:52 So I think it's probably best to just wrap this with text, and have hours, minutes, and seconds, but also part of perhaps maybe even days here. 12:07 If you have more than, need more than hours. So let's add this text to our function here. Text and, I think we only need one comma there. There we go. 12:23 So now let's add a few more.

Submit tickets. Ticket one. Submit. Ticket two. Submit. 12:35 Go back and see we have, ah, zeros until we have close, but this will be fine. We have some open tickets. 12:47 Now we need to create a dashboard . We're just going to go through, we're not going to make this very easy, we're just going to go through some very simple formulas. 12:56 Open tickets. Say, total open tickets. Here. Average time to close. Okay, so here we need to figure out how many tickets are there that are not closed. 13:19 So we can do count if. And our criterion is that it's not closed.

You can also do count filter . 13:36 I think we just actually want to say equals count all, b colon b will have an issue. 13:49 of tickets. And then we'll minus count if this is closed. 14:04 That gets us to perfect. That is our answer. But now, here's our average time to close. 14:17 How are we going to figure that out? Well, we can use the seam filter . Or we can create a filter . 14:25 We're going to filter our time to close. And our condition is that this is equal to closed. Let's take out the very first header row . 14:37 So E2 and C2.

And we're going to average all of those. And there's our average. Let's see if that is correct because it's 177. Let's bring this back to reality. 14:56 There, 349. Dashboard , and here's our average time to close. Perfect. So we've created a little bit of a dashboard to get our information. 15:07 We've created our ticketing system here, where we have an issue. We can add any number of columns here that we want to get information from the Google Form . 15:17 We have a timestamp when it's submitted. We have a closed drop-down menu that's added.

And our close time is automatically added when we click closed. 15:28 Our dashboard shows total open tickets and average time to close. Pretty cool. Thanks for watching.</div>