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:

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.

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.


Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon. Just click on a book! (Affiliate links).

JavaScript Fundamentals for Apps Script users

a

Google Apps Script Projects 1
Google Apps Script Projects 2

30 comments

  1. I wish I could actually read this, but the NBA ad at the bottom keeps pulling my browser focus to it and so I can’t read this great information without a lot of hassle!

    1. Hi Lori-The ads are something WordPress adds automatically and will depend on who’s viewing the page. Maybe if you try later the ad will change. Anything thing to do is use a different browser.

  2. nice tutorial and sheets.
    i have a slight problem with the feature of changing the dates though as it affects the date shown only and not the actual recorded attendance x’s – for example, if you set the start date in attendance “B1” to “02/03/17”, it shows the dates in G1A as 02/03/07 through to 04/03/07 as you would expect, but it shows the attendance in G1A for 01/03/07 through to 03/03/07.

    is there a way to correct this?

    1. Hi James-I’ve just opened a copy and tried it out and don’t see the problem you’re having. First thing will be to refresh the page, as sometimes previous dates still show, even though the sheet has actually been updated. The other thing is that the dates are in DD/MM/YY format.

  3. Hi Baz,
    thanks for taking the time to reply.
    I have tried refreshing but its still happening…
    leaving the dates in “attendance” as they are on the copied sheet from your link above, the data in the row for date:
    01/03/17 is : X X X X X
    02/03/17 is : X _ _ X X
    03/03/17 is : X X _ _ X
    04/03/17 is : X _ X X X

    but when i change the start date in “attendance” to 03/03/17 (so you would only see results from 03/03/17 and 04/03/17, instead of seeing the result:
    03/03/17 is : X X _ _ X
    04/03/17 is : X _ X X X

    it actually shows the correct dates but the data from 02 and 03 march.
    03/03/17 is : X X X X X
    04/03/17 is : X _ _ X X

    here is a link to the sheet – the only thing ive done is set the start date to 03/03/17:
    https://docs.google.com/spreadsheets/d/180lO4EPqCZE0D0Brt8uNyDXkx6BelBGO_DQvGdKWKPc/edit?usp=sharing

    1. There’s a problem with the arrayformula in G1A where the Xs are. The first line always shows all the students. Tonight I’ll have a look at it. Thanks for pointing it out!

    2. I’ve found the problem, the formula in the row 7 (B7 onwards) should be: =arrayformula(if(B$3=””;””;iferror(if(find(B$3;VLOOKUP($A5:$A;G1c!$B$2:$C;2)); “x”;””)))) The sheet in the blog post was using an old formula that had an extra sheet and therefore the way it looked up the dates was different. I’ve updated the file and I’ve added a temporary note in the post. Try it out and let me know how it goes, hopefully it’s ok now. Thank you v much for pointing it out, not only for the post but I realised that some of the files at work weren’t correct either! Lifesaver! 🙂

    1. The form you set up the same way. On the sheet G1A, change the last part of the transpose function to e.g. A2:A20. This will fill the columns with all the names. On the Attendance page, the arrayformula range would need to be extended too, e.g. $A$2:$A$20

  4. Hi, Baz. I’m excited to use this script for my Staff Attendance. Where should I add a new staff name…G1A or the Google Form?

  5. Hi Baz, I appreciate you sharing the files and directions on how to do this. I am trying to add extra groups to this and it doesn’t seem to work. (ex: in the ClassLists spreadsheet – G1 | G2 | G3 | G4)

    I duplicated the G1A sheet and switched the =transpose(ClassLists!A2:A11) to the proper columns. The names change but the “x’s” disappear and even when I change the other formulas to point to the correct places in the G1c sheet I can’t get anything to work properly. What am I missing?

    (btw I made a new form that loads the classlists and I linked it to the spreadsheets. The original sheets seem to work fine, but anything extra does not.)

    Any assistance would be nice. I can send a link or screenshots if need be.

    Cheers,
    Don

    1. Hmm, sounds strange but sounds like a referencing error. Send me the link and I’ll take a look at it. Note, I’m just about to start our academic year, so it may take a few days.

      1. Hi Baz

        Thanks for the reply. I figured it out and added a few extra fields to keep track of payments, classes taken, classes left, and their ID number.

        Thanks for the information and the inspiration. Good luck with your school year!
        Don

  6. Hi,
    I am getting a formula parse error in this section:
    =arrayformula(if(B$3=””;””;iferror(if(find(B$3;VLOOKUP($A5:$A;PG1c!$B$2:$C;2)); “x”;””))))

    I’m struggling to see where the syntax error is?
    Thanks
    Grace

    1. It’s probably the speech marks. Copying it from WordPress copies them in a different format. For each of them, just select it and type one in directly, you should then see it change to green, showing it’s ok.

  7. Hi!

    I recently did your tutorial so that I can track attendance better in my organization. I usually have easy person fill out their own form using QR codes. I’ve tried that and realized that it will only track the attendance for one form submittal.

    I’m trying to find a way where I can have each member fill out & submit the form and it track their attendance, rather than me having to track each person down and submit as one. Let me know if you can help!

    Thanks!

    1. Hi – It’s possible to add multiple forms to a sheet and then you share each form with the individual people. You then connect a sheet to each form sheet. In the example in the post it’s only for 1 form, but it can be repeated for multiple classes.

  8. How would I record attendance for multiple sessions for the same class roster? I’m having trouble when I record multiple attendances on the same day, G1A only shows the correct check marks for the last recorded attendance.

    1. Hi-Maybe you could change the date question into a question where you have to enter the date and time so it would record both in the same record

  9. Hi Bazroberts,

    When i record attendance for multiple sessions in the same day the G1A doubles the last entrie (if was two sessions) and doesn’t count the firts one. The G1 have the registration right whit the day and the hour of the class.

    Tkanks a Lot for the help.
    Rui

        1. Hi Rui – I think you will need an extra field in your form to record, for example, session 1 or session 2.
          Then on the G1c sheet, I would combine the date and session number in one column (e.g. 8/11/20 – 1). You can do that by using the & operator: something like A1&”-“&B1. There’s a post on this in the Sheet Functions section.
          Then on the G1A sheet, you would need to update the QUERY function so that it is getting this column.
          Hopefully that will show that sessions 1 and 2 are different and will get the attendance for each one.

Comments are closed.