Barcode Check-in using google forms

I was enlisted to help with technical setup of a conference with approx two hundred (200) attendees over seven days. We were faced with a problem of tracking all meals taken by the conference attendees, essentially a meal check-in system, mostly for billing purposes and capacity planning. The main requirement was a simple automated or online system with ability to quickly track and report on meals taken by the attendees. So essentially, we would need to track Person/Room, meal type taken (Breakfast, lunch, break, dinner etc..), and any comments. We also needed a way to quickly identify attendees without having to ask for a name, or search through a list of names or manually punch cards or long list on a clipboard. And if that’ wasn’t enough I had a huge time constraint – one night (it had to be demoed and decided on in a meeting the following day).

I first thought of putting together a simple web page with a database back-end, that idea was quickly discredited because of the time frame given and the software development, bugs, and deployment headache. Then I thought, maybe I can do a quick online survey type form, that seemed possible and quickly doable. After some thought I began hacking together a solution. Firstly, google forms can be used to create a quick online form that can be shared among the administrators to effectively collect the required meal information. Google forms also , tracks every response along with timestamps and even gives you quick charts. So that solved the first bit of how to collect data and ascertain good inform

meal_check_in_form
Meal Check-In form

ation. So I went ahead and created the Meal Check-In form. A very simple form with Name, Meal time (type of meal) and Comments.

Now I had one last problem to solve, how to quickly identify attendees (by name or room number), I thought of QR or bar codes, but how do you
1. create bar codes based on text and
2. scan them into the online form?
I went searching. I found an android app called Barcodescanner Keyboard. It costs $3.99 but there’s a demo version that works equally well for a trail period of 30 days. This app is a keyboard with bar code scanning features, so once you engage that feature and scan a code the app simulates typing on the keyboard i.e. it adds the text to the textbox (pretty nifty). This was perfect as it would allow the administrators the ability to scan a bar code with the attendees information directly into the form.

Now came the final piece of the puzzle, how to generate codes for each attendee. After some more research I found a little trick on google docs. It’s not specific to google docs but google sheets allowed me to use a formula along with a link to generate the bar code. There is a website that generates barcodes from text (https://www.barcodesinc.com/). I used a link to that website and a formula in google sheets. So in column one of the sheet I added the names of the attendees and in column 2 I added the formula

=image("http://www.barcodesinc.com/generator/image.php?code=" & substitute(A1," ","%20") & "&style=197&type=C128B&width=200&height=100&xres=1&font=3", 3)

All the above does is use the “image” function to generate an image from the returned 64bit encoded string from the link. The substitute was used to replace spaces with the proper URL encoding of %20 from the cell that contained the Name. the only other option I changed was the width and height of the resulting image which you can do by changing

&width=200&height=100
barcode_sheet
Barcode sheet

That left me with a barcode sheet. After filling in the entire sheet with all the attendees (copy and paste from another sheet). I was able to produce all the barcodes needed. I then passed this on to the administrators who happily printed the sheet, laminated, cut them into separate tags and added key rings to them for easy handling by the attendees. Meanwhile I focused on training other administrators on the check-in process

Meal Check-In Report
Meal Check-In Report

The final product looked awesome and worked great. We encountered one issue, because the barcodes were being scanned from a phone, the ability for the phone to pickup the barcode for the dinner at night was a bit difficult. This was solved by either standing under a good light source or turning on the “Flashlight” feature of the phone before check-in the person.

Finally for reporting, I added a second sheet to the responses sheet and added a pivot table that counted meals by day and meal type.

This was an interesting and exciting little project as I gave me autonomy to seek out a solution using freely available tools and it resulted in hassle free mean check-ins

Final Product
Final Product

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.