In this post, we’re going to look at a simple issues reporting system, where the teacher fills out a Google Form to report the issue. This is stored in a Google Sheet, and an email with the summary of the issue is either sent to the maintenance person or IT technician, depending on the type of issue.
This is based on a system I introduced at the academy where I work, which is in Spain but not all the teachers speak Spanish, so the form allows them to report the issue in English and then it’s translated into Spanish for the relevant person to resolve the issue.
Once the issue is resolved, the status is changed to Closed and this triggers an email to be sent to the people, informing them it’s closed, along with the action that was taken and any other relevant information.
Files
Google Form : “6-Issues log”
This is what the teacher fills out. They fill in the problem, state whether it’s a maintenance or IT problem, select their name from a list, state which building the problem is and the classroom. This form is connected to a Google Sheet.
Google Sheet : “6-Issues log”
The submission is stored in this Google Sheet and the translation of the problem is added and the status is set as open (‘abierto’ in Spanish).
SCRIPT PROJECT
- 1-Send Issue.gs – This script gets the form submission, updates the sheet with the translation, etc, and emails the relevant people with the details.
- email.html – This is the email that is sent.
- css.html – This is the styling for the email.
- 2-Close Issue.gs – This script is triggered if the status of the issue on the Google Sheet is changed to closed (‘Cerrado’). It sends a summary of the issue along with the closed date.
- email2.html – Closed email
- 3-updateTsOnForm.gs – This updates the list of teachers on the form.
- onOpen.gs – This adds the menu to run the updateTsOnForm function and also when the sheet is opened, goes to the last row and highlights the status cell.
CODE
Send the issue – 1-Send Issue.gs
Let’s look at the first script, which will translate the issue and send it to the relevant people.
1. //Rev1
2. function sendIssue() {
3. //Get latest issue
4. const ss = SpreadsheetApp.getActiveSpreadsheet(),
5. urlOfSS = ss.getUrl(),
6. shISSUES = ss.getSheetByName("ISSUES"),
7. lastRow = shISSUES.getLastRow(),
8. latestSub = shISSUES.getRange(lastRow, 1, 1, 12).getValues()[0];
9.L2: Open the function.
L4-5: Get the active spreadsheet and get its URL, which we’ll include in the email we send.
L6-8: Get the ISSUES sheet, its last row where the latest submission is, and get the data on the row. Note, I’ve added [0] at the end just to convert the array from a 2D one to a basic 1D array, which makes it a little easier to extract the data later on.
Now let’s get the room that is affected. On the form, this is divided into 3 questions, as it could be a room connected to Block A, B, or C.
So, on the Sheet it is stored in one of three cells (one related to each question).
I just want the room, so let’s get those 3 cells and filter it so that we only get the room value.
10. //Get the room affected
11. const room = latestSub.filter((cell, c) => {
12. return cell != "" && c > 8;
13. });
14.L11: Get the latest submission and use the filter method. The content of each cell in the array will be looped through.
L12-13: We then return only ones that match the criteria, which is that the cell isn’t empty and that the index number in the array is greater than 8, which will correspond to 10th column (J) and above. This will give us the room.
Now let’s translate the problem into Spanish. For me, this is my favourite part of the program, and best of all it’s really easy to do.
15. //Add translation
16. const probInEng = latestSub[5],
17. translation = LanguageApp.translate(probInEng, '', 'es');
18.L16: Get the problem from the latest submission.
L17: We use the LanguageApp and the translate method to do the translating. We pass the problem, leave the next parameters blank, in case the problem was already written in Spanish as some of our teachers speak Spanish well, and then state the language we want to translate to, in this case Spanish. Note, this will translate any language entered into Spanish, which for us is useful as we have English, French, and German speakers in our department.
Next, we need to update some of the cells on the sheet, with the room, the translation, and the open status. Rather than adding these individually to the sheet which takes slightly slower, we can do it in one hit, by adding the missing info to the original latest submission array and then adding that to the row.
19. //Add room, translation and status on sheet
20. latestSub.splice(4, 1, room[0]);
21. latestSub.splice(6, 1, translation);
22. latestSub.splice(8, 1, "Abierto");
23. shISSUES.getRange(lastRow, 1, 1, 12)
24. .setValues([latestSub])
25. .setBackground('#f4cccc')
26. .setWrap(true);L20-22: We can replace elements in an array by using the splice method, stating where we want to replace and what with. So here it adds the room (note the [0] to get rid of the array), the translation, and the text “Abierto” (closed).
L23-26: Then add that array to the last row. I also want to change the background to a light red, to show it’s open, and also want to set the cells to text wrap, particularly if the problem is longer text, so that it wraps within the cell.
We need to get the email of the teacher who sent the form.
//Send email to IT or maintenance & form sender
29. const tEmail = getTeacherEmail(latestSub[1]);
30. sendEmail(tEmail, latestSub, urlOfSS);
31. }L29: Call the getTeacherEmail function and pass the teacher’s name. We’ll see this function below.
L30: Finally, let’s send the email. Call the sendEmail function.
Let’s now look at the functions that were called above.
33. //Get teacher's email from "Teachers details" spreadsheet
34. function getTeacherEmail(tName) {
35. const ssTD = SpreadsheetApp.openById('FILE ID');
36. const shTD = ssTD.getSheetByName("TD");
37. const tAndEmails = shTD.getRange(2, 1, shTD.getLastRow() - 1, 2).getValues();
L34: Open the getTeacherEmail function.
L35-36: Get the Teacher Details spreadsheet and get the sheet called TD.
L37: Get the all the teachers’ names and emails.
39. //Extract teacher and email from list, then return teacher's email
40. const tDetails = tAndEmails.filter((tAndEmail) => { return tAndEmail[0] == tName });
41. return tDetails[0][1];
42. }L40: Filter the list by the teacher’s name which will return the teacher’s name and email address. Store this in the tDetails array. Close the filter.
L41: Then return the teacher’s email address from the tDetails array.
Finally, let’s look at the function that sends the email.
44. function sendEmail(tEmail, latestSub, urlOfSS) {
45. //Get values from latest response
46. const name = latestSub[1],
47. itMaint = latestSub[2],
48. building = latestSub[3],
49. room = latestSub[4],
50. issueEng = latestSub[5],
51. issueSpan = latestSub[6],
52. timestamp = Utilities.formatDate(latestSub[0],
53. Session.getScriptTimeZone(), 'dd-MM-yyyy HH.mm');L44: Open the sendEmail function.
L46-51: Extract the individual values in the latest submission.
L52-53: Format the timestamp so it shows the date and time.
Next, we set up who we are sending the email to, which will depend if they selected IT or not on the form.
55. //Set up who to send email to
56. if (itMaint === "IT") {
57. var emailTo1 = 'baz@bazroberts.com, it@bazroberts.com';
58. }
59. else {
60. var emailTo1 = 'baz@bazroberts.com, maintenance@bazroberts.com';
61. }L56-61: If they have selected IT, set up the first emails, otherwise set up the second ones. Note, I’ve used var here so that the emailTo1 variable is visible below in the next line.
Now let’s build the email.
63. const emailTo = emailTo1 + ", " + tEmail;
64. const subject = "Incidencia - " + timestamp;
65. let emailBody = HtmlService.createTemplateFromFile('email')
66. .evaluate().getContent();
67. emailBody = emailBody.replace('#TEACHERNAME#', name);
68. emailBody = emailBody.replace('#BUILDING#', building);
69. emailBody = emailBody.replace('#ROOM#', room);
70. emailBody = emailBody.replace('#ISSUEENG#', issueEng);
71. emailBody = emailBody.replace('#ISSUESPAN#', issueSpan);
72. emailBody = emailBody.replace('#SSURL#', urlOfSS);
73.
74. MailApp.sendEmail(emailTo, subject, emailBody, {
75. htmlBody: emailBody, replyTo: emailTo, name: 'Incidencia'
76. });
77. }L63: Add the teacher’s email to the other emails.
L64: Set the email subject line.
L65-66: Get the email HTML from the email HTML file.
L67-72: Replace the placeholders in the email with the data from the latest submission.
L74-77: Send the email. Here I’ve added the name “Incidencia” (issue) which will replace my name in who this email is from.
79. function include(filename) {
80. return HtmlService.createHtmlOutputFromFile(filename).getContent();
81. };
L79-81: I’ve added the function include which will add the CSS file to the email file.
Email template – Email.html
This file is the template for the email we’re going to send.
1. <!DOCTYPE html>
2. <html>
3.
4. <head>
5. <base target="_top">
6. <?!= include('css'); ?>
7. </head>
8.
9. <body>
10. <div>
11. <h3>Issue / Incidencia</h3>
12. <table class="table">
13. <tr>
14. <td class="boldRightBlue">Nombre:</td>
15. <td>#TEACHERNAME#</td>
16. </tr>
17. <tr>
18. <td class="boldRightBlue">Edificio y aula:</td>
19. <td>#BUILDING#: #ROOM#</td>
20. </tr>
21. <tr>
22. <td class="boldRightBlue">Problema:</td>
23. <td>#ISSUEENG#</td>
24. </tr>
25. <tr>
26. <td class="boldRightBlue">Traducción:</td>
27. <td>#ISSUESPAN#</td>
28. </tr>
29. </table>
30. <hr />
31. <p><a href=#SSURL#>Incidencias/Issues</a></p>
32. <hr />
33. </div>
34. </body>
35.
36. </html>
L1-5: Leave the default HTML there.
L6: We add the styling from the css file by using the scriptlets and calling the include function.
L11: Add the header.
L12: We’re going to send the issue data in a table. Use the class ‘table’ to format it.
L13: Add a table row.
L14: Add a table cell and style it by using the boldRightBlue class.
L15: Add another table cell, this time with the data, in this case, the placeholder which will be replaced with the teacher’s name.
L16: Close the table row.
L17-28: Add another 3 table rows in the same way.
L29-30: Close the table and add a horizontal line.
L31-32: Add a link to the Issue log spreadsheet and another horizontal line.
L33-36: Close the Div, body and HTML.
CSS styling – css.html
Next, let’s add the CSS styling.
1. <!DOCTYPE html>
2. <html>
3.
4. <head>
5. <base target="_top">
6.
7. <style>
8. h3,
9. p {
10. font-family: verdana, helvetica, sans-serif;
11. color: black;
12. }
13.
14. p {
15. text-align: justify;
16. font-size: 12;
17. }
18.
19. h3 {
20. font-size: 14;
21. }
22.
23. hr {
24. color: blue;
25. }
26.
27. td {
28. line-height: 1.8;
29. vertical-align: top
30. }
31.
32. .table {
33. font-family: helvetica neue, calibri, sans-serif;
34. color: black;
35. width: 100%;
36. }
37.
38. .table td {
39. padding: 5px;
40. border: 1px solid #000000;
41. }
42.
43. .boldRightBlue {
44. font-weight: bold;
45. text-align: right;
46. background-color: #a4c2f4;
47. width: 30%;
48. }
49. </style>
50. </head>
51.
52. </html>
L8-12: Set the font and font colour for the header and paragraph.
L14-17: Set the text alignment and font size for the paragraph.
L19-21: Set the font size for the header.
L23-25: Set the colour of the horizontal line.
L27-30: Set the height of the table cell and the vertical alignment.
L32-36: Set the font, font colour, and width of the table.
L38-41: Set the padding and format the border for the table and the table cell.
L43-48: Set the format for the cells on the left-hand side of the table.
L49-52: Close the tags.
Send an Issue closed email – 2-Close issue.gs
The next part is to send a similar email to the one above but this time to inform everyone that the issue has been closed. This will be called when the status is changed from open to closed (Abierto to Cerrado) and is run from an onEdit trigger, which ‘listens’ for edits on the sheet.
Note, I’ve added some data validation to the status (Estado) column so that it produces the Abierto/Cerrado menu options.
1. //Rev1
2. function onEdit(e) {
3. const sh = e.source.getActiveSheet();
4. const columnNo = e.range.getColumn();
5. const rowNo = e.range.getRow();
6. const ssUrl = e.source.getUrl();L2: Open the onEdit function and include the event parameter (e), which includes certain data if there is an edit.
The event includes the following data:
{value=Cerrado, authMode=LIMITED, source=Spreadsheet, range=Range, oldValue=Abierto, user=baz@bazroberts.com}
L3: First, let’s get the active sheet by getting the source property of the event. Note, the dot notation used.
L4-5: We can also get the column and the row where the edit was made, by using the range property.
L6: Get the URL of the spreadsheet.
8. if (sh.getName() === "ISSUES" && columnNo === 9) {
9. if (e.value === "Cerrado") {
10. let issueRowRange = sh.getRange(rowNo, 1, 1, 12)
11. let issueRow = issueRowRange.getValues()[0];
12. issueRowRange.setBackground('#b6d7a8');
13.
14. const tName = issueRow[1];
15. const tEmail = getTeacherEmail(tName);
16. sendClosedEmail(issueRow, tEmail, ssUrl);
17. }
18. }
19. }Next, I only want the script to run if there is an edit on the ISSUES sheet and in the status column.
L8: Do this with an if statement.
L9: Plus, only run it if the status has been changed to “Cerrado” (closed). Note, we could add this in the same if statement above.
L10: Get the range of the row which was edited.
L11-12: Get the values in that row and change the cell background to green to clearly show it’s closed.
L14: Get the teacher’s name.
L15: Call the getTeacherEmail function to get the teacher’s email. This function is in the first script file, and there’s no need to repeat here, it will still call it even if it’s in a different script file.
L16: Call the sendClosedEmail function.
L17-19: Close the two if statements and the function.
21. function sendClosedEmail(issueRow, tEmail, ssUrl) {
22. const name = issueRow[1],
23. itMaint = issueRow[2],
24. building = issueRow[3],
25. room = issueRow[4],
26. issueEng = issueRow[5],
27. issueSpan = issueRow[6],
28. comments = issueRow[7],
29. timeZone = Session.getScriptTimeZone(),
30. timestamp = Utilities.formatDate(issueRow[0],
31. timeZone, 'dd-MM-yyyy HH.mm'),
32. closedDate = Utilities.formatDate(new Date(),
33. timeZone, 'dd-MM-yyyy HH.mm');L21: Open the sendClosedEmail function.
L22-28: Get the data in the row and allocate them to individual variables.
L29-33: Format the timestamp of the form submission and create a new date and time which will be the closed date.
35. //Set up who to send email to
36. if (itMaint === "IT") {
37. var emailTo1 = 'baz@bazroberts.com, it@bazroberts.com';
38. }
39. else {
40. var emailTo1 = 'baz@bazroberts.com, maintenance@bazroberts.com';
41. }
42.
43. const emailTo = emailTo1 + ", " + tEmail;
44. const subject = "Incidencia - " + timestamp;
45. let emailBody = HtmlService.createTemplateFromFile('email2')
46. .evaluate().getContent();
47. emailBody = emailBody.replace('#TEACHERNAME#', name);
48. emailBody = emailBody.replace('#BUILDING#', building);
49. emailBody = emailBody.replace('#ROOM#', room);
50. emailBody = emailBody.replace('#ISSUEENG#', issueEng);
51. emailBody = emailBody.replace('#ISSUESPAN#', issueSpan);
52. emailBody = emailBody.replace('#CLOSEDDATE#', closedDate);
53. emailBody = emailBody.replace('#COMMENTS#', comments);
54. emailBody = emailBody.replace('#SSURL#', ssUrl);
55.
56. MailApp.sendEmail(emailTo, subject, emailBody, {
57. htmlBody: emailBody, replyTo: emailTo, name: 'Incidencia'
58. });
59. }
L36-41: As we saw in the first script, we check to see which type of issue it is to then send the email to the relevant people.
L43-44: Add the teacher’s email and set the email subject line.
L45-54: Get the email2 template and replace the placeholders.
L56-59: Send the email.
Closed email template – Email2.html
This template is very similar to the email we saw above. The main difference is that this one includes the closed date and the comments about the issue.
1. <!DOCTYPE html>
2. <html>
3.
4. <head>
5. <base target="_top">
6. <?!= include('css'); ?>
7. </head>
8.
9. <body>
10. <div>
11. <h3>Issue CLOSED / Incidencia CERRADA - #CLOSEDDATE#</h3>
12. <table class="table">
13. <tr>
14. <td class="boldRightBlue">Nombre:</td>
15. <td>#TEACHERNAME#</td>
16. </tr>
17. <tr>
18. <td class="boldRightBlue">Edificio y aula:</td>
19. <td>#BUILDING#: #ROOM#</td>
20. </tr>
21. <tr>
22. <td class="boldRightBlue">Problema:</td>
23. <td>#ISSUEENG#</td>
24. </tr>
25. <tr>
26. <td class="boldRightBlue">Traducción:</td>
27. <td>#ISSUESPAN#</td>
28. </tr>
29. <tr>
30. <td class="boldRightBlue">Comentarios:</td>
31. <td>#COMMENTS#</td>
32. </tr>
33. </table>
34. <hr />
35. <p><a href=#SSURL#>Incidencias/Issues</a></p>
36. <hr />
37. </div>
38. </body>
39.
40. </html>
Update list of teachers on form – 3-updateTsOnForm.gs
For the emailing to work, it’s essential that all the teachers are on the form. To do that I’ve connected it to a central list, which will keep the form up-to-date every day via a trigger. This is very similar code to what we saw in the last project.
1. //Rev1
2. function updateTsOnForm() {
3. const ssTD = SpreadsheetApp.openById('1yA2SXlN8Ob3yPseXGo9X5bip0H_3b08w2JkDxUdAk7s'),
4. shTD = ssTD.getSheetByName("TD"),
5. teachers = shTD.getRange(2, 1, shTD.getLastRow() - 1).getValues(),
6. form = FormApp.openById('1XUpr__4C_TCVNWmXxLnQ3GDNV3GP8l4Dod0dXHyVm3Y'),
7. allItems = form.getItems(),
8. formQ3 = allItems[2].asListItem();
9. formQ3.setChoiceValues(teachers);
10. }
L2: Open the function.
L3-4: Get the TD sheet on the Teacher Details spreadsheet.
L5: Get the list of teachers’ names.
L6: Get the Issues log form.
L7: Get all the questions on the form.
L8: Get the third question, which asks for the teacher’s name and get it as a list item, as it’s a drop-down menu.
L9-10: Set the options for that question using the list of teachers’ names and close the function.
Create a menu and highlight the latest row – onOpen.gs
In this final script, when the issues log spreadsheet is opened, it’s going to get the latest row and highlight the status cell, ready for the maintenance or IT person to update it. Plus, it will make a menu to run the update teachers script, in case it needs updating manually.
1. //Rev1
2. function onOpen() {
3. const sh = SpreadsheetApp.getActiveSheet();
4. sh.getRange(sh.getLastRow(), 9).activate();
5. const ui = SpreadsheetApp.getUi();
6. ui.createMenu('ADMIN')
7. .addItem('Update names on form', 'updateTsOnForm')
8. .addToUi();
9. }
L2: Open the function.
L3: Get the active sheet.
L4: Get the last row and the cell in column 9, i.e. the status cell and highlight it.
L5-9: Get the UI and add the menu. Close the function.
Setting up the triggers
This project has 3 triggers, onFormSubmit, onEdit, onOpen. The last one runs automatically as it uses the keyword onOpen in the function name, but the other two need to be set up.
Set up the onFormSubmit one so that it sends the first email, notifying the relevant people of the issue. Click on Triggers on the menu on the left-hand side.
Then click ‘Add Trigger’.
Select the sendIssue function, and select the event type as ‘On form submit’. Then click ‘Save’.
The onEdit one is so that when the status is changed to closed (‘Cerrado’) it will send the closed email to the relevant people. An onEdit trigger can run automatically without having to set up a trigger, like we do with the onOpen one but there are limitations.
If we change the status on the sheet at the moment, you will see an error in the Executions list.
In this script as we need to open a separate spreadsheet file, using a simple trigger can’t do this as it doesn’t have the permissions to do that. So, we need to set up a trigger so it runs off your account.
You should now have two triggers set up.
Fill in the form and submit it. The relevant people receive the email below:
Once the issue has been resolved, the status is changed to closed (‘Cerrado’) on the sheet and the same people receive the closed email like this:
Files
Here you can make a copy of the spreadsheet and form used in this script project:
This file contains all the code. If you’re using this to learn how to use Apps Script, I’d recommend deleting the code in the script project and adding it as you work your way through the tutorial.
FURTHER IDEAS
- Highlight the row red again if the issue is reopened
- The building option may not be necessary and so the form and code could be simplified.
- Add a translation to English for the comments in the closed email.
- Send a weekly summary of the issues to the maintenance/IT manager.
- To easily open the form, you could set up a QR code and post it on the wall, so teachers could just use their camera on their phone to open the form quickly. I’ve added a tab on the spreadsheet called QR, which contains a formula in cell B2, which creates the QR code.
This post is taken from my book “Google Apps Script Projects 1“, available on Amazon here.
Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).
a
