Library Digital Sign In
Keeping track of who visits our libraries and why they visit provides us with valuable feedback on what our communities need. Below is a step-by-step guide for creating a digital sign in procedure for your library. Feel free to follow as much of this guide as works best for you.
1. Dedicated Library Email
I found it useful to have a dedicated library email. I emailed Mike Johnson and asked for one, and he created ecvhslibrary@guhsd.net for me.
The reason this is useful is that it enables me to log into a computer (even a Chromebook) under that account, and I can set the home page of that account as the library's sign in Google Form.
I can also bookmark the form so it can easily be found again as the ONLY bookmark on the bookmark bar. See the upper left corner of the image.
2. Sign In Stations
I used a few old desktops to serve as sign in stations. I did some technology magic and installed the Chrome Operating System on them, so they're basically Chromebooks. However, that is not a requirement for your sign in stations. Old Chromebooks would work great here.
As stated above, I have the Google Form set as the home page for the ecvhslibrary@guhsd.net account.
I wanted signing in to be simple for students, so instead of having keyboards, I only have number pads hooked up to my sign in stations.
All I want students to do when they sign in is punch in their ID number and check the box (see below).
Note: If you do this, you should change the password for your library Google Account that Mike creates for you to all numbers.
3. Google Form Sign In
When creating the Google Form that I want students to use to sign in with, I wanted to keep it simple.
Students just enter their ID number, and check the box(es) for why they are visiting.
If they aren't sure why they came in (happens to me, too), I tell them to just check General Library Use.
When students fill out that form, it populates a spreadsheet. Specifically, it populates a sheet on that spreadsheet called "Form Responses 1". That little detail is important for formulas later on.
When you first create your own form, you won't see the other sheets that are in this image. I have added the "Query Sheet" sheet, "Student Info" sheet, and the "ECVHS Library Sign in Awesome Table Display" sheet.
Important: in the share settings, share this spreadsheet with your entire staff as View Only. This way, only your staff members can see the ID numbers.
Option: You could stop after step 3 if you want. You would be collecting student ID numbers and why they are visiting the library. This was all my sign in system was for the first semester of this year! As I learned more, I added more. See the steps below if you want to take it to the next level.
4. Adding a Query Sheet
I got tired of scrolling all the way down to the bottom of my sign ins to see the most recent visitors. So, I created a Query Sheet that automatically brought in all the responses from the "Form Responses 1" sheet and sorted them by the time the student signed in. This made it so the most recent sign ins appeared at the top of the "Query Sheet" sheet.
- Click on the plus symbol.
- Name your new sheet "Query Sheet."
- Click in the A1 box.
- Paste the query formula:
=query('Form Responses 1'!A1:F, "select * order by A desc", 1)
Again: Share this spreadsheet with your teachers so that they can see that the student they sent to the library actually made it to the library. Important: share this spreadsheet with your entire staff as View Only. This way, only your staff members can see the ID numbers.
Option: You could stop after step 4 if you want. Now you can easily see your most recent sign ins! See the steps below if you want to take it to the next-next level.
5. Adding Student Info
You're collecting ID numbers and sorting them by timestamp, but who is who? This step is the first part of developing a nice display sheet that shows you student names, while only ever needing students to sign in with their ID numbers.
- Click on the plus symbol to add a new sheet.
- Name that sheet "Student Info," or some similar name.
- Paste in your student information. You need to update this sheet as you get new students. We'll probably update this sheet once a month. For the ECVHS library, I have column A=ID number, B=Last Name, C=First Name, D=grade level.
6. Creating Your Display Sheet
- Click on the plus symbol.
- Name the new sheet "Display Sheet" or something like that. As you can see, I've called mine "ECVHS Library Sign In Awesome Table Display".
- Click on the "View" settings, then scroll over "Freeze," and choose "Freeze 2 Rows." This is an important step for later on.
- Click in the cell right under the 2 frozen rows (so, in cell A3).
- Add the formula:
=query('Form Responses 1'!A1:C, "select * order by A desc", 1) - See what I have in the top 2 rows of each column? Add that to your own sheet:
- A1: Time Checked In
- A2: NoFilter
- B1: ID Number
- B2: String Filter
- C1: Purpose for Visit
- C2: NoFilter-Hidden
- D1: Last Name
- D2: StringFilter
- E1: First Name
- E2: StringFilter
7. Click in cell D4.
8. Add the formula: =vlookup(B4,'Student Info'!A1:D2501,2,false)
9. Drag that formula down this column as far as you would like it to go. Click on the little blue box in the bottom right corner of the cell and drag it down to copy the formula down. For the ECVHS library, I have this formula for ~300 rows. So that means I can easily see the names of the last 300 students who signed in. I don't have a need to see more than 300, as I mostly want this information for students who are currently in the library. If I really wanted to see who was signed in a 2pm 4 months ago, I could look them up by ID number.
Note: the reason I only have this formula for 300 rows is that the longer the formula goes, the slower the spreadsheet is. I don't want to bog it down by giving it a task that I don't really need it to do.
10. Click in cell E4.
11. Add the formula: =vlookup(B4,'Student Info'!A1:D2501,3,false)
12. Drag the formula down--like you did in step 9.
7. Using Awesome Table To Display Your Data
As the name suggests, Awesome Table is awesome. Basically, it displays the information from your spreadsheet in a useful way. See my image to the right. I have my Display Sheet (created in section 6 of these instructions) feeding an Awesome Table view. Then, I have that Awesome Table view embedded on my library's website so that library staff and teachers can see who has recently signed in.
Notice that it's searchable by ID number, last name, or first name.
Note: as stated waaay back in step 3, your spreadsheet must be shared with your staff as View Only. Without that--staff members won't be able to see the Awesome Table View (or any other view of the spreadsheet).
1. Go to Awesome-Table.com and log in with your Google Account. If you created the Google Form and Spreadsheet with your library Google Account, log in with that account. If you used your regular work Google Account, log in with that one.
It will ask for permissions for Awesome-Table to be able to access your Google Drive and a couple other things. You will need to allow those permissions for Awesome-Table to work.
2. Click on Create New View.
3. Select the Spreadsheet (the one your Google Form is feeding).
4. Here are my settings on DATA SOURCE and VIEW CONFIGURATION. I have left the other settings alone. If you want a different number of students to display at once, you can change that on the VIEW CONFIGURATION. See that I have set it at 30.
Notice on "Layout" where it has the word "Filters"? This is why you added the NoFilter & StringFilter tags way back on step 6 of section 6. Those filters tell Awesome Table how to display your data.
5. Click on the eyeball (View icon) to see the Awesome Table view. That's the link you can send staff members if they want to bookmark it to see who has/hasn't visited the library recently. Again, since you only shared the original spreadsheet with staff members, only they can view it.
You can also click on the Share icon to get the view link, or to get the code to embed this table on your website (like I did--see the image at the beginning of section 7.
