Introduction: Daily Poll With Makey Makey and Google Sheets
I wanted to create a way to record student data as they enter the classroom as well as have a way of easily showing the results in the room on the projector screen. While I could simplify this by using Scratch, I wanted an easy way to record and save the data input each day. Using Google Sheets satisfied that need a little easier.
Slap Switch idea taken from this instructable: https://www.instructables.com/id/Slap-Switch-Simple-No-solder-Touch-Switch-for-Make/
- cardboard scraps
- 6 washers
- 6 metal screws
- 6 alligator clips (or wire)
- masking and/or duct tape
- scissors / box cutter
- hot glue gun
- Makey Makey
- Additional laptop/desktop (optional if wireless projector)
- Projector and projector screen
Step 1: Create the Slap Switches
Using the materials I had handy, I followed the steps created in this instructable: https://www.instructables.com/id/Slap-Switch-Simple-No-solder-Touch-Switch-for-Make/ as a guide to create three slap switches.
I added enough layers of cardboard so that the screws were covered, so the number of layers will depend on the length of the screws.
The size of the switches was about 3in by 5in. The circle switch is about 4 inches diameter. Make these switches the size that best works for you and your classroom.
Three switches are needed because two are the yes/no switches and the third will connect to the enter button on the keyboard in order to advance to the next cell in Google Sheets.
If you wanted to create this with Scratch instead you could accomplish this with just two switches.
Step 2: Remap the Makey Makey
I knew I would be creating Yes/No buttons for this project, and needed to modify the Makey Makey so that it reflected a letter input instead of an up and down button. The ability to do this is available on the Makey Makey website here: https://makeymakey.com/pages/remap
Following the instructions on the website, I changed the Makey Makey's up button to a "y", the down button to a "n" and the space button to an "Enter". These would now align with the slap switches I created.
Connect the Makey Makey to the slap switches and to the Chromebook/laptop available.
Step 3: Creating the Google Sheet
I wanted the Google Sheet to display the data as clear as possible, which led me to generate two columns of formulas.
As students would come in, I would prepare this sheet by first clicking in the first cell. If you want to add a title on the sheet, have student input start in the second cell. Column A will be the student-generated data from the slap switches.
To take into account the possibility a student might have their hand on a yes/no switch too long, press it more than once, or forget to submit, I wanted to add in a formula that will eliminate more than 1 character in each cell. I used =left(A2,1) This formula states that in the column to the left (Column A), in cell A2 I want to display 1 character. What ends up being then displayed in this column is the first character in cell A2. By dragging down this column a number of rows (dictated by the number of students in class), the formula will be copied to the rows below it.
I could leave it as is, with the chart displayed with a "y" and "n" to represent the two answers. To make the data a little cleaner, I wanted to turn the "y" and "n" inputs into "yes" and "no". This formula is also great if you wanted to change from a yes/no response to true/false, etc. I used this formula: =IF(B2="y","Yes",(IF(B2="n","No",IF(B2="", "")))) It is a nested expression, but essentially similar to an if/then loop in Scratch. It states that if the character in cell B2 is a "y" then I want to display a "Yes" in C2. Additionally, if B2 contains a "n", I want to display "No". I also took into account that a student may press the submit button too many times, which would display a blank, but also create a false reading on my chart and add a third pie slice or column if I did not add in a statement that also kept that blank read as a blank.
Step 4: Publishing the Pie Chart
The nice thing about Google Sheets is the versatility it has in displaying live data. I can have a chromebook attached to my slap switches outside my classroom, as well as display the same sheet on my computer in my room, and project that to my projector screen.
I first generated a Chart from my Column C data, which displays the full words of "Yes" and "No".
To further clean up the visual for students, I can publish the chart itself in the Google Sheet to display it on its own. Click on the 3 dots at the top right of the chart, choose Publish Data. By default the display will be interactive, which you want to leave to allow the chart to transform live for students to view. After clicking publish the live website location for the chart will be displayed, and you can open the live chart in its own window.
Step 5: Save the Data and Reuse
Because we are working with Google Sheets, the data will save automatically. To create a new question each day, you can add more sheets to the document or create a new sheet each day. Copy the formulas for the next day and reuse as needed. All that needs to happen each time is a new question be created and posted in Google Sheets as well as outside on the wall.
This project could also easily be turned into an exit ticket process, since it is still a nice way to gather data at the end of the class in a concise Google Sheet.