Make & Send Kids Reports

In this post, we’re going to look how we can create kids reports from a Google Sheet and convert them into individual PDFs made from Google Docs, then email them to the parents. This could of course be adapted to send any types of reports.

The teachers will fill in the spreadsheet and as its a Google Sheet will be able to do it at the same time as each other, using drop-down menus to make it quick and easy to do. The code will convert this information into individual PDF reports, which will be stored on our Drive and then sent via email directly to the parents. The process is completely paperless and free!

---

Setting up the spreadsheet

In one sheet, I’ve set up the columns as below:

We have two classes with a few students in each. Then we have the areas we want to report to the parents. In the blue part, their language skills and knowledge; in the pink part their behaviour; their attendance, exam marks, and the recommendation for the following year. In the final column, we will add the links to the individual PDFs.

Create a second sheet and call it “Options”. This is where we’re going to store the options for some drop-down menus on the main sheet. First, let’s set up the drop-down menus for the language feedback.

Select the cells where you want to add the drop-down menus.

Right-click, click on View more cell actions and select Data validation.

In the dialogue box that will appear, click on the box next to “List from a range”.

This will ask you for where that data is. Instead of typing in the box, open the Options sheet.

Then select the feedback options for language. This will add the range in the dialogue box.

Click ok, then in the main dialogue, I usually select “Reject input”, so that teachers can’t add any other comments in this columns. Click Save.

As we can see, it’s now added the drop-down menus.

Clicking on one of the cells, will open the menu and the teacher can just select the comment they want to add.

Now do the same for the behaviour comments.

So, you should have a different set of feedback options this time.

Now in the exam marks, let’s prevent the teachers adding numbers that are not between 0 and 10. Here in Spain, the norm is to receive a mark out of ten, which sometimes our teachers don’t realise or forget and they add a percentage mark. Select the cells, then open the data validation dialogue as before.

This time select “Number” from the criteria and “between”. Then add 0 and 10 in the boxes. This time I’ve ticked the “Show validation help text”. This will show the message in the box, if they don’t write a number between 0 and 10.

Here’s the help text they would see.

Finally, as this is the end of the year, the parents will want to know, if their child can go to the next level, needs to repeat the year, or can jump a level. So, for each we will add the 3 options. As the options will depend on their current level, we’ll need to do this per level. So, first select the recommendation cells for class A1.

Open the data validation and select the 3 options next to Class A1. As you can see, the menu options don’t have to be in columns, but can be across rows too.

As we can see, it’s added the options available for that level. We then do the same for Class A2, and so on.

Now the teacher fills out the sheet, choosing the options from the menus we’ve created, which makes it really quick for them to fill out. They also add the attendance figures and exam results. I usually add some conditional formatting to the menus, as it makes it more visually clear who’s doing well or not. See my post on Conditional Formatting if you’re not sure what to do.

---

Report template

Now, we need a blank report template. Here, I’ve created one in a Google Doc using tables to organise the information. As we’ll see in the code later on, accessing tables is easy to do, and so we will be able to add our students’ data with ease.

Create a folder where you’re going to store the finished reports. Then get the URL of the folder and paste it into the Options Sheets in cell A10, and the URL of the report template into cell A12.

---

The code to make the PDF reports

Once the teachers have filled in the data, we can run the program to make the individual reports in PDF format. From the Google Sheet open the Script Editor from the Tools menu. Delete the default code in there. I’m going to assume you know the basics of Apps Script, but I will explain what each step is doing.

1. function onOpen() {
2.  const ui = SpreadsheetApp.getUi();
3.  ui.createMenu('Reports')
4.    .addItem('Make pdfs', 'createReports')
5.    .addItem('Email reports', 'sendEmails')
6.    .addToUi();
7. }

First, I want to add a menu where we can run our code from.

Line 1: Call the function onOpen() so that when the spreadsheet is opened, the menu will appear.

Line 2: To set up a menu we need to get the spreadsheet UI (user interface). We do that by using getUi().

Line 3: We then create the menu on the menu bar using createMenu() and in the brackets give it a name. This is the name of the menu on the menu bar. Note, no semi-colon at the end.

Line 4: Then we need to add an item to it, which will run the createReports function we’re going to write. So, first we use addItem(), then in the brackets we need to state the name of the menu item, and the function we want connected to it. So, here I’ve called it ‘Make pdfs’ and it will run the createReports function when clicked.

Line 5: Do the same as line 4 for the second part of this program, which is to email the reports.

Lines 6-7: Finally, we need to add it to the UI. We do that by using addToUi(). Then we close the function with a curly bracket.

8. function createReports() {
9.   //Get data from sheet and remove header
10.  const ss = SpreadsheetApp.getActiveSpreadsheet();
11.  const sh = ss.getActiveSheet();
12.  const ssData = sh.getDataRange().getValues();
13.  ssData.shift();
14.  let rowNo = 2;

Line 8: Now, we write a new function called createReports.

First, we need to get some of the data from our active sheet. Make sure, when you run the code that the first page with the report information is open and not the Options page.

Line 10: Store the active spreadsheet in the variable ss.

Line 11: Store the active sheet in the variable sh.

I’ve used getActiveSheet() here and not the sheet by name just because if you have lots of students, you may split them into different sheets, so you would open the sheet you wanted and then run the code for that set of students. However, if you have a smaller set of students, you may prefer to do it all on one sheet, and therefore, use the getSheetByName() method.

Line 12: Then let’s get all the data on our sheet and store it in the variable ssData. We do this by using getDataRange() then getValues().

Line 13: We don’t need the header row in ssData, so we can remove that by using the shift() method.

Line 14: The start row number will be row 2. So, set up the variable rowNo with 2, which is the variable we’ll use to track which row we’re on as we loop down the rows.

Now, let’s get the report folder URL and the report template URL from the Options sheet. Then use the URLs to get the report folder and the report template key.

16.  //Get the report folder and report template URLs
17.  const shOptions = ss.getSheetByName('Options');
18.  const reportFolderUrl = shOptions.getRange(10, 1).getValue();
19.  const reportFolderKey = reportFolderUrl.replace("https://drive.google.com/drive/folders/", "");
20.  const reportFolder = DriveApp.getFolderById(reportFolderKey);
21.  const reportTemplateUrl = shOptions.getRange(12, 1).getValue();
22.  const reportTemplateKey = reportTemplateUrl.match(/[-\w]{25,}/);

Line 17: First, let’s get the Options sheet by name, using getSheetByName().

Line 18: Then, we get the report folder URL which is in A10 (row 10, column 1).

Line 19: We’ve got the URL but what we need is the folder ID. We can extract that from the URL by removing the first part of the URL up until /folders/. Here, we get the URL stored in reportFolder and use the replace() method, to replace one thing with another. In the brackets, I’ve added the first part of the URL, which we want to remove, then after the comma, add two quote marks, to show that we’re replacing the text with nothing. I.e. We’re removing it.

Line 20: Then get the report folder using its ID (key).

Line 21: Similar to before, but this time we get the report template URL.

Line 22: File URLs are a little more complicated, as we need to remove the first part, like above and also the /edit part at the end. The best way to do this is to use a regular expression. This will look for a particular pattern in the URL and remove the parts we don’t need. First we use the match() method and then in the brackets add the regular expression. It looks strange, but just add what’s there, believe me it works!

Note, all the code written so far, is before the loop we’re about to make, as it only needs to be run once, so we don’t want it within the loop, as it would be run multiple times, slowing down the program. Now, we create a loop to move down the rows of data, and for every row, it will grab the data needed and create a report from it.

24.  //Loop down rows
25.  for (const row of ssData) {

Line 25: We set up our for of loop. The variable row is each row of data as the loop goes thru the ssData array.

26.    //Get data from sheet
27.    let [studentName, , group, teacher, speaking, listening, pronunciation, writing, grammar, vocabulary, attention, behaves, participates, arrives, missed, classesInTerm, written, oral, recommendation] = row;

Line 27: We can extract the individual pieces of data in each row by “destructuring” the row array. Rather than writing out individual let statements for each of the variables, we can do it in one go, by listing all the variables we want to assign, and put them within the square brackets. Any we don’t want to assign we add a gap, for example, the second column (email address).

Now, we need to make a copy of the blank template.

29.    let reportTemplate = DriveApp.getFileById(reportTemplateKey);
30.    let newReport = reportTemplate.makeCopy(`Report-${group}-${studentName}`);
31.    let newReportId = newReport.getId();

Line 29: First, we get our template file by its ID. Use the DriveApp Class and getFileById() and add the variable reportTemplateKey where we stored the ID in.

Line 30: Now, make a copy of it, by using makeCopy() of the variable reportTemplate we just created. To dynamically name the file, we can use a template literal. We put the whole name within back ticks, then when we want to add a variable, we use the ${variable} syntax.

Here, I’m going to call it “Report-Group name-Student’s name”, so I state the text, then, as the class and student’s name will come from the data for that particular student, we add the variables group and studentName. So, we will have something like, “Report-Class A1-Barney Pebble”.

Line 31: Finally, we need to get the ID of the new file, so we can then work with it. Use getId() for this.

Now let’s get our new report and add the student’s data into it.

33.    //Open new Doc and get its body
34.    let openReport = DocumentApp.openById(newReportId);
35.    let body = openReport.getBody();

Line 34: To edit the document we need to ‘open’ it by using openById() and adding its ID in the brackets.

Line 35: To write something on a document, we first need to get its body. So, logically we use getBody() and store this in the variable body.

Now, we need to access the tables one by one and the rows and cells within each one.

37.    //Fill report details
38.    let table1 = body.getTables()[0];
39.    table1.getRow(0).getCell(1).setText(studentName);
40.    table1.getRow(1).getCell(1).setText(group);
41.    table1.getRow(2).getCell(1).setText(teacher);

Line 38: First, we need to get the first table in the document. We get the body, then get the table using getTables() and state which table number we want. As with arrays, the first table will be table 0, so we add a 0 in square brackets.

Line 39: Now, we need to get the table row we need and the cell number on that row. We use getRow() to get the row, and getCell() to get surprise, surprise, the cell. So, for the student’s name we need the first row and the second cell in that row, so we state getRow(0) and getCell(1). Then we use setText() to state what text we want to add in the cell. In the brackets we add the studentName variable.

The rest of the lines are in a similar format, so for example, the class name is in row 2 (1) and cell 2 (1).

43.    let table2 = body.getTables()[1];
44.    table2.getRow(0).getCell(1).setText(speaking);
45.    table2.getRow(1).getCell(1).setText(listening);
46.    table2.getRow(2).getCell(1).setText(pronunciation);
47.    table2.getRow(0).getCell(3).setText(writing);
48.    table2.getRow(1).getCell(3).setText(grammar);
49.    table2.getRow(2).getCell(3).setText(vocabulary);
50.
51.    let table3 = body.getTables()[2];
52.    table3.getRow(0).getCell(1).setText(attention);
53.    table3.getRow(1).getCell(1).setText(behaves);
54.    table3.getRow(0).getCell(3).setText(participates);
55.    table3.getRow(1).getCell(3).setText(arrives);
56.
57.    let table4 = body.getTables()[3];
58.    table4.getRow(0).getCell(1).setText(missed);
59.    table4.getRow(0).getCell(3).setText(classesInTerm);

We then get the next table and repeat the same process and so on.

 61.   let table5 = body.getTables()[4];
 62.  table5.getRow(0).getCell(1).setText(written.toFixed(1) + " /10");
 63.   table5.getRow(0).getCell(3).setText(oral.toFixed(1) + " /10");
 64.   table5.getRow(1).getCell(1).setText(recommendation);

With the exam marks, I want to add the number to one decimal place and add some text to show it’s out of 10.

Lines 63-64: In the brackets for setText we state the variable written and add the toFixed() method and as we want 1 decimal place, we add 1 in the brackets. Then to show it’s out of 10,  we add a plus and the text ” /10″.

66.    //save edits
67.    openReport.saveAndClose();

Line 66: As we ‘opened’ the report to edit it, save and close it to save all the changes. We get the new file’s ID in openReport and use the saveAndClose() method.

Now, let’s convert this file into a PDF.

69.    //Save current Doc as pdf        
70.    let pdf = DriveApp.getFileById(newReportId).getAs('application/pdf');
71.    pdf.setName(openReport.getName() + ".pdf");
72.    let reportPdf = DriveApp.createFile(pdf);
73.    let newPDFId = reportPdf.getId();

Line 70: Get the new file by its ID and convert it to a PDF by using getAs() and in the brackets ‘application/pdf‘. Store that in pdf.

Line 71: Then, we need to give the PDF a name. Here, I just want it to be the same as the Google Doc. So, we use setName() and in the brackets, we get the name of the Google Doc using getName() and add the .pdf extension.

Line 72: Now, we create the new PDF using the DriveApp Class and createFile() and in the brackets we pass the pdf variable.

Line 73: Finally, get the ID of this new PDF, which we will use to add its URL to the sheet and in the emails.

This has made a Google Doc and a PDF in our My Drive, so as we want the PDF in our reports folder we need to move it there.

75.    //Move pdf in report folder, delete original Doc  
76.    reportPdf.moveTo(reportFolder);
77.    DriveApp.getFileById(newReportId).setTrashed(true);

Line 76: Move the PDF to the report folder using moveTo().

Line 77: Next, we want to remove the original Google doc on our My Drive. Here, we get the file ID using getFileById() and then use setTrashed() and state true in the brackets. This will remove it to the trash.

Now, let’s add the link to the new PDF in our report folder on our sheet, so we can access it directly from the Sheet and so that we can add it to the email to the parents.

79.    //Add PDF URL to Sheet using PDF ID
80.    let cell = sh.getRange(rowNo, 20);
81.    cell.setFormula('=HYPERLINK("https://drive.google.com/file/d/' + newPDFId + '")');
82.    rowNo++;
83.  }

Line 80: We get the cell we want to add the PDF link into, by getting the current row number and column T (20th).

Line 81: Then, we set the formula in that cell to show a hyperlink to the PDF report. We do this by using setFormula() then adding our formula. The text parts need to be between quotes and variables without. Pay close attention to the use of quote marks here.

Line 82: We increase the row number counter by one.

Line 83: Close the loop with a curly bracket.

84.  ss.toast("Reports made.", "Finished");
85. }

I always like to display a message to the user, to show the program has finished. So, here, we’ll add a toast message, which will pop up from the bottom of the screen telling us the reports have been made.

Line 84: Use the toast() method and state the message and title.

Line 85: Close the function with a curly bracket.

---

Making the reports

Now, we’re ready to make the reports. Refresh the page, so that the Reports menu appears. Then click Reports>Make pdfs to run the program. The first time you run it, it will ask for authorisation, click the review and allow buttons.

As we can see on the sheet, it has added the links to the PDFs for each student.

In our reports folder, it has created a PDF report for all our students.

If we open one of the reports, we can see it has been populated with that student’s information.

It takes about 50 seconds to create all the reports. As there is a runtime limit of 6 minutes, normally you can only make about 50 reports this way in one go. To make more, either store the students on different sheets, or what we do is tweak the code, to ask for a starting row number and finishing row number, so the reports are made in batches. This also means we can make the reports as soon as a particular class’s data is ready.

---

Emailing the reports to the parents

The final part will be to send the reports to the parents. We could include this code within the one above, but as we don’t want our program running too long, I’ve set it up so it runs separately.

We’re going to send the email below:

It contains our school’s logo (a fictitious one), it’s personalized by including the student’s name in the email title and in the main text, and it contains a link to the report PDF.

So, how do we do this?

In a new script file (from File>New) write the following code.

1. function sendEmails() {
2.  const sh = SpreadsheetApp.getActiveSheet();
3.  const ssData = sh.getDataRange().getValues();
4.  ssData.shift();

Line 1: Set up a new function called sendEmails.

Line 2: Get the active sheet and store it in sh.

Line 3: Get the values on the active sheet.

Line 4: Remove the header row from the ssData array.

6.  //Get school logo from Drive
7.  const image = DriveApp.getFileById("0B3zSwpJCAxswMEpablpOZGt5ZXc").getBlob();

As we’re going to add the school logo to make it more professional looking, we need to get the image, which is stored on our Drive.

Line 7: We get the image’s ID (the random numbers and letters part in the URL) and use getBlob(). The blob will contain the data of the image.

9.  //Loop down the rows of students
10.  for (const row of ssData) {

Now we need to set up a loop to go down the list of students.

Line 10: Set up a for of loop. This will loop down all the rows.

12.  //Get data on row
13.  let name = row[0], emailTo = row[1], pdfLink = row[19];

Now, we need to get the student’s name, email and pdf link.

Line 13: With every loop down the rows, we need to get the student’s name, email, and the report PDF link. We do that by getting the values in the respective positions in the row array.

Now, we need to set up our message and store it in the variable message.

15.  //Set up message
16.  let message = '<img src="cid:logo" height="40" width="60"/>';
17.
18.  message += "<p>Dear parents,</p>";
19.  message += "<p>Please find the end of year report for " + name + ".</p>";  
20.  message += "<p>Please click on the link below to open the PDF.</p>";
21.  message += '<p><a href='+pdfLink+'>REPORT</a></p>';
22.  message += "<p>Best regards,</p>";
23.  message += "<p>Barrie</p>";

Line 16: First, let’s start with the school logo. Here, we’re using a bit of HTML to add the image (using the img tag) and we’ll state the id as logo. I’ve also controlled the height and width here. Pay attention to the quote marks.

Lines 18-23: Each line is adding to the message variable using +=. To keep it simple, each line will be a separate paragraph (using the < p > tags). We open the < p > tag add the text we want and then close it using < /p >. Text needs to be within quote marks and variables like name, need to be without but are connected with the plus marks.

25.  //Send email
26.  MailApp.sendEmail(
27.    emailTo, "Report-"+name, "",
28.    { inlineImages:{ logo:image },
29.      htmlBody:message });
30.  }
31. }

Finally, we need to send the email. This contains 3 main parts: email you’re sending to, email title, and email message.

Line 26: We use the MailApp and sendEmail() to send emails.

Line 27: We use emailTo to state the current email address we want to send to. Then in the email title I want to call it “Report-and the student’s name”, so the parents can see it’s a message about their child. The message part we’ll just add 2 double quote marks, as the next lines will add the details of the message. End the line with a comma.

Line 28: In curly brackets we’ll add the options we want. First, add the inline image, using inlineImages: then within another set of curly brackets, state the id logo: and the variable image (where the image blob is stored).

Line 29: Then we also need to add the body, which is a HTML body (htmlBody:) and the message variable, which contains the message we wrote above.

Lines 30-31: Close the loop and then close the function.

In seconds, this will send the personalised emails to all the parents.

We introduced a system similar to the one I’ve described above, which replaced an antiquated card report system and its made the whole process so much easier for everyone involved, whilst providing the necessary information to the parents in a more modern way.

One limitation with this system is that, if you are using a Gmail account, you can only send 100 emails a day (a Google Workspace account allows you to send 1,500), so if you have more students and want to send all the emails in 1 day, you will need to use a Google Sheets add-on like YAMM and pay for more emails (e.g. up to 400 in a day), or send the emails from different email accounts, not ideal but a workaround.

---

Here’s a copy of the spreadsheet with the code and here’s the report template.

You can find the code here at GitHub.