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 also use a Form add-on called FormRanger to easily populate a Google Form with your class list and will use a range of Sheet functions to organise the data for you.

We will make an attendance sheet like this:

And an attendance summary like this:

---

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.

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.

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.

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

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

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

Create the following sheets:

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:

---

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.

Then type in “form ranger” and click on the add-on and follow the instructions to add it.

Click on the jigsaw piece icon. Then select Form Ranger.

Click Start.

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.

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

Then click on the plus button to add a set of values.

This will open a dialogue box, which contains 3 parts: Which spreadsheet you want the information from, which sheet, the range, 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”.

Now we need to tell it which sheet we want. Click on the “Select Range” drop-down menu and the sheet you want. Here it’s ClassLists.

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

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.

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

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

Then, click the blue button.

We can see that Formranger is going to populate the question called Class list with the range in column G1.

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

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.

---

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

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.

=ARRAYFORMULA(‘G1’!A:A)

So, you should now have 3 columns like this:

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.

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

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!B1

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:

=TRANSPOSE(ClassLists!A2:A11)

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.

So in cell A4 we write the following COUNTA formula:

=COUNTA(A5:A)

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 in cell A5:

=QUERY(G1c!B2:B,”select B WHERE B >= date “””&text(Attendance!B1,”yyyy-MM-dd”)&””” and B <= date “””&text(Attendance!B2,”yyyy-MM-dd”)&””” order by B asc”)

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

=QUERY(G1c!B2:B,

Then we select just the class dates (column B). The initial quote marks open the search query.

“select B

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.

WHERE B >= date “””&text(Attendance!B1,”yyyy-MM-dd”)&”””

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

and B <= date “””&text(Attendance!B2,”yyyy-MM-dd”)&”””

Finally, we sort the results in ascending date order.

order by B asc”)

At the moment, nothing exciting happens as we have no data, but you should get a #N/A error and you hover over the cell you’ll see a message saying that the output is empty, which is fine as we have no data yet.

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

In cell B5, type the following formula:

=ARRAYFORMULA(IF(B$3=””,””,IFERROR(IF(FIND(B$3,VLOOKUP($A5:$A,G1c!$B$2:$C,2)), “x”,””))))

As is often the case with long formulas, they looks scarier than they really are. Let’s look at it in parts.

=ARRAYFORMULA(IF(B$3=””,””,

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.

IFERROR(IF(FIND(B$3,VLOOKUP($A5:$A,G1c!$B$2:$C,2)), “x”,””))))

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, otherwise it would show an error.

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.

In cell B4, type the following formula:

=IF(B$3=””, “”, COUNTIF(B$5:B, “x”))

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:

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.

---

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:

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

In cell A4, add this formula:

=ARRAYFORMULA(ClassLists!$A$2:$A$13)

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

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

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:

=TRANSPOSE(G1A!$B$4:$K$4)

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

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:

=ARRAYFORMULA(IF(A4:A13=””, “”, G1A!$A4))

Note, without any data it shows “1” in the total column as it’s adding the cell with the QUERY function in it. I didn’t feel this was a problem because as soon as the attendance starts to be inputted it will show the correct figure.

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

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 ARRAYFORMULA 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:

=ARRAYFORMULA(IF(B4:B13=””, “”, B4:B13/C4:C13))

This leaves us with a summary of our class.

I usually add a bit of conditional formatting to the attendance % column, to highlight those with bad attendance in red, those with attendance that could be better in yellow 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.

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

The responses arrive in the “G1” sheet.

A copy of these are reflected on the “G1c” sheet.

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

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

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.

Post updated (2021), showing latest version of Formranger add-on, and screenshots from latest versions of Google Forms and Google Sheets.

---