You Can Count on Me

I don’t remember where I first read about these programmable buttons from Amazon. Sure, many of us have seen the “Dash” versions you can get which allow you to place an order at the click of a button, but there is potential for more. And while I’ll let your own minds wander to all of the possibilities, I just want to talk about one: using these as data collection devices.

You can configure each button to communicate over wifi. It can receive and transmit data related to a single, double, or long click. Slip one in your pocket. What are the kinds of things you might like to track? How many minutes in a class period the teacher is talking…or how many boys, girls, or non-binary students s/he calls on? What if you gave one to a student and asked him/her to push a button every time an adult in the building greeted him or her by name? We could even go bigger. What if you put a set in the office with a different question each week and asked visitors to respond?

I had these and other questions in mind. So, last summer, we ordered one of these Amazon Internet of Things (IoT) Buttons for our office…and since then, it’s been sitting in its little box, staring at me, waiting for its opportunity to be useful. When I found myself with a gift of time this week, I decided it was finally the moment to program the little beast. Now it’s time to share my learning with you, in case you want to jump in on this, too.

My only disclaimer here is that what I know about coding would fit in a thimble. There will be, no doubt, more elegant solutions to what I implemented—and if you know them, I hope that you’ll share. Here are the resources on which I relied most heavily:

• Caroline Dunn has a great video on YouTube to help you set up your button and get you started. The code and IFTTT recipes have some useful background, but they did not work for me “as is.”
• This code from Joseph Guerra, however, works like a dream. The only limitation is that it shows you how to capture information from a single click—not the double or long-click (if you want to use all three options with one button).
• To overcome this last issue, I was able to use some code from here. I still had to figure out where to place it…but I did. And you can, too.

You will need

• An Amazon IoT button ($14.95)
• An Amazon AWS account (Free!)
• AWS BTN Dev and AWS BTN Wifi apps (Free!)
• An IFTTT account (Free!)
• A Google account (Free!)

I won’t get into the nitty-gritty here with the button set up—watch Caroline’s video or hit teh Googles for some additional support. Basically, you’ll install the apps on your mobile device and use those to configure the button to a designated wifi source and select its first task. 

Next, set up your IFTTT (If This, Then That) recipes. You'll need three: one for a single click, one for a double click, and one for a long click. Use the Webhooks app as the trigger (If then...) and the "add a row" option from Google Sheets (...then that) to capture the type of click. Even though you set this stuff up first, it's really the second part of what happens when the button gets pushed. But you need some information from this to add to the first part of process.

So let's take care of that. Log into your Amazon AWS account and find the Lambda app. This is where your code for the button lives. There's some placeholder stuff there from when you first configured the button using the app on your smartphone. Delete that and replace it with the stuff from Joseph Guerra. You'll be all set for one type of click (single, double, or long). If you want to have the button collect two or all three types of clicks, modify the code so it looks like the sample below. Note that you're just copying the stuff for your first click type (e.g., Single) and pasting it in two more times for Double and Long. Replace the words "Single" with the other clicks.

That's it. You can do it. I did---and I didn't even have this to get started. Keep in mind that you can modify your code here to associate different names with your IFTTT recipes (applets). If you don't want single, double, long showing in your spreadsheet, you can have yes, no, maybe or thumbs up and thumbs down. Or start and stop. The possibilities might not be endless, but the variations are completely up to you.

What I like about the button is not just its options for simplified, automated, and "mobile" data collection, but also the anonymous nature of it. The Google Sheet only captures a timestamp and click type (or whatever word you tell it). When I handed the button off to a principal to use in his building, I told him that whatever shows up in the spreadsheet is all his---I don't need to know who he gave the button to or what the clicks represent or what questions he had in mind. As long as he can extract what meaning he needs, then that's all that matters. And even if the spreadsheet was public, the data won't mean anything to anyone who doesn't know what the various clicks represent.

Now that I survived my first foray into this world, I'm ready to go further. We ordered 19 more of the buttons and I will get started this week getting those programmed and ready to check out to principals. I'm excited to see all the different ways that they use these to gather information in their buildings.

What will you do with yours?

Let Them Eat Cake

When you think of an analogy for how well two things go together, do you choose "peas and carrots"? I often think of the old-school Reese's commercials where someone's chocolate was always ending up in someone else's peanut butter. But I doubt that anyone ever tells you that "X and Y go together like Excel and GoogleDocs."

It's an uneasy partnership, to be sure, but there is no reason to think that these two tools can't be used together. I was thinking about this recently while looking at some templates for school administrators. Some use Google Forms to capture information (for example, from classroom observations). The templates developed from these have all sorts of analysis built from the data collected in the forms. There are definite advantages with using the Google Forms to collect data. You only need wifi and an Internet connection---no special apps, no worries that Excel doesn't run on a tablet, and it can be platform agnostic. The disadvantage is simply that Google Spreadsheets can be a bit fussy to work with...and, the results are often crude and visually unappealing.

But why can't you have your cake and eat it, too (and isn't eating cake better than peas and carrots)? Is it so much to ask that you be able to marry the convenience of a Google Form with the genius of Excel?

I know some of you are saying, "Duh...you just download the spreadsheet from Google and open it in Excel." And you're right---you can certainly do that. And every time something is updated in Google, you have to download a new version of the spreadsheet. But perhaps you didn't know that you can connect Excel with the Google data source...and then just refresh your Excel workbook?

Bake the Cake
For this example, I started by creating a Google Form. The purpose of the form is to capture observations of students. This is just a "proof of concept" deal, so I only included three classes. You could certainly add lots more classes, or if you're an elementary teacher, you might skip this piece and just kick things off with your class list.

This Google Form has five pages. The first is what you see on the right, and depending upon which radio button is checked, the user is sent to one of three pages (Biology, Chemistry, Physics). The final page contains the "submit" button. Makes things a little clunky---I'd rather be able to submit data from each class page, but sometimes you have to take what Teh Googles gives you.

Each class page has a dropdown menu with the class list and another menu that allows the teacher to choose a standard. Finally, there is a space to include a description for the observation.

Lots of possibilities here for teachers. You could add choices for student behaviors...parent contacts...classroom management ephemera (hall passes, pull-outs, etc.). Make a form that suits whatever your needs may be---and don't forget that there are already lots of templates out there for both students and teachers.

Okay, once you've set up your form, you can test it out and have a look at your Google Spreadsheet. I actually did three tests---one for each class period. You can see how the data are organized:

Your final tasks are to Share your document and Publish it. If you don't want to change the settings for this spreadsheet such that anyone with the link can view it (even though that "anyone" will only be you), you will just need to remember to be logged into your Google account each time you want to sync with Excel. Secondly, while choosing to "Publish to the Web" (under the File menu) is not required to sync with Excel, doing so will give you a much cleaner result. After you have published the document, copy the link that appears for sharing the document. You'll need this for the next part of the set-up.

Make the Icing
Now it's time to set up things with Excel. Open Excel and click on over to the Data tab. In the Get External Data section, choose "From Web."

We're going to use this feature to link Excel with our Google Spreadsheet, but keep in mind that you can use this trick to pull data from most websites. Need stock info? Weather data? Sports scores? Excel will be happy to collect that for you.

After you click on the "From Web" option, a pop-up browser window will appear. Paste the link for your GoogleDoc to bring up that spreadsheet. Anything on the webpage that is fair game for pulling into Excel will have a yellow arrow beside it. Click on the arrow beside your data (the arrow will turn green). Then click "Import." Excel will ask you if you want to put the data in cell A1 of the workbook. You can click "Yes" or choose another spot.

Now, when the data imports, you will probably get some extraneous bits. Notice in the sample below that cell A1 has the name/sheet from Google, there are some extra rows (2, 4, and 5), and a footer in row 9. But still, we're pretty clean. Just save your workbook and you're good to go.

Serve It Up

This is where the magic really happens. Keep collecting data using your Google Form. Whenever you want to sync the information with your Excel version, open your Excel spreadsheet. Go to the Data tab and click the "Refresh All" button. Abracadabra! Excel will grab all the new data for you.

You can also check out the "Connections" and "Properties" options if you want Excel to check for new data on a particular schedule or you have other adjustments to make.

Now that your data is nested in the comforting arms of Excel, you can build additional reporting tools or data analysis options within your workbook. Just set up whatever you need on other sheets and let those update every time you refresh, too.

But you don't have to take my word for it, you can try all this for yourself.

• Here is the link for the Google Form. Add some data.
• Then, check out the Google Spreadsheet. You can view all of the data, including what you've added. If you like, you can save a copy to your own account to play with.
• Finally, download the Excel workbook and refresh the connection.

I'd love to know what possibilities you see for your classroom or school. Bon appetit!

The Shaggy Dog

Stop me if you've heard this one: Excel, Web Apps, and GoogleDocs walk into a bar...

Okay, maybe that isn't the basis for the story I'm about to tell, but I was nearly driven to drink by these three, um, tools.

As part of my day job, I work with a small database of resources. Until recently, the database has lived on a Moodle site---a long and painful sidebar to this story, which I will spare you. As our story really begins, this database is downloaded into a csv file and begins its new life as a spreadsheet. The goal is to webify this content and make it interactive---all without the expertise (and expense) of a developer. We do have the ability at our workplace to have a table on the web with alphanumeric sorting---but no filtering. This is great for things that you only need to sort on one attribute (and for a small dataset). My pseudo-database didn't fit these criteria.

Part One: The Easy Way Out...Or Not
I tried using the Excel Web App first. I really wanted this to work for two reasons. First, it would be awesome to have the first idea work and be able to end the story at this point. The other reason was simply an IT one: in my workplace, it will be a lot simpler to argue to embed a Microsoft product vs. a third-party app. So, I converted my spreadsheet to a table, uploaded it to SkyDrive, and if you know what you're doing, you can filter the results using the app. But this just wasn't going to work. By the time you built in enough directions to navigate the filters, a novice user would probably just give up on the damned thing. Plus, you could only filter one column, so I was no better off than just the simple sorting we already had.

Part Two: Back to the Drawing Board
I went back to Excel to see if I could get a handle on the some of the mechanics (formulas) I would need to build in the filtering. It's simple enough to build some data validation (dropdown) menus that allow a user to choose. But I had to learn how to generate a dynamic list from these choices. It's one thing to use INDEX and MATCH to find one item on a spreadsheet...but how do you use it to find all of the items for a set of criteria? A list that will change with every possible combination of input from the user? Yikes. I did find the answer to this and built a little interactive form. It worked great offline, but I couldn't move it onto the web, because the Excel web app doesn't support data validation. But hey, Google does!

Part Three: A Move to the Dark Side
Let's give Google Spreadsheets a crack at things, eh? I uploaded the workbook..even figured out how to make the array formulas work. The dropdown menus were functional. But then, Defeat was snatched from the jaws of Victory. You see, the dropdown feature only works in the spreadsheet---it doesn't work if you embed it in another webpage. Argh. So, I decided to build a Google Form for the input and connect that with the spreadsheet to generate the dynamic lists of resources. Um, no. Again, it worked great in the live worksheet, but didn't make a bit of difference for the embedded one. Le sigh.

Part Four: Gee, It's Good to Be Back Home Again
I'm starting to get pissed. Twice now, I've gotten down to the very last piece of the puzzle, only to find that it doesn't exist. But I'm also a bit more focused on what needs to happen. Whatever gets developed needs to (a) live on SkyDrive, (b) be intuitive for the user (c) not need data validation or VBA. So, I'm back to square one with Excel, begging my old lover to take me back and teach me some new tricks. I ended up building some IF statements based on whether the user placed an "X" beside a choice and used that for the input. The only final piece was to use the HYPERLINK formula to piece together the title and location of a resource from the spreadsheet with the data into the list for the user, so they could just click-n-go.

Part Five: It's Alive!
After some final formatting, I uploaded my Frankenspreadsheet to SkyDrive, and it worked. I suppressed my urge to run around the office like Colin Clive in Frankenstein and scream "It's Alive! It's Ah-liiii-ive!" I admit that it's a little clunky, but it meets the criteria it has to satisfy.

I've mocked up a sample using different a different dataset for you to see (without hyperlinks, since these refer to documents instead of webpages). If you're viewing this post via RSS, you'll have to click through to the original post to see the embedded spreadsheet.I'll give you the background on building this in the next post. For now, any suggestions for improvement?

To play with the spreadsheet, place an "X" beside one item in the list of Units and an "X" beside one item underneath Category.

Google Motion Graphs

Many of you may be familiar with the work of Hans Rosling---perhaps one of his TED talks or perhaps the Gapminder tool, a product of the foundation he helps to lead.

If you haven't seen the tool, it is a graph which allows you to plot five variables (one of which is "time") and then set the graph in motion. It can give you a very powerful look at the interaction between different factors. Gapminder does provide a desktop format you can use to view some of their selected visualizations.

But what if you want to build your own, using your own data? You're in luck. You can create your own motion graphs using Google Docs. Let's look at an example using state level science test results for the past 8 years.

You will need to organize your data in a specific way. First of all, you must include some sort of time period. This means that your main category, such as the name of a school district, will appear multiple times. The time period does not have to be a year---it can be any length of time you specify. In this example, however, testing is done annually. Next, you need at least three additional columns of data. The motion graph will plot up to four. The graphic at the right does not show all of the points I included, but here's a list:

• Number Tested: Number of Grade 5 students tested for that year
• % Met Standard: Percent of Grade 5 students who "passed" the test
• % Level 1, % Level 2, % Level 3, % Level 4: Percent of students who scored in each of the four evaluation categories (Below Basic, Basic, Proficient, Advanced). Levels 3 and 4 should equal the percent who met the standard
• Enrollment: Total number of students in the district
• % Non-white, % White
• % Male, % Female
• % SPED: Percent of students (district-wide) receiving special education services
• % F/R Lunch: Percent of students receiving free or reduced lunch; used as a measure of poverty
• % Teachers Post-bac: Percent of teachers (district-wide) with a masters degree (or higher)

 Once you have your data organized, you will choose "Insert" and then "Gadget" from the toolbar. Then, select the "Motion Chart."

You can choose to place the chart on the same page as the spreadsheet or on its own page. Now, you're ready to rock and roll. Here is one configuration:

All of the circles you see represent school districts in Washington state. Not every district is shown. Some districts have such a small enrollment that it violates privacy laws to publicly report their data. But most districts---ones with a consistent enrollment of ~150 students---are plotted. On the x-axis, we have the variable for meeting the standard on the Grade 5 science test. On the y-axis, we see the % of students in each district qualifying for free or reduced price meals. Already, we don't see a lot of surprises. The "richer" the population, the better they perform on the test. The colour of the circle relates to the percent of non-white students. Here again, not too many surprises: districts with a fewer minority students do better. The size of the circle relates to enrollment. In the sample above, I've hovered over Seattle, the largest district in the state. Note that the figures associated with each variable also appear: 32.8% met the standard; 41% F/R Lunch; 59% non-white; 47,546 students enrolled.

When I press the play button, I can watch the graph change over time. What do things look like in 2011?

First of all, we're a little more spread out in terms of scores, but most districts are performing better. Seattle is about the same size with the same % F/R Lunch, but has increased its percent of non-white students. In fact, you could make that same comment about ethnicity changes for most of the state.

Motion graphs can be very interesting tools, especially for data sets that need a big picture. This isn't to say that it wouldn't be intriguing to plot student grades in one, but some of the organization required means it's not an everyday tool.

But hey, why don't you play with one for yourself? Use this link to go directly to the Google Spreadsheet with this motion graph (then make a copy to your own account, if you'd like to edit it). Or, use the embedded version below (not available via RSS).

Excel Apps for Android: Docs

The final stop in our tour of apps that give you access to your spreadsheets on a tablet is Docs. Google Docs. *shakes martini*

Unlike previous apps I've posted about, Docs requires a connection to the Internet. As lovely as it is to have files in the cloud, not everyone has a 3G plan with their tablet or the ability to consistently connect to a wifi signal. I feel like that this automatically puts Docs at a disadvantage for classroom purposes. But hey, let's see what we can see. After all, Android is a Google platform---perhaps Docs makes the most of things while being in its native habitat. Docs is a free download from the Android market.

I uploaded a file similar to one shown in the Zero Effect post, just as I did when testing previous apps (OfficeSuite, QuickOffice, and Documents to Go). Remember, there is a worksheet named "Grades" that is supposed to be hidden, embedded formulas and conditional formatting, and two graphs. Here is what the file looks like when first opened with Docs on the tablet:

Not too bad. Formatting looks like it might be okay. We've lost the graphs, but the hidden worksheet doesn't show. Editing is a bit clunky, compared to the other apps---you can't just click in a cell and fill it. You have to choose to edit a row or column and then save that work.

And after adding information?

Nothing happens. Conditional formatting is lost. Formulas don't work. The numbers are clear, but overall, this is really not impressive. So let's move on and see what happens when we use Docs to view a completed spreadsheet:

Like the others, this isn't too bad. With a fully loaded spreadsheet, the formulas and conditional formatting are applied. But no graphs---and no way to build them. That's so not cool. And, if you bypass the app and try to use Google Docs in your tablet's browser, you are no better off. Everything is just the same as the app.

On the good side of the balance sheet, all of the "offline" apps reviewed in previous posts can pull from Google Docs. So, if you want to store your spreadsheets in the cloud, but have the functionality of a better app, you really can have the best of both worlds.
 

So, what have we learned? First of all, you get what you pay for. The more expensive the app, the more robust it is when it comes to using your spreadsheets on a tablet.

Secondly, keep your expectations low. You're not going to be doing fancy-dancy data analysis with any of these. No data dashboard'ing. No (overly) complicated formulas.

Even though seamless integration between PC and tablet isn't a current possibility when it comes to spreadsheets, I don't know that it would hold me back in the classroom. Even if data analysis is limited, data collection might be facilitated. All that "anecdotal" evidence from conversations with students or observations of activities might be more easily captured. I'd love to see more qualitative data be used when evaluating student efforts. I, for one, welcome our new tablet overlords. We just need the apps to catch up a bit.

Gradebook in the Cloud

A couple of weeks ago, I posted a "Roll Your Own Gradebook" series for beginners. The advanced version will be available soon, but in the meantime, some of you might be interested in the GoogleDocs version of the gradebook.

I like GoogleDocs for a variety of reasons. "Cloud-based" documents are accessible from anywhere I have an internet connection, collaboration is simple, and sharing a snap. Mind you, these are exactly the same attributes which can be deadly for student grades. In the U.S., the Family Educational Rights and Privacy Act (FERPA) outlines the responsibility institutions have when it comes to student data. So, even though you can keep your gradebook on the web, please think carefully about whether or not you should.

The spreadsheet feature of GoogleDocs is really not ready for primetime, but it does afford some functionality. You have very few colours to choose from (and no way to adjust RGB values), limited formulas, and no way to pretty up your charts and graphs (even though you have some types unavailable in Excel). However, if you just need a down and dirty way to look at scores, it's good enough.

Here is a link to the unadulterated version of the gradebook. You can copy this to your own Google account and play with it to your heart's content. You can follow (nearly) all of the same steps as I posted for the Excel version (see Part I, Part IIa, and Part IIb). Or, create a new beast.

I have also developed a final version of the gradebook and reporting tool, with all of the steps applied, if you just want to skip ahead to the ending. This, too, can be copied to your own Google account for hours of amusement.

Ow! My eyes!

Keep in mind that many people have created gradebook templates in GoogleDocs and have posted them to share. Look around and see how you can improve on what's there.