Apps Script – Issues reporting form, log & email

In this post, we’ll look at a way to create a quick and simple system to report maintenance and IT issues in the classroom.

The teacher fills out a Google Form on their phone, this gets logged on a Google Sheet, and as we work in a multilingual environment, it uses Google Translate to automatically translate the issue before emailing the relevant people.

Issue reporting - 32


Setting up the form and sheet

I will assume you know how to create Google Forms and link them to Google Sheets. If don’t then check out my posts on Google Forms.

I’ve created a simple form for the teacher to fill out. They fill out the problem, whether it’s an IT or maintenance issue, their name, and choose one of the rooms from the drop down menu. So, nice and quick to fill out.

Issue reporting - 3

Then link that with a Google Sheet and you should then have the questions in that sheet, as below (on the sheet called “Form responses 1”):

Issue reporting - 4

Then, add 3 more columns on the end and label them, Translation, Comments, and Status. Plus, I’ve added a bit of formatting.

Issue reporting - 5

Then, add a second sheet and rename it “Emails”. Add in the first column, the emails of those who will receive a maintenance issue and in the second column, those who will receive an IT issue.

Issue reporting - 11


The code

Now, we need to add the code to the sheet. Open the Script Editor from the Tools menu.

Issue reporting - 6

Rename the file, e.g. Maintenance-IT Log.

Issue reporting - 7

Delete the default code that’s in there.

Issue reporting - 18

Line 1: First, we set up the function. Here, I’ve called it onFormSubmit, just to remind myself that I will need to set up an onFormSubmit trigger later on.

Line 2: We then need to get the active spreadsheet and store it in the variable ss.

Line 3: Then we need to get the sheet where the responses will appear, called “Form responses 1”, using the getSheetByName() method.

Issue reporting - 19

In this part, I just want to format the data on the sheet, so that the data is all centered, it all has text wrap, and that it has borders around it. Basically, to makes it look nice and more readable.

Line 6: We get the sheet with frSheet and get the range of all the data currently on that sheet, using the getDataRange() method. We then add our formatting to that range. Starting with centering the data, using setHorizontalAlignment(). Note, there’s no semi-colon at the end here.

Line 7: Then we add text wrap to all the data, using setWrap() and setting it to true. Here, as we are still attached to the getDataRange() part, we only need to use a dot and then setWrap(true).

Line 8: Finally, we add the borders to the data, by using setBorder(). There are 6 potential places for the lines (top, left, bottom, right, vertical, horizontal), but as I want all the borders to have lines, I state true for each position. This time we add a semi-colon, as we’ve come to the end of formatting this range.

Issue reporting - 20

Now, we want to get the last row, which will contain the latest data, and the last column. We’ll use this later on.

Line 11 & 12: To do this we use getLastRow() and getLastColumn().

Now we want to add a couple of pieces to the latest row. The first will be the status of the issue, which will be open at first. Then, as we want to be able to work with multiple languages, I want to translate the problem reported into Spanish, as I’m working in Spain and the maintenance and IT guys are Spanish.

Issue reporting - 21b

Line 15: First, let’s get the status cell on the latest row. As this is in the last column, we simply get the range using the lastRow and lastColumn variables we just set up. Then, set the value to “Open”.

Issue reporting - 22

Line 19: First, we need to get the cell where we’re going to add the translation. Here, I’m going to use A1 notation with the getRange() method. So, I know it will be in column F, and I already know the last row number, which is in the variable lastRow, so I combine the two together, using the plus sign.

Line 20: Here, we’re going to add a formula to the translation cell. We’re going to use the GOOGLETRANSLATE and DETECTLANGUAGE functions. See my post on how these work in Sheets. As the cell reference will be different every time, we need to refer to the numbers of rows and columns away from the translation cell the original problem text is.

So, in the brackets, we add single quote marks, then our formula. This will look at the cell on the same row as the translation cell (row 0) but 4 columns to the left (row -4, i.e. column B), it will detect what language it is and translate it to Spanish (“es”).

Here’s what it will look like:

Issue reporting - 10

Issue reporting - 23

Now, let’s get the data from the latest row, as we want to include that in our email to maintenance or IT. We could just write the lastRowValues parts directly in the email part, but remembering which number refers to what information, can be confusing sometimes, as in our email the information isn’t in the same order as it is on the sheet, so let’s store them in easy to understand variables.

Line 23: First, we get the values all along that latest row and store them in lastRowValues.

Lines 24-29: Now, we get the individual pieces of information stored in the lastRowValues array. So, for example, to get the timestamp, we look at position [0][0]. The first 0 is always 0 as we only have 1 line of data, so the row will always be 0. And the second 0, refers to the place in the array the timestamp is, which as it’s the first piece of data (column 1), the position is 0. Remembering that arrays always start with 0 not 1.

Issue reporting - 24

Dates, are always a bit more complicated to deal with, as if we use the timestamp as it is, it will come out like this: Fri Apr 14 2017 18:43:13 GMT +0200 (CEST), not the most succinct date. So, we need to shorten it to a format we want.

Line 32: First, we need the time zone we’re working in. Use Session.getScriptTimeZone() for that. Note, the capital “S” in Session, as it’s a class.

Line 33: Now, let’s shorten the timestamp. To do this, we use Utilities.formatDate(). This needs 3 arguments, the date & time you want to convert, the timezone, and the format of the date and time you want. The new format is between quote marks.

Issue reporting - 25

At the bottom of the email to maintenance or IT, I want to add a link to the log, so they can access it directly from the email, to update what they’ve done in the Comments column.

Line 36: This is easily done, just get the URL of the active spreadsheet (ss), using getUrl().

Now, we need to prepare the email we want to send. If you’ve read my previous posts on sending automatic email from form submissions, then you’ll know how easy it is. In those posts, I just used basic formatting in the email. Here, let’s use some basic HTML to make the email easier to read and a little more professional. Plus, see this site for more info on HTML.

Now, we need to state what we want to include in the body of the email. Lines 40-51 is all stored in the variable emailBody. Below is the email format I want. There’s an “Issue” header, then the the various bits of information from the form. Note, the title of each is in bold. Plus, I’ve added some lines, organise it a bit better. Then, at the bottom is the link to the log sheet.

Issue reporting - 31

Issue reporting - 26

Line 39: First, we need a subject for our email. Here, I want it to state “Issue -“plus the date of the issue, using the shortTimestamp variable.

Line 40: As a general rule, the fixed text we want to add needs to be in quote marks and the variables we use to add the form data, are without them. So, first I want the title “issue” to be an underlined header. So, I’ve added a header 3 tag < h3 >and an underline one < u >. Then, I close the tags with </ h3 > and </ u>. Note, that every line except the last one will need a backwards slash \.

Line 41: I want the word ‘Problem’ in bold, so I use the < strong > tag to do that. Then I close it before the form data, so that it’s not bolded. Note, the use of the pluses to connect the information together. Plus, I use the line break tag < br /> to create a new line.

Most of the other lines are similar. In Line 43, I want to add a horizontal line to separate the information, so I use the < hr /> tag to do that.

Line 51: At the end, close the quotes and end with a semi-colon.

Issue reporting - 27

Now, we need the email addresses of those we are going to send the email to.

Issue reporting - 11

Line 54: As you’ll remember, we set up an Emails sheet. So, first we get that sheet.

Line 55: Then, let’s find out how many rows have email addresses, so let’s just use getLastRow() to do that. Note, if you have one column with more emails than the other, it doesn’t matter, as if you’ve got some blanks as the email service will just ignore them.

Now, we need to decide which column we’re going to use. To do this, we use an if statement, which will look to see if it’s an IT issue, and if not, it will default to the other emails.

Issue reporting - 28

Line 57: We check to see if the value in column C (“IT or Maintenance?”) is equal to “IT”. Remember to use the triple equal signs for ‘equals to’ not a single equals sign.

Line 58: If it is, then it gets the email addresses from column 2.

Line 59-61: The alternative is that it isn’t (else). In which case, it gets the email addresses from the first column.

Issue reporting - 29

Now, we put it all together, to send the relevant people the email.

Line 64: We use GmailApp.sendEmail() to send the email. We have 3 main arguments: who the email is going to (emailTo), the email subject (emailSubject), the body of the email (emailBody). As we’re using HTML, we also need to add the option of htmlBody. Options go between curly brackets. First, write htmlBody: then the emailBody variable.

I also want a specific person to receive an email if someone replies to this email. Remember this email is being sent from the account in which the code has been set up under, but you may not want that account to be emailed to. To do so, just add replyTo: and the email address you want between quotes.

Line 65: Close the function with a curly bracket.


Setting up the onFormSubmit trigger

We want the email to be sent automatically when the form is submitted, so we need to set up the onFormSubmit trigger to do this.

Issue reporting - 12

Click on the clock symbol in the toolbar.

Issue reporting - 13

Click “No triggers setup”.

Issue reporting - 14

Here, you’ll need to change the Events.

Issue reporting - 15

Change “Time-driven” to “From spreadsheet”.

Issue reporting - 16

Change “On open” to “On form submit”.

Issue reporting - 17

Then press “Save”.

The first time press the play button on the toolbar, to prompt the authorisation of the script.

Issue reporting - 8

Click “Review permissions”.

Issue reporting - 9

Then click “Allow”.


Reporting an issue

The teacher fills in the form and submits it.

Issue reporting - 10

The information appears in the sheet and the code then adds the translation and the Open status. It then send the relevant parties an email with the summary:

Issue reporting - 31

As you can see, the issue is clearly presented, allowing the person to take action.

Once the issue is resolved, the person would then add the action taken in the comments on the sheet and change the status to Closed. As the sheet is shared with everyone, the open issues are clearly visible, prompting quick resolution of them.


Here’s the link to the sheet containing the code.

https://docs.google.com/spreadsheets/d/1i2paFxUjxun6pvLgfAf247Uw6eJGIwK19SLWrMD4Vdc/copy

Here’s the form:

https://docs.google.com/forms/d/1j9hC0S-P7BsbX5873v9BGptw2PbELOI8uuWb5QvjrdM/copy


Here’s the full code:

Issue reporting - 33Issue reporting - 34


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Google+Flipboard / Twitter)


Advertisements

2 thoughts on “Apps Script – Issues reporting form, log & email

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s