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.

---

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.

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”):

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

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.

---

The code

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

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

Delete the default code that’s in there.

1. function onFormSubmit() {
2.  const ss = SpreadsheetApp.getActiveSpreadsheet();
3.  const shFR = ss.getSheetByName("Form responses 1");

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.

5.  //Format sheet - Centre all data and add text wrapping  
6.  shFR.getDataRange().setHorizontalAlignment("center")
7.                     .setWrap(true)
8.                     .setBorder(true, true, true, true, true, true);

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.

10.  //Get last row and column
11.  const lastRow = shFR.getLastRow();
12.  const lastColumn = shFR.getLastColumn();

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

Lines 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.

14.  //Add "Open" status in last column of latest row
15.  shFR.getRange(lastRow, lastColumn).setValue("Open");

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”.

17.  //See Google Translate for the list of languages available
18.  //Translate problem from any language into Spanish     
19.  const translationCell = shFR.getRange("F" + lastRow);
20.  translationCell.setFormulaR1C1('=GOOGLETRANSLATE((R[0]C[-4]), DETECTLANGUAGE(R[0]C[-4]), "es")');

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:

22.  //Get data from latest response and store in variables
23.  const lastRowValues = shFR.getRange(lastRow, 1, 1, lastColumn).getValues()[0];
24.  const timeStamp = lastRowValues[0];
25.  const problem = lastRowValues[1];
26.  const ITMaint = lastRowValues[2];
27.  const name = lastRowValues[3];
28.  const room = lastRowValues[4];
29.  const translation = lastRowValues[5];

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. The [0] at the end make this a 2D array.

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], which as it’s the first piece of data (column 1), the position is 0. Remembering that arrays always start with 0 not 1.

31.  //Change date and time format
32.  const timeZone = Session.getScriptTimeZone();
33.  const shortTimestamp = Utilities.formatDate(timeStamp, timeZone, 'dd-MM-yyyy HH.mm');

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.

35.  //Get URL of spreadsheet
36.  const URLOfSS = ss.getUrl();

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.

38.  //Prepare email
39.  const emailSubject = "Issue - " + shortTimestamp;
40.  const emailBody = "<h3><u>Issue</h3></u> \
41. <strong>Problem: </strong>"+ problem + "<br /> \
42. <strong>Translation: </strong>"+ translation + "<br /> \
43. </p><p><hr /> \
44. <strong>Room: </strong>"+ room + "<br /> \
45. </p><p><hr /> \
46. <strong>Name: </strong>"+ name + "<br /> \
47. <strong>Date & Time: </strong>"+ shortTimestamp + "<br /> \
48. </p><p><hr /> \
49. <strong>Issue Log: <br /> \
50. </strong>"+ URLOfSS + "<hr /> \
51. ";

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.

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

Now, we need to decide which row 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.

53.  //Get all emails (read list one if it's maintenance or list 2 if it's IT)
54.  const shEmails = ss.getSheetByName("Emails");
55.  let emailTo;
56.  if (ITMaint === "IT") {
57.    emailTo = shEmails.getRange(2, 2).getValue();
58.  } else {
59.    emailTo = shEmails.getRange(1, 2).getValue();
60.  }

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

Line 55: Set up the emailTo variable, which we will store the email addresses into, so it’s visible later in the code.

Line 56: 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 57: If it is, then it gets the email addresses from row 2.

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

62.  //Send email
63.  MailApp.sendEmail(emailTo, emailSubject, emailBody, { htmlBody: emailBody });
64. }

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

Line 64: We use MailApp.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.

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.

Click on the Triggers clock symbol on the left of the editor.

Click “Add Trigger”.

Here, you’ll need to change the Event type from the default “On open”.

Select “On form submit”.

Click “Save”.

It will prompt you to authorize the script and the trigger. Click on your email account.

Click “Advanced”.

Then click “Go to Maintenance-IT Log (unsafe)”. It’s safe to do so!

Click “Allow”.

To go back to the editor, click on the arrow icon on the left of the screen.

---

Reporting an issue

The teacher fills in the form and submits it.

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:

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

---