Tutorial: Automating a Help Desk Workflow

Goal

This tutorial guides you through the steps of automating a help desk workflow.
Bob is the sole employee staffing the help desk at
ACME Corp. Until now, he's been managing his workflow by having users submit
their issues via Google Forms, and he uses the associated
spreadsheet to record notes, resolutions, and other information about the
issues. He copies and pastes the user's email address, resolution, etc. into
Gmail to update users on their issues. While this system works, it's time
consuming. In this tutorial, we'll walk through five different steps Bob can
take to automate his workflow.

Time to Complete

Approximately 45 minutes

Index

This tutorial is divided into five sections. Each section
adds additional functionality to the help desk script.

Section 1: Creating a form and sending a simple, automatic email
confirmation

You should see seven columns in the spreadsheet: Timestamp, Description,
Urgency, Contact email, Notes, Status, and Resolution.
Bob uses these columns to manage his tickets. The first four columns are
populated when a user enters a request via the spreadsheet form. Bob uses
the last three columns to enter information about the request.

Let's add some code that will send an email to the user when a
new request is entered. In the spreadsheet, open the Script Editor (Tools
> Script editor)

Copy, paste, and save the following code in the Script Editor.
You'll be prompted to give your script a name when you save it. You can
name it 'Help Desk' or anything you like.

The function
formSubmitReply
is an event handler, which will be set up so that it is called every time
a form response is submitted. An event is passed to every event handler as
the parameter
e
. In a spreadsheet 'on form submit' event, you have access to
e.values
, which is an array containing all the values in the form submitted by the
user. The ordering of the values matches the ordering of the columns in
the spreadsheet.

The
formSubmitReply
function extracts the user's email address from
e.values
and then sends an email using MailApp.sendEmail to the user to let them know their
request has been received. We're using the optional advanced argument
name
to set the name of the sender as 'Help Desk'.

Now we need to set up a trigger so that this function will be
called each time a form response is submitted. In the Script Editor, go to
Resources > Current project's triggers. Click the link that says 'No
triggers set up. Click here to add one now.'

There are three listboxes in the dialog that appears. Choose
'formSubmitReply' for the script to be run, 'From spreadsheet' for the
event type, and 'On form submit' for the event trigger. Then click Save.

You will then see an 'Authorization required' dialog. This
dialog will inform you what data and services the script is able to access
so that you can choose to authorize the access or not. In this case, the
script will have access to send emails. Click the Authorize button to
grant the access.

Let's test the script by entering a sample request. In your
spreadsheet, select Form > Go to live form. Fill out the fields in the
form, being sure to enter your own email address in the 'Contact email'
field, and then click Submit.

You should see an entry in your spreadsheet, similar to this:

And, you should see an email from the Help Desk acknowledging your
request:

Section 2: Including ticket number and place in the support queue in
the confirmation email

In Section 1, we set up a simple confirmation email that was sent to
users when they enter a request. In this section, we'll build on that code
so that we can send the users a more informative email, including a ticket
number and their place in the queue.

First, let's take a look at how Bob manages the tickets. When a new ticket
is entered, he marks 'New' in the Status column. Once he begins
working on a ticket, he marks it 'In Progress', and when it's complete, he
marks it 'Resolved'. He uses the Notes column for any notes or
additional details about the issue, and he uses the Resolution column
to store details about how the issue was resolved.

Enter 'New' in cell F2 to indicate that it's a new issue.
Next, we'll add code to handle this automatically going forward.

Open the Script Editor. Replace the existing code for this
script with the code below:

The new code in
formSubmitReply
retrieves the last row in the active sheet and set the value in the Status
column (column F) for that row to 'New', since it's a new request. Then
the script loops through all the preceding rows and counts the number of
rows which have 'New' status. This allows us to inform the user of their
place in the queue. Next, the script emails the user, this time, including
a ticket number in the subject line (based on the row number of the
spreadsheet), and also tells them their place in the queue. The
getColIndexByName
helper function returns the column index for a given column name.

Let's try it out. Select Form > Go to live form and enter a
new request.

You should see a new entry in your spreadsheet, with 'New' in the Status
column:

And, you should see an email from the Help Desk with a ticket number in
the subject line, and your place in the queue:

Now that Bob can send customized email confirmations automatically, he
wants to automate some of the other repetitive tasks. Sending updates to
users when their tickets are updated is currently a manual process for Bob.
He copies information like the contact email, status, and resolution from
the spreadsheet into an email each time he needs to send an update. In this
section, we will add a custom menu entry to call a function that email the
status of a selected ticket to the user.

Open the Script Editor. Paste this code below the existing
code in your script:

The
emailStatusUpdates
function gets the row number of the row in the spreadsheet that's
currently active. This would be whatever row Bob has selected in the
spreadsheet. Next, the script retrieves the user's email address from the
Contact email column (column D) and sends an email to them
including the information in the Notes, Status, and Resolution
columns (columns E, F, and G).

The
onOpen
function is an example of a simple event handler, which is a type of event
handler that's built into the Google Apps Script language. The function
onOpen
is executed automatically when a user opens a spreadsheet for editing. One
common use of
onOpen
is for adding a custom menu to the spreadsheet's menu bar. In the code
above, we're added a menu named 'Help Desk Menu' with one option 'Send
Status Email', which calls the
emailStatusUpdates
function.

Reload the page containing your spreadsheet in order to trigger the
onOpen
event. After the page loads, you'll see 'Help Desk Menu' appear in the
spreadsheet's menu bar. Click a cell in row 2, for example A2, and then
select 'Help Desk Menu' > 'Send Status Email'.

You should have received an email with the updated status, notes, and
resolution on the ticket.

Section 4: Scheduling appointments with users

Sometimes Bob needs to schedule an appointment with a user to help them
with their issue. In the past, he would open both calendars, find a time
when they were both free, and then create the meeting invitation. Now, he
wants to extend his script to handle that work for him.

In this section, we'll modify the script to add a new custom menu entry that
will find the first available 30 minute time slot on a given day in both
your calendar and the user's calendar and then schedule the appointment.
We'll use UiApp to build a custom user
interface to gather information for scheduling.

Open the Script Editor. Paste this code for the
scheduleAppointment
and
schedule
functions below the existing code in your script:

scheduleAppointment
constructs a vertical
panel with a button
and a grid, which
contains text
boxes to hold input data. The input data consists of appointment date,
the start and end times for Bob's work day, and the user's email address.
In a hidden field, we're passing through the row number of the
spreadsheet, so the spreadsheet can be updated after the appointment is
scheduled. When the 'Schedule Appointment' button is clicked, the
schedule
function is called. It looks at both your default calendar as well as the
calendar of the user. The function assumes that a work day begins and ends
at the time you specified in the UI dialog (09:00 PDT to 17:00 PDT by
default). It will check both calendars to try to find the first 30 minute
time slot where both you and the user are free. If a time slot is found,
then the calendar invitation will be created and the status and notes in
the spreadsheet will be updated. If no time is found, you'll be instructed
to choose a new date.

Next, we'll add the new menu entry to our custom 'Help Desk Menu'. Replace
the existing
onOpen
function in your script with the following code:

Reload the page containing your spreadsheet in order to trigger the
onOpen
event. After the page loads, you'll see the new 'Schedule Appointment'
menu item in the 'Help Desk Menu'. Click a cell in the row for which you
want to schedule an appointment, and then select 'Help Desk Menu' >
'Schedule Appointment'.

Enter a date, and if the script is able to find an open time slot on that
date, then you should see an appointment in both your and the user's
calendars.

To reduce his workload even more, Bob wants to add the ability to
automatically push issues and resolutions to a knowledge base on Google
Sites, so that users can find the answers to frequently asked questions on
their own.

In this section, we'll modify the script to add a new custom menu entry
that will add a selected issue description and resolution to a Google Sites
knowledge base.

First, we need to create a Google Site to host the knowledge base. If you
already have a Google Site, you can use it. If you don't, you can create
one here. The URL
of your site will be something like
https://sites.google.com/site/SITE_NAME
or
https://sites.google.com/a/YOUR_DOMAIN/SITE_NAME

Next, from your Google Site, click the Create page button and
choose List from the 'Select a template to use' section. Enter 'kb'
as the name of the site, and make sure that the Put page at the top
level radio button is selected. Then click the Create Page
button. On the next page, click the Create a custom list button and
then create two columns of type 'Text': "Question or Issue" and
"Resolution", and then click Save.

Open the Script Editor. Paste this code for the
pushToKb
function below the existing code in your script, and make sure you replace
SITE_NAME, and optionally YOUR_DOMAIN, with your own values:

Reload the page containing your spreadsheet in order to trigger the
onOpen
event. After the page loads, you'll see the new 'Push to KB' menu item in
the 'Help Desk Menu'. Click a cell in the row for the issue you want to
push to the knowledge base, and then select 'Help Desk Menu' > 'Push to
KB'.

Then load the knowledge base page on your site, and you should see the new
issue and resolution added to the page.

Summary

Congratulations, you've completed this tutorial. Here is the full code
for the script described in this tutorial.