For several years in my physics classes at Urbandale High School in Urbandale, Iowa I have kept track and displayed the percent of students proficient on every physics competency. Proficiency has been established at 70% and the goal is that 100% of my students reach proficiency on every competency. In the past I have displayed this with different colored stickers on a bulletin board according to the following color codes:

Green 100% Proficient

Blue +90% Proficient

Orange +80% Proficient

Purple +70% Proficient

Yellow +60% Proficient

Red -60% Proficient

The colors also stand for "R You Procrastinating Or Becoming Great!"

My goal for at least the last three years has been to somehow program LED lights to light up automatically after students finish a competency to display the percent of students proficient. Here is a quick overview of the project: My students take their competencies in "Scholar", which is our school district's LMS powered by Moodle. There is a way to have those scores automatically exported into a Google Sheet as they all are finishing the test. I do not discuss that in this write-up so feel free to contact me if you need more information about that. In the Google Sheet, formulas can be easily set up to calculate percent proficiencies. Next, I made a PHP webpage which reads the Google Sheet and concatenates a string of letters which represent the various colors with which I will eventually want to light up LEDs. Finally an Arduino Sketch is created which reads the string of letters from the webpage and lights up the LEDs. This Instructable will attempt to show the basics of what I did to accomplish my goal. I hope that it will inspire others but I feel I'm writing all of this out so I can have a record of what I did. :)

Step 1: Setting Up a Google Sheet

You first can set up a Google Sheet with numbers across the top row which will represent the percent of proficient students. In my simple example here I just used 10 data points in the various color ranges.

Next under File, you "Publish to the web". Choose "Entire Document" and "Comma-separated values (.csv)" and Publish. You need to copy that .csv link because we will be inserting it into our PHP page.

When we are done with this entire project, if you go into this Google Sheet and change one of the data points then (after a few minutes) that particular LED will update it's color. Below is a link to this sample Google Sheet if you would like to make your own copy.

Sample Google Sheet

Step 2: Creating Your .php Page

Next, I created a .php webpage which I uploaded to my server which will read the Google sheet and create a string of letters representing colors in between an opening "<" and a closing ">". We will look for these symbols when the Arduino Sketch scans this webpage. Any symbols could have been used. I just chose the angle brackets. If you don't know how to program in PHP or Arduino then this project might be too advanced. However, if you have a basic understanding of coding then a brief explanation of various parts of the code might help you figure it out.

Line 2: Set up an empty string variable called $colorstring

Line 4: Set up a $url variable which contains the .csv url to your Google Sheet

Lines 8-22: This starts scanning the Google Sheet row by row and entering the data into an array which I named averages[ ]. We only have data in the first row which is row 0. Our "for" loop on line 16 loads the first 10 data points from row 0.

Lines 27-36: We iterate through each of the 10 data points in the averages[ ] array and append the correct color letter to $colorstring depending on the value of the data point. For example on line 29, if the data point is equal to 100 then the letter "g" is appended on to $colorstring.

Line 38: We print on the webpage a "<", then the $colorstring variable and finally a ">". Our Arduino Sketch will search this entire page and will be able to find the $colorstring because it will be looking for stuff that comes between the "<" and the ">"

Step 3: Arduino Sketch Part A

I will show my Arduino Sketch code in seven parts. In this part we first #include some important libraries. Next, we #define PIN 6 as the pin we will use on the Ethernet Shield to send the data into the LED strip.

We then set up our Adafruit_Neopixel strip. The "30" represents the number of LEDs on our strip.

In the next three lines we entered the byte mac[ ] address of our Arduino Ethernet Shield. You will find this on a sticker on the board. We then entered the char server[ ] address where we uploaded our PHP page. Finally, we entered a String dataLocation which is the pathway on the server to your PHP page.

Next, we set up a lot of variables to be used in our Sketch including a String Scores which will be very important.

Step 4: Arduino Sketch Part B

In this section, we start our setup( ) function and attempt to connect to the server where our PHP page is residing. We then initialize all of our LEDs to off.

Step 5: Arduino Sketch Part C

In this section we begin our loop( ) function. This loop here is reading in every single character from our .PHP page one character at a time. After it reads in the character "<" it starts adding each character to the Scores string. Once it reaches the ">" character it stops reading. Finally, the Scores string is printed to the serial monitor in Arduino so you can visible watch what is happening to make sure your program is working correctly.

Step 6: Arduino Sketch Part D

Here we start lighting up the LEDs based what is now in the Scores string. We use a for loop to iterate through the first 10 LEDs which would be LED 0 through LED 9. For each "j" value we call a function which I named checkcolor which takes two parameters. You will see this function on a later step but basically we feed the checkcolor function the pixel number (j) and also the next color letter in the Scores string. It then lights up that particular LED.

Step 7: Arduino Sketch Part E

This code resets for the next reading and starts the loop over.

Step 8: Arduino Sketch Part F

This is the connectToServer( ) function which was called earlier in the code. Again the Host is the server where your PHP page is stored.

Step 9: Arduino Sketch Part G

This is my checkcolor( ) function that takes in two parameters. An integer of the pixel number and a string of the letter color. Depending on the letter color that is fed to the function, the correct strip.setPixelColor function is called.

That's the entire code. That sketch needs to be uploaded to your Arduino board through your USB cable after we get everything wired up.

Step 10: Power Source and Parts

There are several important links that can help you with this part. The first one is to a Neopixel pack you can buy that comes with the LED strip, power supply and connectors and adapters that you will need. The second link is to an Arduino UNO board. The third link is to the newest and best Ethernet Shield. The Ethernet Shield pins all insert into the UNO board to sit on top. I used an older one that I don't think they sell anymore but it should still work the same way. The power source plugs into the wall and then into the 2.1mm jack to screw terminal block. You should screw into this block red and black wires that then go to the + and - busses on your breadboard. Also across this jack you MUST attach a capacitor to prevent the initial onrush of current from damaging the pixels. I used a 4700 microfarad 10V capacitor. I figured a lot of things out by reading the Neopixel Uberguide. I highly suggest you read it!

Step 11: Wiring Schematic

On the far right of this breadboard you see where the + and - wires came from the power supply to give power to the busses on the bread board. Make sure while you are wiring this up that there the power source is unplugged. I went to the local hardware store and bought a few small wire nuts to make the wiring less confusing.

There should be arrows on your LED strip describing the direction that the data needs to flow into it. Look at the wires that are going into the beginning of your strip. You should see a single + red wire, a single - black wire (ground) and then two wires (white and black) which end with a connector. This white wire is your data wire and the black one is another ground. The single red and single black can be plugged directly into your + and - busses on your breadboard but I needed them to be a little longer so I extended each of them with wires and the grey wire nuts.

Now for the black and white data wires: To the ground connector (where the black wire went in) I connected the yellow wire to one of the GND ports on the Ethernet Shield. Make sure this wire is always securely grounded. Blowing out my first strip of LEDs was NOT FUN!

Next, we need to hook up wires to get the data from the Ethernet Shield into the LED strip. Notice I have a red wire going from PIN 6 on the Ethernet Shield into the breadboard. I then added a resistor between that wire and the next wire. The Uberguide said that adding at least a ~470 ohm resistor between your micro-controller's data pin and the data input on the NeoPixels can help prevent spikes on the data line that can damage your first pixel. I added a 560 ohm resistor. "Go big or go home", I always say. Then my final red wire went into the connector to attach to the white data wire.

Finally, the Ethernet cable needs to be plugged in. The Ethernet Shield shown in the picture has a built in PoE (Power over Ethernet) Module built in. This means that my Arduino Boards will power up simply by plugging in the Ethernet cable. I assume the newer Ethernet Shields will have this. However, if you do not get power to your Arduino board through the Ethernet cable for whatever reason you can use your power supply to not only power your LED strip but also your micro-controller. To do this simply add a wire from the + bus on the breadboard to the VIN pin on your Ethernet Shield. Only do this if your board is not receiving power.

Step 12: 3D Printed Wire Box

So as to not leave the micro-controller and wires exposed I designed and printed on our SE uPrint 3D printer a wire box cover. I haven't decided whether to use screws for a more permanent solution or velcro strips for easy access. The Autodesk Inventor part file and the .stl file for this wire box are attached.

Step 13: Woodworking

A couple of brief points about the woodworking part of this project. I went high dollar and bought two 6' 1"x4" oak boards. I cut them each into 4' and 2' sections and made a frame with them. I also bought a 4' x 2' 1/4" oak panel for the surface. I stretched out my LED strip on the top surface of the board where it needed to be and marked dots at each LED. I then used a 1/2" drill bit to gently drill the holes. Make sure you drill on the good side because on the back side wood chips off real easily and it does not look good. I then marked horizontal lines between each hole. I bought 4' wooden dowel rods and cut them into 2' sections and used gorilla glue to attach them. I also bought some trim and gorilla glued them down each side. So it would stand more securely I screwed on two 1' boards on the bottom for feet. As you can see on the back side I lined up the LEDs and secured the whole strip with duct tape. Why duct tape? Because I refuse to finish any project without using it somewhere! Finally, I stained and varnished the entire project.

And there it is! My entire Arduino NeoPixel Google Sheet Controlled Physics Competencies Proficiency Board. I hope it gave you some ideas. School is about ready to start so it's time to see if it works!

<p>This is such a fun way for everyone to know where they are at! </p>

About This Instructable




More by marc.hermon:Arduino Physics Proficiency Display Board 
Add instructable to: