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.

Kids Reports - 54

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:

Kids Reports - 2

Kids Reports - 3

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.

Kids Reports - 4

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

Kids Reports - 5

Right-click and select Data validation from the menu.

Kids Reports - 6

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

Kids Reports - 7

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

Kids Reports - 8

Then select the feedback options for language.

Kids Reports - 9

This will add the range in the dialogue box.

Kids Reports - 10

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.

Kids Reports - 11

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

Kids Reports - 12

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

Kids Reports - 13

Now do the same for the behaviour comments.

Kids Reports - 14

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

Kids Reports - 17

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.

Kids Reports - 18

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.

Kids Reports - 19

Here’s the help text they would see.

Kids Reports - 20

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.

Kids Reports - 21

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.

Kids Reports - 26

Kids Reports - 25

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

Kids Reports - 24

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.

Kids Reports - 27

Kids Reports - 28


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.

Kids Reports - 29

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.

Kids Reports - 50


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.

Kids Reports - 30

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.

Kids Reports - 31

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.

Kids Reports - 50

Kids Reports - 32

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.

Kids Reports - 33

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.

Kids Reports - 34

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.

Kids Reports - 35

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.

Kids Reports - 36

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.

Kids Reports - 37

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.

Kids Reports - 38

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

Kids Reports - 39

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

Kids Reports - 40

Kids Reports - 41

Kids Reports - 42

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

Kids Reports - 43

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.

Kids Reports - 55

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.

Kids Reports - 56

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.

Kids Reports - 57

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.

Kids Reports - 58

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.

Kids Reports - 59

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.

Kids Reports - 60

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

Kids Reports - 51

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

Kids Reports - 52

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

Kids Reports - 53

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.

Kids Reports - 72

Here, I’ve just added line 5. When we open the report spreadsheet again, we will see both menu options.

Kids Reports - 71

We’re going to send the email below:

Kids Reports - 70

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.

Kids Reports - 62

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.

Kids Reports - 63

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.

Kids Reports - 64b

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.

Kids Reports - 65

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.

Kids Reports - 66

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.

Kids Reports - 67

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.

Kids Reports - 68 (1)

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.

Kids Reports - 69

Kids Reports - 70

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.


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

You can find the code here at GitHub.


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

Baz Roberts (Google+Flipboard / Twitter)


Apps Script Basics (5) – Arrays, Logger, Execution Transcript

In this post, we’re going to look at another key area in Apps Script and indeed many coding languages, that of arrays, which are just special variables which allow you to store multiple items in a single variable. I’ll also introduce you to the logger, where you can store see what’s happening in your code without for example, having to print it on a sheet. Plus, the execution transcript, which shows you the steps your code is taking and how long they take.


Introducing the logger

Following on from my post on loops, let’s take a look at some looped data in the logger. Here we have some data in a sheet. We’re going to loop down the data and put it one by one into the logger.

5Arrays - 1

5Arrays - 2

With all the examples in this post, we’re going to get the active sheet, so line 3, you’ll see repeated for each of the functions below. You’ll use this line repeatedly for a lot of programs you write, where you’re working with a spreadsheet and the current sheet.

5Arrays - 3

Now we want to loop down the list on the sheet.

Line 5: Here we know we have 20 numbers, so we set the loop to start at row 1 and finish at row 20 (<21). Here I’ve used the variable r as the counter.

Line 6: Then we get the cell at that point in the loop using getRange() and a row and a column number. The row will be the variable r and the column will always be 1 as it’s the first column, A. Then we get its value and store it in the variable listOfNumbers.

5Arrays - 4

Line 8: Now, still within the loop we log the numbers in the logger. This is easy to do, just use Logger.log() and in the brackets state what you want to add to it. In this case, it’ll be the contents of the variable listOfNumbers.

Lines 9 and 10: We close the loop with the curly bracket and then the function with another one.

5Arrays - 17

If you’re using a copy of this file I’m using, you’ll need to select the function you want to run. Just click on “Select function” form the toolbar and in this case “logNumbers“. Then press the play button to run the function. The first time you run it you will have to authorise it.

5Arrays - 15

Once run, you’ll want to open the logger. Go to the “View” menu and click “Logs”.

5Arrays - 16

As we can, it’s logged the values each time in went around the loop. So, it got “2” from row 1, then “4” from row 2, and so on. Each time placing them in the logger.

The logger is an excellent way to debug your code and to see what’s happening in your code and any point in it. You can put Logger.log() on any line and it’s really useful to see what’s going on, especially with what’s being stored in your variables.


Single items and multiple items

OK, now we have a place we can see our data, let’s move on to look at arrays. In this example, we’ll look at the storing individual items and multiple items in variables.

5Arrays - 5

Lines 13-14: As above.

Line 16: Here, I’ve stored the text string “Fred” in the variable item.

Line 17: This time I want to store 4 different names in the variable items. To do this, I need to set up an array. The contents of an array is shown by whatever is in between the square brackets. So here we have four names all within one array called items. To separate them, you need to use commas and as these are words we’ll needs to use quote marks.

5Arrays - 6

Lines 19-21: Now, let’s log the two variables in the logger and then close the function.

5Arrays - 18

Choose “array1a” from the toolbar and press play.

5Arrays - 19

As we can see, it shows the text string “Fred” and then the array below it. In the logger, it displays the array with square brackets, so you know it’s an array.


Accessing values in arrays

We often want to get at the information stored in an array. Let’s see how we do that. Here we’re going to access the two variables item and items and add them to our sheet.

5Arrays - 7

Line 24-28: The same code as the previous example.

5Arrays - 8

Line 30: Let’s add the content of item to the cell D1.

5Arrays - 9

Line 32: Now, let’s do the same with the content of items to cell E1.

5Arrays - 10

Finally, let’s get a specific item from our array and put it in cell F1. To do so, we need to state in which position in out array is the name we want. In arrays, each item of data has a position, starting in position 0. So, “Joan” is at position 0, “Paula” is in position 1, and so on. A common mistake is to forget that array are zero-based, i.e. they start at 0 not at 1.

Line 34-35: So, for example, to get “Paula” we need to get the array items at position 1. We do that by stating the variable name and in square brackets stating its position, e.g. [1].

Run the code and let’s see what we get.

5Arrays - 20

“Fred” has been added in cell D1 as expected.

However, in cell E1 we were expecting to add the contents of items but we only have the first name in the array! This is because we’re using setValue() which is expecting a single item. You may then think that we could use setValues() to get multiple values, but it doesn’t work like that with the array we’ve set up. This is because the variable items is still a single array, it just happens to contain multiple values (mini arrays). This is why we need to state the position of the items within the array. As we’ll see below there are of course ways to access multiple data.

Finally, in cell F1 we wanted to add the second name on the list and we have indeed, so the index we used has worked.


Using get and setValues() to get a range of values in one go

Here, we’re going to get a range of values, add them to the logger to see what they look like. Then add those values to our sheet and pick out a value from that range and add it to the sheet.

5Arrays - 11

Line 41: Let’s get the range of numbers we had in the first example from column A. Here, I’m using getRange() and stating the range in which those numbers are (using cell notation). Then, as there is more than one value, we use getValues() to get all the values in one go and store them in listOfNumbers.

5Arrays - 12

Line 43: Then, log the contents of listOfNumbers.

5Arrays - 13

Line 45: Now, let’s add that range of numbers to our sheet. Here, I’m going to put them in column H. I get the range of cells I want to add to. Note, this has to be the same number of cells as the original data, otherwise it will throw an error. Then, similar to getValues() we’re going to use its opposite, setValues() to add the values to those cells.

5Arrays - 14

Line 47-48: Finally, let’s extract one of the numbers from listOfNumbers and add it to cell I1. Similar to above, we’ll need to use setValue() then state the variable we want and the position of the data we want. So, here I want to get the second item, so I need to state position 1. Then close the function.

Run the code and you’ll see the list of numbers has been added to column H and that we have picked the second number out and added it to cell I1.

5Arrays - 22

Open the log and we’ll see that line 43 logged our data.

5Arrays - 21

Note, that this time every number has a set of square brackets around it and that all the data is enclosed within a second set of brackets. This shows that the data is stored in lots of little arrays within 1 larger array, what is known as an array of arrays. This is why, we can use setValues() as here we have multiple items and we can add them to our sheet in one go. Whereas, in line 32 we couldn’t use setValues() as the data was one item, as we can see in the log output below:

5Arrays - 19

This can all sound a bit complicated, but basically, we either have an array on its own. So, 1 array with multiple items in it. Or we have arrays within an array (an array of arrays). So, the code knows this array has multiple items, whereas the other one it treats it as a single item.


Looping through an array

A useful technique is to loop through an array. In this example, we’ll loop through an array of names and add those names one by one to a sheet, with each name on a different row.

5Arrays - 23

Lines 51-53: The same as array1a, we set up an array with 4 names.

5Arrays - 24

Line 55: First, we set up a for loop. This will go from 1 to 4. Here, we’re using the variable i to keep count. Note, that i is often used in loops, so you’ll see it a lot when you look at code examples.

Line 56: Now, we want to get a cell on the sheet (we’re going to use column K) and set its value to one of the names in the array. So, first we get the range and get the cell location, by adding the cell letter “K” to the current number in the loop (i), e.g. K1, K2, etc. Then we will set the value by getting the array items and getting one word at a time, starting at 0, so we need to subtract 1 to get the array position, e.g. [0], [1], etc.

Lines 57-58: We close the loop and the function.

5Arrays - 25

As we can see, on the sheet, it has taken the contents of the array and added the names in each of the cells.


Setting up an empty array and adding to it

Here, we’re going to loop down one list, add the values one by one to an array, then add those values one by one to another part of the sheet. We’re going to see how we create empty arrays, and how we can add items to an array.

5Arrays - 26

Line 63: First, let’s set up an empty array. We do that, simply by assigning a pair of square brackets to it. So, at the moment the variable listOfNames is empty but it’s an array.

5Arrays - 27

Line 65: Then, let’s set up our for loop, to count from 1 to 4.

5Arrays - 28

During each loop, we will get the value from the cell on the sheet then add it to the array.

Line 67: First, we get the cell using the getRange() method using the row and column. As we want to go down the rows from 1 to 4, we add the variable a in the row part. The column will remain fixed as there is only one column (K the 11th column). Then we get its value.

Line 68: Now, we need to add it to the array. To do this, we use the array method push(). So, we state the name of the array, add a dot then the word push. In the brackets we state what we want to add to the array. In this case, it will be the value we just got, which is in the variable originalList.

5Arrays - 29

Line 70: I’m going to log the names, so below we can see what’s happening in the array.

5Arrays - 30

Line 72: Now, as in the previous array example, we get the cell we want on the sheet, this time in column M, and add the names one by one.

5Arrays - 31

As we can see, it’s copied the names from column L to column M. Not the most exciting thing to do, but we can use a similar technique to copy data from one sheet to another or to another spreadsheet or even completely different place like a Google Doc, you can start to see the power of this.

5Arrays - 40

The log clearly shows us what’s happening, each time we go around the loop. It’s adding one word at a time at the end of the array.

5Arrays - 32

Also, in the View menu, there is the Execution transcript. Click on that to open it.

5Arrays - 335Arrays - 34

This shows you every action the code is taking, right from getting the active spreadsheet to adding the last name on the sheet. Along with the log, this is a vital tool to find out what is going on in your code and will help you troubleshoot. If the code comes across an error and stops, it will show you where it stopped by showing you the line number. It’s important to remember that this doesn’t always mean the problem is in that line, it may be due to something else not running correctly elsewhere but because there is some reference here to that, it is stopping here.

The other thing that is useful is to see how long the steps are taking and the overall time the code took to complete. Here it took 0.821 seconds. It doesn’t sound long, but as we’ll see in a moment, this can be reduced. There is a maximum runtime limit of 6 minutes, so you can imagine a program of hundreds of lines long will mean every second counts. Plus, you want your program to run as quick as possible, as you don’t want to sit there twiddling your thumbs while your program works it’s magic!


Reducing the execution time

The above code is fine and is useful in certain circumstances, but for the job we just did, that of copying one set of data and pasting it somewhere else, we can do it quicker.

5Arrays - 35

We start off as before.

5Arrays - 36

Line 81: Let’s get the range of values in one go, by using the getRange() method which needs 4 arguments: start row, start column, number of rows, number of columns. So, here we have row 1, column 11(K), 4 rows, and 1 column. Then get the values, as we saw in array2.

5Arrays - 37

Line 83: Then, we get the range we want to add the values to (column O) (remembering it has to have the same number of cells as the original data), then set the values from the variable originalList2.

5Arrays - 38

As we can see, it added the 4 names.

5Arrays - 39

Looking at the execution transcript, we first of all see it’s much shorter than the previous example and due to that it is also quicker. It took only 0.152 seconds, over 5 times faster.

The difference being that in the previous example, we got a value and set it, then got another value and set it, etc, four times. Whereas, in this example, we got a group of values and set them in one go.


Here’s the link to the sheet with the code in it:

LINK

Have a play around with the arrays, it’s the best way to learn how they work and what can be done with them.

If you want to know more about arrays and array methods, I recommend checking these two links out: ARRAYS and ARRAY METHODS


Here’s the full code:

5Arrays - 415Arrays - 425Arrays - 43


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

Baz Roberts (Google+Flipboard / Twitter)


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)


Apps Script Basics (4) – Loops

Here, we’re going to look at loops and how they can make repetitive tasks really easy and how they can save you so much coding. For example, if we want to print the word Hello! a number of times down a column without a loop, we would have to repeat the same line of code for each of the rows, as you can in the code below:

4Loops - 30

The only thing changing each time is the row reference in the getrange() method. Imagine if we wanted to write this 100 times, that’s a lot of code! With a loop, as we’ll see in the examples below, we can simplify this to just 1 loop and 3 lines of code. A loop just allows you to repeat a section of code as many times as you like.

Below, we’re going to look at seven simple examples of how loops can be used. In the spreadsheet, there are 2 sheets, one called Numbers and the other called Names.


Loop 1 – Print “Hello!” 20 times down column A

4Loops - 4

Open the script editor in your Google Sheet and replace the default code with the above one.

Line 2: We start with the function and here let’s just call it loop1.

Line 3: Now we want to get the sheet called Numbers. So, we use the SpreadsheetApp class, then get the active spreadsheet (i.e. the current one we’re using), then get the particular sheet we want, in this case called “Numbers”. For that we use the getSheetByName() method and add the name between quotes in the brackets. I’ve stored it in the variable ss, as we’re going to refer to the sheet again in the next part, so instead of writing out the SpreadsheetApp… part again, we can just use the variable name ss.

Now we want to set up a loop which will add the word Hello! to cell A1, cell A2, and so on, down to cell A20. Here, we’re going to use the common for loop. There are two main parts: the counter, in this case counting from 1 to 20, and what you want the loop to do each time it goes around.

4Loops - 5

Line 5: Start with the keyword for then open the brackets. There are 3 parts within the brackets:

  1. Starting point of the loop.
  2. Condition you want to check; if it’s true it continues the loop, if it’s false it stops the loop and continues on with the program.
  3. How much you want to increase the counter by each time it goes around the loop.

So, here we have the variable r (for rows) and we’re starting at 1, as we want to start at row 1.

Then we want to continue until 20, so the condition will be to continue while the variable r is less than 21.

As we want to put the name in all the rows, we want to increase r by 1 each time, so that it will print Hello on row 1, row 2, row 3, etc. Here I’ve used the common shorthand way to increase a value by 1, i.e. using a double + after the variable (r++).

All of this is contained in normal brackets. Then we need to tell it what to do, during each loop. To do this, we use the curly brackets, so we open the curly bracket ready to put some instructions in it.

4Loops - 5

Line 6: Now, we want to get the range and set its value to the word Hello!. So, first we get the sheet we want using the variable ss, then use the getRange() method. Here, we’re going to use the getRange method, that needs a row and column, to determine which cell it is. In the brackets, we have two arguments: (row number , column number). The column will always be the same, so we write 1, for the first column, A. But the row we want to change each time we go around the loop, so we use the variable r from our for loop. Then we set the value of that cell with the word Hello!.

Line 7: We close the for loop with a closed curly bracket.

Line 8: We close the function in the same way, with a closed curly bracket. So, you can see that the function does everything within its curly brackets, and the for loop does everything within its curly brackets.

When we run the code, the first time around the loop, r will be 1, so the range it will get will be row 1, column 1 (i.e. cell A1). Then it will add Hello! in that cell. The counter r will go up by one, then the second time it goes around the loop r will be 2, so it will get row 2, column 1 (i.e. cell A2), and so on, until it reaches row 20. Then the condition will be false, as r will be 21 and isn’t less than 21, so it will continue down the program.

Let’s run the code and see what happens. As I’ve already set up the 7 different loops, we need to select loop1 to run it.

4Loops - 18

So, from the toolbar where it says “Select function”, click on “loop1”.

4Loops - 19

Then click the play button to run the code.

4Loops - 2

The first time you run it, it will ask for authorisation, just click “Review permissions”.

4Loops - 3

Then click the blue “Allow” button.

4Loops - 20

As we can see it’s written Hello! in cells A1 to A20, just as we wanted.


Loop 2 – Print numbers 1 to 20 down column A

This time let’s use the numbers in the loop and add them to the sheet. We’re going to add the numbers 1 to 20 in column A.

4Loops - 6

Line 11 to 12: Same as loop 1, except call it loop 2.

4Loops - 7

Line 14: Same as above

Line 15: This time we want to set the value of the cells to be the current number in the loop. So, we get the range as before, and this time set the value to be the variable r. So, the first time around the loop, r will be 1, so it will put 1 in row 1. the second time it will be 2, so it will be 2, etc.

Lines 16-17: We close the loop and function as before.

Select loop 2 from the toolbar and run the program.

4Loops - 21

As we can see it added the numbers 1 to 20 to column A.


Loop 3 – Fill cells A1 to A20 in blue

It’s not just values that we can use loops with. Let’s add some colour to our sheet.

4Loops - 8

Lines 20-21: As before.

4Loops - 9

Line 23: As before.

line 24: This gets the range as before, but this time set the background colour to blue, using the setBackground() method.

As we can see it’s filled the cells in blue.

4Loops - 22


Loop 4 – Fill cells A1 to A20 in blue and print numbers 1 to 20

We can carry out more than one instruction within our loop. Let’s fill the cells with blue and add the numbers 1 to 20.

4Loops - 10

Lines 29-30: As before.

4Loops - 11

Line 32: As before.

Line 33: Similar to loop3, we set the background colour, but this time, don’t add a semi-colon at the end of the line. This allows us to add other instructions to the same range.

Line 34: Type dot and the method you want, in this case, setValue(). Here we’re going to add the variable r, i.e. the current number in the loop, to the cell.

4Loops - 23

As we can see, it added both the colour and the numbers.


Loop 5 – Print the numbers 1 to 20 across 10 different columns

So far, we’ve just added values and colours down the same column but we can of course move across the columns too. Here, we’re going to look at how two for loops can be used together, to add numbers down the columns and to add them to different column across the page.

4Loops - 12

Lines 39-40: As above.

4Loops - 13

Lines 44-46: This is pretty much the same loop from loop2, i.e. it will add numbers from 1 to 20 down a column. The only exception is that instead of keeping the column value as a constant 1, I’ve added the variable c (for column), which will change as the loop goes around.

Line 42 and 47: Wrapped around the above loop, we have another loop which will change the column number from 1 to 10, or in other words from column A to J. Here I’ve called the variable c and it starts at column 1, then increase by 1 until it reaches the tenth column (i.e. c less than 11).

Running the program, we can see that it added the numbers 1 to 20 to column A, then to column B, and so on until column J (the tenth column).

4Loops - 24

So, how does this work exactly?

4Loops - 13

At the start, c is 1 and r is 1, so it gets the cell A1, and adds the number 1 (r) in the cell. It then gets trapped in the r loop, and goes around that one a second time. So, this time c is still 1, but r is now 2, so it adds 2 (r) to cell A2. It continues around the r loop until it hits 20, then it goes down a line and hits the end curly bracket of the c loop (line 47).

It then goes back to line 42 and goes around again. This time c is now 2, and the r loop is reset back to 1. It goes around the r loop like before until it hits 20, then it goes back to the c loop, which will now be 3, and so on, until the c loop hits 10. At which point it finishes.

Loops within loops can be complicated to follow and if I’m struggling to follow what’s happening, I usually look at it step by step and see what’s happening to the variables each time they go around the loop.


Loop 6 – Create 5 documents and name them Document1 to Document5

This time let’s see what else you can do with a loop. Adding numbers and colours to a sheet is fine, but you can do so much more with them. Here, let’s create some Google Docs and number them individually from our loop.

4Loops - 14

Lines 51-52: As above.

4Loops - 15

Line 54: I want to create 5 documents, so let’s set the condition to less than 6.

Line 55: Every time it loops, we want it to create a document and name it “Document” plus the current number from the variable r. To create a new Google Doc we use the DocumentApp class and the create() method. In the brackets we state the name we want to give the document. So, it will be “Document” plus the number from r.

Lines 56-57: We close the loop and function as before.

Running the program, we will see in our My Drive folder, 5 new documents, all titled individually, 1 to 5.

4Loops - 25


Loop 7 – Create 4 documents each with names from the sheet

It’s not just numbers we can add to our documents, but we can also get text from our sheet and name the documents accordingly. Here we’re going to create documents for 4 students and append each document title with their name.

4Loops - 16

Line 61: Use the Names sheet for this one, where I’ve already added the names of the 4 students.

4Loops - 27

4Loops - 17

Line 63: Set the for loop up to count from 1 to 4.

Line 64: First, we need to get the student’s name from the sheet. We’ll get a different name every time we go around the loop. Set up a variable called studentName and then get the range which is r, 1. So, the first time around the loop it will get the name in row 1, column 1, i.e. “Joan”, the second time it will get row 2, column 1, i.e. “Paula”, and so on. then get the value in that cell, i.e. the name.

Line 65: Then using DocumentApp.create we create a new document which will be titled “Document-” plus the student’s name.

Line 66-67: As always we close the loop and function.

4Loops - 26

As we can see, in our My Drive, it’s created 4 documents individually titled for each of our students.

The important thing here is that we are getting information from a sheet, using a loop to move around the sheet to get different bits of information and then using it elsewhere. This is extremely useful and allows you to do all sorts of things.


Here’s the link to the sheet, which contains the code:

https://docs.google.com/spreadsheets/d/1Dt3n4-vvSY1OFLrHXsATfHJBVjbG7JyoG1jgOLWwBFU/copy

Here’s the complete code with all 7 loops:

4Loops - 284Loops - 29


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

Baz Roberts (Google+Flipboard / Twitter)


Forms & Sheets – Attendance System

In this post we’re going to make an attendance system using Google Forms and Sheets. The user will fill in the attendance on the form (possibly on their mobile) and the data will then be stored and organised automatically per student. This will use a Form add-on called FormRanger and will use a range of Sheet functions to do organise the data for you.

We will make an attendance sheet like this:

Attendance - 75

And an attendance summary like this:

Attendance - 76


Setting up the form

Create a new form. The form itself is very basic and just includes the date of the class and a question where we’re going to populate the class list. Make the date question a required one, just to make sure it’s always entered.

Attendance - 1

Click on the settings cog and under General, the only option I select is “Edit after submit”. This can be useful if a student arrives a bit late and I’ve already done the attendance. It will allow me to go back to the class list and update it.

Attendance - 2

Under Presentation you may want to tick the Show link to submit another response, if you do, the attendance of more than one date at a time. Change the default confirmation message.

Attendance - 3

Then we need to link the form to a Sheet. Click on Responses and the green sheet icon to create a spreadsheet.

Attendance - 4

By default, it’ll create a new one. You may want to change the title here. Click Create.

Attendance - 5

The Sheet will open and we can see the 3 columns it’s set up.

Attendance - 6

Create the following sheets:

Attendance - 24

I’ve changed the name of the ‘Form Responses 1’ sheet to G1, partly so we now it’s referring to group 1 and partly because it’s a shorter name! I’ll go through each sheet later on.

For now, just add your class list to the ‘ClassLists’ sheet like this:

Attendance - 7


Populating the class list on the form

We could just type in the student names on the form, but I often use an add-on called FormRanger, which connects a question in the form to a range on a sheet. This then updates the form automatically if there are any changes to that range. In other words, if the students change in the class, the form will be updated.

If you don’t already have it, download the add-on by clicking on the 3 dots menu and choosing add-ons.

Attendance - 56

Then type in formranger and click on the blue ‘Free’ button, to add it.

Attendance - 55

Click on the jigsaw piece icon.

Attendance - 8

Then select formRanger.

Attendance - 9

Click Start.

Attendance - 10

A menu will appear which has two main parts, the top part is where you link the question to the sheet and the bottom grey part is where you can set it up to populate the question automatically.

Attendance - 11

Click on the class list question in your form, then tick “Populate from values list”.

Attendance - 12

Then click on the grey drop-down menu and select “new values list”.

Attendance - 13

This will open a dialogue box, which contains 3 parts: Which spreadsheet you want the information from, which sheet, and then name the list.

Find the spreadsheet you want to use. If you’ve just been using it, it normally appears first. Click “Select”.

Attendance - 14

Now we need to tell it which sheet we want. Click on the “Select Sheet” drop-down menu.

Attendance - 15

Then select the sheet you want. Here, I want the sheet “ClassLists” where we added the class list earlier.

Attendance - 16

Now we need to tell it which column we want to use on that sheet. As our class list is in the first column, the column with the header “G1” is already showing. If you have a sheet with a number of classes on it, you’d have to click on the “Select column header” drop-down menu and select the specific column.

Note, as this works with columns, it’s important not to have anything else in the column, as this would appear in the form too. So, when adding class lists, just use that sheet to add columns of class lists.

Attendance - 17

On the right-hand side, we can see that it shows us the contents of that column, which we can see is the class list we want.

Attendance - 18

Finally, we need to name this value lists, with whatever name that makes sense.

Attendance - 19

So, for example, in the “Give this values list a name” box write “G1”.

Attendance - 20

Then, click the blue button.

Attendance - 21

On our form we’ll see the class list is now populated with the contents from that sheet.

Attendance - 22

If you expect possible changes to the class list or maybe you want to use the form for a different group in the future, then you can toggle the auto-populate questions options to on, so that the form checks every form submission or hour, for any changes and updates itself if there are.

Attendance - 23


Setting up the spreadsheet

Now, let’s set up the spreadsheet. We have 5 sheets. The data from the form will arrive and be stored in “G1”. Then a copy of it will be stored in “G1c”. Then this will be converted into an attendance sheet in “G1A”. The overall attendance figures per student will be displayed on the “Attendance” sheet, pulling in the data from “G1A”.

Attendance - 24

So, first let’s set up sheet “G1c” so that it automatically makes a copy of the data on sheet “G1”. In cell A1 type the following ARRAYFORMULA function. This will get all the data in column A on sheet “G1” and fill column A on sheet “G1c”. Copy and paste this into cells B and C, to do the same for those columns.

Attendance - 26

So, you should now have 3 columns like this:

Attendance - 25

Why do we need to make this copy? If we didn’t then what happens is that any row numbers we use on the next sheet will change as a form submission comes in. For example, if we want to use the data in cell A3, when a new form submission comes in, it will push that cell down, so that it becomes A4, and then it will update the reference on the other sheet to A4, which continue to be blank, as it should still be looking at cell A3, which has the data we want. The work around for this is to create a copy sheet as described above. (If anyone reading this knows a simple way to get around this I’d love to know it!)


Setting up the attendance sheet

Now let’s set up the sheet “G1A”. This is where we’ll see the attendance data more visually. We’ll see the list of students and their attendance will be shown per class date in the rows below.

Attendance - 29

At the top I’ve added the start and end dates.

Attendance - 27

As these will also appear on the first page “Attendance” then let’s add a reference to those cells. So, in cell B1 add this reference:

Attendance - 28

Cell B2 will have the same except it will refer to Attendance!B2.

Now, let’s add the list of students in row 3. On the ClassLists page the list is vertical, but here we need to show it horizontally, so we need to use the TRANSPOSE function to change it from vertical to horizontal.

In cell B3, type the TRANSPOSE function below:

Attendance - 30

The list of students will appear in each of the cells. Any changes to the ClassLists page will be replicated here automatically.

To work out the attendance, we first need to know how many classes there were in that particular period. To do this, we’ll count how many class dates are shown in column A from cell A5 downwards.

Attendance - 31

So in cell A4 we write the following COUNTA formula:

Attendance - 57

Now, we want to filter the attendance so that we only show the attendance of the classes between the start date and end date. To do this we’ll use the following QUERY function:

Attendance - 58

Phew! Long isn’t it? Don’t worry let’s go through it part by part.

First, we want to look at the data on the “G1c” sheet and we’re only interested in column B (the class dates).

Attendance - 59

Then we select just the class dates (column B).

Attendance - 60

Then we want to filter the dates by the dates that are the same or later than the start date on the Attendance sheet. Be careful with the syntax with this.

Attendance - 61

AND we want to filter the dates by the dates that are the same or earlier than the end date.

Attendance - 62

Finally, we sort the results in ascending date order.

Attendance - 63

At the moment, nothing exciting happens as we have no data, but you should get a message saying that the output is empty.

Attendance - 39

If you want to know more about how the QUERY function works, check out my QUERY post.

If the student attended, I want to add an X in the cell, if they didn’t then to leave it blank.

Attendance - 64

In cell B5, type the following formula:

attendance new1

It looks scarier than it really is. Let’s look at it in parts.

attendance new2

First, I use ARRAYFORMULA so that this formula works all the way down the column, without having to copy and paste the actual formula into the cells.

Then we have check to see if the cell B3 is blank, and if so, it leaves the cell blank. So, basically it’s checking to see if a student’s name is in cell B3, if it isn’t, it leaves it blank. More info on the IF function here.

attendance new3

Finally, if there is a student’s name and there’s a class date, it looks to see if that student’s name appears in the form responses on the “G1c” sheet. Using the VLOOKUP function, it looks for the student’s name from B3, then looks up the date from column A and looks on sheet G1c for that date and looks at the students that attended in column C on that date. If it finds the student, i.e. that they attended, it adds an X in the cell, otherwise, it leaves it blank. The IFERROR function is just there to leave it blank if it doesn’t find anything.

The final part on this sheet, is to count how many times a student has attended. We can do this by counting the number of Xs in that student’s column.

Attendance - 40

In cell B4, type the following formula:

Attendance - 41

This checks to see if B3 is blank, i.e. there’s is no student’s name. If it is, it leaves the cell B4 blank. If there is a name, it counts how many Xs there are in that column. See my post on countif if you want to know more about that function.

Copy cells B4 and B5 and paste them across to cells K4 and K5, so that there are 10 columns with the same formulas in them.

So, you should be left with a sheet like this:

Attendance - 29

I usually add a little bit of conditional formatting to show the Xs (i.e. class attended) more clearly. I just use the “Cell is not empty” option and fill the cell with a colour. See my post on conditional formatting if you don’t know how to do that.

Attendance - 71


Calculating and showing the students’ attendance

Now, the final part. I want a sheet that displays a summary of the students’ attendance for that period.

On the “Attendance” sheet, set up a table like this:

Attendance - 65

Now let’s grab the data from the different sheets. First, let’s add the class list.

Attendance - 47

In cell A4, add this formula:

Attendance - 67

This will get the class list from column A on the ClassLists sheet.

Then, let’s add the classes attended per student.

Attendance - 49

We get this from the “G1A” sheet (attendance sheet) from row 4. We use the TRANSPOSE function to convert it from horizontal information to being stored vertically. In cell B4 write the following formula:

Attendance - 50

We then want to add the total number of classes there were.

Attendance - 69

So, again we get that from the “G1A” sheet and the number was in cell A4. I’ve added an IF function, so that if there isn’t a student name in column A, it leaves the cell blank. In cell C4, write the following formula:

Attendance - 52

Finally, we work out the attendance percentage for each student.

Attendance - 68

We divide column B (attended) by column C(total). We need to add the full range (i.e. B4 to B13) as we are using the ARAYFORMULA function. I’ve also added an IF function to leave a space if there is no data in column B. In cell D4, write the following formula:

Attendance - 54

This leaves us with a summary of our class. Note, the total is 1 as even though we haven’t entered any classes yet, it’s counted the QUERY function as 1. You could change the total formula to get rid of this, but once the classes start being added this doesn’t get included.

Attendance - 66

I usually add a bit of conditional formatting to the attendance % column, to highlight those with bad attendance (in red) and those with good attendance (in green).


Putting it into practice

Let’s add some attendance and see what the final product is like.

The teacher opens the form on their mobile or on the computer and quickly adds who was there and submits the form.

Attendance - 70

Once submitted, they have the option of editing that response or submitting another.

Attendance - 72

The responses arrive in the “G1” sheet.

Attendance - 73

These are copied in the “G1c” sheet.

Attendance - 74

On the “G1A” sheet, this data is converted into the an easy to understand attendance sheet.

Attendance - 75

On the “Attendance” sheet we can clearly see the attendance of all our students.

Attendance - 76

To view the attendance for different periods all you have to do is change the dates and it will update the attendance accordingly.

You can make a copy of the sheet and linked form here.


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

Baz Roberts (Google+Flipboard / Twitter)