One of the things that I often struggle with is with the purpose my online meanderings. Although I agree about ‘collecting the dots‘, it can sometimes be hard to justify in amongst the everyday hustle and bustle. However, again and again I find myself diving into something I read long after the fact.
Today I was testing barcodes with an electronic sign-in system. I had a Crystal Report which produces student cards that included a barcode associated with the ID. However, I found that although I could get them to work with the scanner, it was very fiddly and was far from optimal.
Knowing that any adjustments to the Crystal Report would take some time as it involved a number of teams and processes, I wondered if I could produce my own cards using a spreadsheet. I noticed that the third-party application used a QR Code when enrolling a new device which has really quick, so I wondered if I could also use a QR Code for the student cards. After doing an initial test with one student ID to confirm that QR Codes would work, I searched up a post from Ben Collins regarding the generation of QR Codes using Google Sheets. After exporting a test copy of the student data and importing this into the Google Sheet in Sheet1, I created a template where a QR Code was generated for the each student ID:
=ARRAYFORMULA(IMAGE("https://chart.googleapis.com/chart?chs=250x250&cht=qr&chl="&ENCODEURL(QUERY(Sheet1!A2:A,"SELECT A WHERE A IS NOT NULL")) ))
Then in the next column I entered the student details, including first name (Column L), last name (Column K), class/form (Column I), year (Column F) and ID (Column A). I also used Char(10) to separate this information onto different lines.
=ARRAYFORMULA(QUERY(Sheet1!L2:L,"SELECT L WHERE L IS NOT NULL")&" "&QUERY(Sheet1!K2:K,"SELECT K WHERE K IS NOT NULL")&""&Char(10)&"Class "&QUERY(Sheet1!I2:I,"SELECT I WHERE I IS NOT NULL")&" / Year "&QUERY(Sheet1!F2:F,"SELECT F WHERE F IS NOT NULL")&""&Char(10)&"ID "&QUERY(Sheet1!A2:A,"SELECT A WHERE A IS NOT NULL"))
I was then left with a list of cards, the problem is that I wanted to print two columns to a page. To achieve this I created two dynamic named ranges, one for column one:
"Sheet2!A1:C"&(ROUNDUP(COUNTUNIQUE(Sheet2!B1:B)/2))
And column two:
="Sheet2!A"&(ROUNDUP(COUNTUNIQUE(Sheet2!B1:B)/2))+1&":C"&(ROUNDUP(COUNTUNIQUE(Sheet2!B1:B)))
Then in a new tab I used the INDIRECT formula to bring in the two columns.
The last step was to adjust the printer settings. This included extending the margins and changing the scale to 55%.
Knowing that this would be used by different people, I made a copy of the template and deleted Sheet1 that could be used as a master. I then put together the following instructions for how it could be used from scratch.
- 1. Create a copy of the QR Code Google Sheet (https://docs.google.com/spreadsheets/d/1lzfHDR9229g5xWTb16JPRjKDTBHlix1NmOTJE9fp-ck/copy)
- Import the student spreadsheet previously saved and Insert a New Sheet
- Click on Sheet2
- Click on Cell A1 and press Enter
- Click on Cell B1 and press Enter
- In Cell E2 add ‘=’ at the start of the formula
- In Cell F2 add ‘=’ at the start of the formula
- Click on StudentCards tab
- Click on FIle > Print
Not sure if anyone else has used Google Sheets for similar purposes. Comments appreciated.
If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.
Creating Student Cards with QR Codes using Google Sheets by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.