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 and select Data validation from the menu.
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.
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.
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.
Lines 5-6: Finally, we need to add it to the UI. We do that by using addToUi(). Then we close the function with a curly bracket.
You’re likely to use this chunk of code for numerous programs, so I would save it as a script editor file in your Drive, so you can just copy and paste it in.
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 sheet in the variable ss.
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 11: 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(). Later on we will get the data we need from this variable, rather than get it from the sheet every time, which would slow down the program.
Line 12: Next, we need to know where our data ends, so we get the last row number, using getLastRow().
Line 13: The start row number will be row 2, let’s set up the variable rowNumber with 2. This is the counter we’re going to use to go down each row and get that particular student’s data.
Line 14: We will sometimes be using row numbers and sometimes the row number within an array, and as arrays numbers start at 0 not 1, we need a number that is 1 less than the rowNumber. We’ll store it in the variable rowNumbersA. I could have just written = 1, but if we ever change our sheet and the start row number changed, we would only have to change one variable.
Now, let’s get the report folder URL and the report template URL from the Options sheet.
Line 17: First, let’s get the Options sheet by name, using getSheetByName().
Line 18: Then, we get the 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: Similar to before, but this time we get the report template URL.
Line 21: 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.
Line 24: We set up our for loop, starting at row number 2, continuing until the row number is equal to the last row (the last student on our sheet), and it will move down 1 row at a time.
Now, let’s get the data about the student, which we stored in ssData. Every line is similar, it’s just getting a different position each time which corresponds with the different columns on the sheet.
Line 27: We set up the variables and start off with the student’s name, which we’ll store in studentName. The variable ssData is an array of data, as it contains all the data from all the rows and all the columns on our sheet. So, to get the specific data we want we need to tell it, which ‘row’ it’s on and the ‘column’. Our references will have to be array references so will be one less than are on the sheet. The row we use the rowNumberA variable, so the first time we go around the loop, this will be 1 and this relates to row 2 on the sheet. The column is 0 as this relates to column 1 on the sheet.
At the end, add a comma, to connect it to the next variable, without having to repeatedly write var every time.
Continue doing the same for all the pieces of data we need and on the final line, close it by adding a semi-colon.
This chunk of code is optional, as we could put the ssData parts directly in the report part below, but using easy to understand variable names it helps us remember which piece of information is which, so we don’t mix any up.
Now, we need to make a copy of the blank template.
Line 46: 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 47: Now, make a copy of it, by using makeCopy() of the variable reportTemplate we just created. In the brackets we state the name of the new file. Here, I’m going to call it “Informe-Class 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 class and studentName. So, we will have something like, “Informe-Class A1-Barney Pebble”.
In case you were wondering, I’ve just realised I left the first bit (“Informe”) in Spanish, yours would be ‘Report’. The code I’ve adapted this from, was for our reports, which are all in Spanish.
Line 48: 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.
Line 51: To edit the document we need to ‘open’ it by using openById() and adding its ID in the brackets.
Line 52: 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. We then set the text in this cells using the variables we created in lines 27 to 44.
Line 55: 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 56: 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).
We then get the next table and repeat the same process.
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 79-80: 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″.
Line 84: 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.
Line 87: 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 88: 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 89: Now, we create the new PDF using the DriveApp Class and createFile() and in the brackets we pass the pdf variable.
Line 90: 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. Unfortunately, there isn’t a move method, so we’ll have to add a copy and then remove the original.
Line 93: Get the report folder by getting its ID from the reportFolderKey variable we created in line 19.
Line 94: Add a copy of the pdf to the report folder by getting the folder ID, then using addFile() and in the brackets adding the report PDF file.
Line 95: We then remove the original PDF file from My Drive. With My Drive, we just use DriveApp then the removeFile() method then state the file we want to remove.
Line 96: Finally, we want to remove the original Google doc on our My Drive. Removing a Google document is a bit different from removing a PDF. 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.
Line 99: We get the current row using rowNumber and get column 20(T) the PDF URL column.
Line 100: 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.
Note, it is possible to get the file’s URL and add that directly here, but it tends to be a bit longer, as it contains unnecessary information at the end of it, so I prefer to do it this way.
Line 102: We’re at the end of our loop, so we need to increase our array row number counter by one, so we state the variable rowNumberA and add two pluses.
Line 103: Close the loop with a curly bracket.
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 104: Get the active spreadsheet and use the toast() method. Then state the message, title, and how many seconds you want the message displayed.
Line 105: 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.
First, let’s update the onOpen() function, so that an “Email reports” option appears in the menu.
Here, I’ve just added line 5. When we open the report spreadsheet again, we will see both menu options.
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.
Line 1: Set up a new function called sendEmails.
Line 2: Get the active sheet and store it in sheet.
Line 3-4: Get the last row and column, for later use.
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.
Now we need to set up a loop to go down the list of students.
Line 10: Set up a for loop, starting on row 2, and which will stop when it gets to the last row and sends the last student’s report.
Now, we need to get the student’s name, email and pdf link.
Line 13: First, we get the data on the latest row and store it in data.
Lines 14-16: Then, let’s store the student’s name, email, and pdf link in separate variable ready to use in the email. For example, the student’s name is in the first column and so we get it from position 0 in the data array.
Now, we need to set up our message and store it in the variable message.
Line 19: 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.
Now, let’s add the rest of the message.
Lines 21-26: 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.
Finally, we need to send the email. This contains 3 main parts: email you’re sending to, email title, and email message.
Line 29: We use the MailApp and sendEmail() to send emails.
Line 30: 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 31: 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 32: 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 33-34: 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 you can only send 100 emails a day, 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.
You can find the code here at GitHub.
eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:
- “Beginner’s Guide to Google Drive” – iBooks store / Kindle store
- “Beginner’s Guide to Google Forms” – iBooks store / Kindle store
- “Beginner’s Guide to Google Sheets” – iBooks store / Kindle store
- “Beginner’s Guide to Google Docs” – iBooks Store / Kindle store
- “Beginner’s Guide to Google Slides” – iBooks Store / Kindle store
- “Google Sheet Functions – A step-by-step guide” – iBooks Store / Kindle Store