Google Sheets – Query Function a Video Tutorial

Recently I posted about Google Sheets and how awesome the query function is. I thought it was so cool that I would make a little video about how to do it, in case my instructions were not super clear. Watch it below and leave comments about how you would use the query function in your classroom or school.

Posted in Google Apps, Helpful Tips, Patrick Cauley, tutorial, Video | Tagged , , | Leave a comment

Project Based Learning and Class Size


By: Tony DePrato | Follow me on Twitter @tdeprato

The Class Size Issue in Project Based Subjects

The relationship between class size and project based subjects is inverse compared to studies that look at traditional courses where instruction is rote, and the differentiation needs to be very focused.

Of the top 22 Hattie indicators (The Hattie data can be viewed here. ), 10 connect directly to courses that at project based:

  • Self Report Grades
  • Piagetian Programs
  • Response to Intervention
  • Cognitive Task Analysis
  • Classroom Discussion
  • Teacher Clarity (Students Questioning Teacher Instruction)
  • Reciprocal Teaching (6 Facets of Understanding)
  • Feedback
  • Formative Evaluation
  • Self Questioning

Class size has been a central focus in nearly every school improvement plan I have been connect with. In fact, I recently helped build a schedule that was nearly solely dictated by class size.

As some one who solely works in project based subjects, team driven contests, and peer reviewed assessment I can attest that small classes are detrimental to learning in these environments.

When a class falls below 12 students, the student input, instances of serendipitous discoveries, the diversity of teams, and the needed conflict to fuel trial and error scenarios  all diminish. To be clear: the class becomes boring and stagnant.

Students need to be formed and re-formed into teams and groups in a project based environment. They need variety of opinion. They need to take the lead and be the teacher; they need to lead their peers; and they need their peers to explain “what went wrong” when failure happens. And failure will happen more often than trophies are presented.

If a class size is too small, this process (learning spiral) becomes repetitive and predictable. In my experience, small classes can be a stimulus for groupthink.

 

Read More At The International Educator

Posted in Educational Technology, Instructional Technology, Opinion, TIEONLINE | Tagged , , , | Leave a comment

Inventory – Just do it!

For the past three weeks I’ve been putting together an inventory for our school. We’ve never had one here before so I thought it was time to make that change, but this post will explain why we did it, how we did it and how we will use it going forward.

I will start off by explaining why we didn’t have an inventory in place and what prompted me to decide now was a good time to set one up. My school is small, less than 500 students and it only goes up to 8th grade. Like a lot of schools (large and small) widespread WiFi is pretty new. Less than 5 years old. Before that we had desktop computers, plugged into our local network with an ethernet cable. So if we had a room, we most likely had a desktop computer in it. Taking inventory would be as quick as looking at a map and counting.

With WiFi at the school people no longer wanted desktops. They wanted laptops. Fair enough, now we have devices that are no longer “chained” to a desk and most likely leave the campus every night when our teachers would go home. This would have been an ideal time to start the inventory. Now we have student iPads, computer carts, teacher iPads and spare laptops. Now there are a lot of moving devices on and off campus – I felt an inventory was essential.

Why

A lot of people think that the purpose of an inventory is track who has what device and you know what? They’re not wrong. It is good to know who has what device and to track any problems with the device and so on. Having a record of the device itself is also valuable. If we know it is a device that is constantly causing issues, then it may have to be taken out of service for repair or replaced. An inventory helps you track these issues.

Also, when budget time rolls around it is good to know how many older devices we have and how many of those we need to recycle and replace. Instead of guessing, jotting down notes, setting up multiple meetings with people, you can run a report and discuss who has what device and if it does need to be replaced. Now you have concrete numbers to work with, not just educated guesses. When you are dealing with concrete numbers, then you greatly reduce the risk of over or underbuying a product. This is a good.

How we set it up

There are a lot of inventory systems out there and most are expensive. I didn’t want to spend money on this system. As I mentioned we are a small school with one campus. Most proprietary systems are way more than what we need. Here is what I wanted. A system with a lot of search features, a number of fields that I could manipulate for each device and a way to export that data (PDF, CSV or Excel). Oh yeah, I didn’t want to pay for it or pay very little (ideally less than $100/year).

I found (thanks Tony) GLPI. This is a free open source inventory and ticketing system. It does a lot more than just that, but this is all I needed. There are others out there, Spiceworks and OCS. They seemed pretty good but I was familiar with GLPI and I didn’t see a real advantage of the others.

There is a catch with open source software. There is no support line. You need to trouble shoot it all on your own. Also, you need to host it. We have it hosted locally on a Mac Mini using MAMP (which is also free). I’ll write more about how to set up GLPI and MAMP on a Mac – it’s easy and you don’t need to be a super techy person either.

Once installed, I started to configure it. I wanted to know where in the school (middle school, lower school, office, etc.), if it was working, who was using it, inventory number, serial number, etc. Check out the screen shot below.

When I add a device, it looks like this. I have a few templates. They basically fill in some basic information about the device ahead of time.

Then I can input all the information I need. If you are setting up your own, don’t feel the need to fill in al the information. Only record what you need. Sometimes too much information just gets in the way. This info is basically all that I needed. With this info, I can tell if the device is allocated to a student or faculty member, where, who, what type of device (which gives me a solid idea of how old it is) and if it was a student, when they graduate.

From that first screen. I am able to search based on any of those criteria that I inputed. So, if I want to see how many MacBook Air computers we have allocated to students, I can see that!

If I want to export that search result, I have a bunch of options. I can do PDF, CSV or SLK (apparently this is Microsoft format meant to transfer info from databases to spreadsheets).

The system wasn’t entirely free. The software (GLPI and MAMP) was free, but I wanted a bar code scanner and a label maker. Here is what I purchased (all from Amazon). The bar code scanner is this guy. He s cheap and seems somewhat reliable and so far has worked fine.

For the label maker I went with Brother. They make good label makers and I wanted one that I could connect to my computer and print out a bunch at once. Brother has some software that allows it to pull data from a CSV file so it will print lists of people. Nice.

I spent a little more than I wanted and picked up this guy. DON’T BUY FROM BROTHER DIRECT!! This same printer was $150 MORE!

Then I bought some label tape. I bought black on white but made sure to get the extra strength. I wanted to get the silver with black letters, but I found out that the barcode reader will not read them reliably.

How I can use it

As I mentioned before, I can use this data in a variety of ways. Of course I can track who has what and even record anecdotal notes such as the condition, minor observable damages, etc.

Our school is in the midst of reaccreditation and this data will be very useful for our report. We can say how many devices students have, the ratios of those devices to students in each grade and really demonstrate how many opportunities students have to engage and utilize technology.

From a budget perspective, I now have a very clear understanding of what devices we have and what we need. We budgeted that our devices should be replaced every four years. Running a few reports on GLPI will give me a specific number of what devices are ready to be recycled. No guesswork anymore. We know that we have 47 devices that meet that criteria and therefore that’s what we need to order.

If a teacher wants more devices, since we have such a strong sense of what the budget will be, we can sit down and talk options. It’s not one of those Well, let’s wait and see, situations. You know how many devices you need, you know the price, you have a working budget and should be able to talk intelligently with that person about what to expect.

Inventories are important, but they don’t need to be done just at the upper echelons of administration. You can use GLPI for a lot of different situations. You could use it to set up your own classroom library. This way you could track who has what book and how popular some books are compared to others!

Ever try to keep track of theater or IT equipment? This could be your answer. Know who is working with what and for how long. I am sure there are other examples too, please leave your ideas and examples in the comments section!

Posted in Helpful Tips, Patrick Cauley | Tagged , , , | Leave a comment

Google Sheets + Query = Hell YEAH!

A few weeks ago I stumbled upon something that has got my a little excited. I mean did you see the exclamation mark in the title? That is not to be trifled with people.

At any rate, I have stumbled upon the QUERY function from Google Sheets and man this is awesome! To be simply, this will allow you to have database like functionality to a Google Sheet. That may not mean too much to you, but it will.

At my school we have a list of approved parental chaperones by grade level. Basically these are family members. This list is made up of interested family members who have had a background check. No background check, means parents cannot attend. Simple as that.

Field trips are organized by the teachers in the classroom. Therefore, they are responsible for not just the students attending but making sure that parents who have been “approved” by the school can go. The teachers should be referring to a list and that is where the problem enters.

It was a mess (organizationally speaking). It was a Google spreadsheet but everything was entered manually. There were tabs for each grade level, but man it was a pain to update.

Here is what the IT team came up with. We will put all the data on one sheet. Then make a tab for each grade level. Finally on each tab we would use the QUERY function to auto populate the tab with the appropriate information for that grade level.

That way the original data didn’t need to be “organized.”

Make sense? It will. I’ve created a new project to show as an example. I have students in different grade levels attending workshops on different platforms. Worry not folks, these names came from a website that generates random names. Any coincidences that may have occurred are just that.

So here is my data.

Now what I want to do is separate it by workshop. That way I know exactly who is going to attend the Apple, the Google, the Linux and the Microsoft workshops. There are plenty of ways to do this using the vlookup function and so on, but this way is much faster and much simpler.

First, I will make tabs for each workshop.

Before I start the QUERY magic I will want to name the range of the data. On the tab that is called All Data I will select all of it by hitting CTRL+A (Windows) or by hitting CMD+A (Mac).

Now I will select Data –> Named Ranges… from the Google Sheets menu.

A new side window will appear asking you to name that information. I named mine workshop. You can name yours whatever you want, but when referencing back to this data the name must be perfect. If you capitalize the first letter, you have to capitalize the first letter in your QUERY.

Now in the Apple tab in cell A1 I will type this formula.

=query(workshop,“SELECT A,B,C WHERE C=’APPLE’”,1)

This is what should happen in that tab.

Now let’s take a look at that formula and break it down.

  • =query – This is you telling Google Sheets you will be using the Query function
  • (workshop – Remember when we selected all that data and named it workshop,? Well this is telling Google Sheets that we want to look at that pool of data. The comma is there to signify that we are ready to move onto the action.
  • Select A,B,C – This is here to say that of all that data we want to look at these three columns. If you only wanted a list of names you could but Select A and call it a day.
  • WHERE C=’APPLE’” – This tells the query to display only the information associated with the word Apple is found in column to C.
  • 1 – The one at the end corresponds to how many header rows there are (I think – I’m still learning here too 🙂

It’s awesome! One function in one cell is populating the worksheet! It also updates dynamically so when you add another student to the worksheet it will automatically update the correct page with that information.

Awesome!

If you want to see my example click the Google Image below.
It is only view only but you can make a copy and look at the formulas which are all in cell A1 of each respective cell.

Now, I did not just stumble upon this and figure it out all on my lonesome. Oh no, I had a little help from two very good websites that helped break this down for me.

One is from Ben Collins, a spreadsheet Developer and much more. Check out his excellent introduction to the query function on his website.

The other website is Coding is for Losers. This site has a tremendous treasure trove of resources and will take your spreadsheet to the next level! You can find the information on query right here. They also have great YouTube videos. Check out their channel here.

If you have any tips or tricks feel free to leave them in the comments!

Posted in Google Apps, Helpful Tips, Patrick Cauley, tutorial | Tagged , | Leave a comment

Clarify – Today is a dark day

As a professional, there are those magical moments when you come across a system, technique or app that just works. It doesn’t just works, it works exactly as expected. It crushes those pesky expectations and everytime you produce a product using it, you feel as though you’ve bettered yourself, your peers, your clients or all of the above.

Clarify, my dear friends, is that app for me.

As an educator and working with IT, explaining how to perform tasks is a daily occurence. Whether I am working with administrators and the schdule or students on how not to reply all to an email, I and the IT team are needed. Clarify allowed us to work smarter. We could prempt these problems by using Clarify to make step-by-step instructions. Not only could you make these instructions, easily and quickly, but then you could upload them to Clarify’s server, so you could email out a link, embed it into a website or heck even export as a PDF or Word file. It was awesome.

The app itself is awesome, but Clarify’s awesomeness didn’t stop there. Oh no. Their support was second to none. A couple of years ago I upgraded to Clarify 2 and was having some issues with it. I emailed their support. None other than Trevor DeVore (co-founder) emailed me back. It took about a week and a lot of work (mostly on Trevor’s part) to pinpoint the problem, but it turned out to be a third party app that wasn’t playing nice with Clarify. Most tech support teams would have given up (beliveve me I know) after the first couple of solutions didn’t work, but Trevor kept at it until it was working.

Clarify is a product of Blue Mango Learning and they make a “grown up” version of Clarify called Screen Steps. It is definitely more tailored for larger companies for their training purposes and apparently that part of their business is growing pretty well because they are just in a position to dedicate the amount of time needed to keep Clarify supported and to keep their servers up and running.

You can read their reasons from their blog here.

You can’t blame them, there are only so many hours in a day and instead of letting a service sit their and fester with the “appearance” of support. They are stepping away from it.

Mark my words people, I will use Clarify until the wheels fall off. I’m thankful to the people of Blue Mango for their work and support and having the great idea of creating a program to develop step-by-step instructions for everybody.

Whoever (if anyone) steps up in this niche market – beware! You have some big shoes to fill.

Posted in Opinion, Patrick Cauley | Tagged | Leave a comment

Episode 141 – GO REDS!

141

Tony and I were in Cincinnati together and took in a Reds game (they won by the way). No, that is not us on the jumbotron. While there we also had a chance to sit down together and talk Ed Tech, which is what we love to do anyway.

Check out the talking points below!

  1. Hello Cincinnatti! 
  2. What’s been going on? 
  3. Tony’s post on Technology Surveys for New Hires. 
    1. https://itbabble.com/2017/07/14/technology-surveys-for-new-hires/ 
  4. Patrick is back on Mac – sort of Extortion = Swivl 
    1. Crazy prices now 
    2. https://www.swivl.com/store/ 
    3. https://www.kickstarter.com/projects/satarii/swivl-multipurpose-motion-platform-for-mobile-and  
    4. What does it do and why is it popular? 
https://itbabble.podomatic.com/enclosure/2017-08-25T07_21_04-07_00.mp3″

You can download the show here!

You can also subscribe to us on iTunes or Podomatic.

Posted in Uncategorized | Leave a comment

Redefine PD with the 80/20 Principle

By: Tony DePrato | Follow me on Twitter @tdeprato

A very significant part of Educational Technology Leadership is devoted to professional development, new systems implementation, and the long term planning of support.

Every year as the semester starts-up, administrators around the world are planning for professional development (PD). There is pressure during those initial weeks to try and rapidly develop the faculty within new areas, to help everyone review all current requirements, and to re-train in areas of concern. Many of these areas rely highly, or solely, upon technology; technology is often the center of the professional development process.

Year after year, group after group, and plan after plan, results tend to be the same. There is never enough time to meet everyone’s agenda, teachers feel rushed, and confidence among many is low but silenced. So why do organizations follow this same pattern?

After many years of asking this question, and proposing options, the answers seem to come down to:

  • This is the only fair way to expose EVERYONE to EVERYTHING.
  • The goal is not mastery; the goal is introduction; mastery comes later.
  • Large groups working together help to create future support groups; the process is team building.
  • Support and resources for PD are easier to manager in mass; the first week or two of the new year shift support to critical needs.

Everyone is 100% and 100% is Wrong

The Pareto principle (80/20) is taught in economics, business, marketing, etc., because when tested, it tests true.

The Pareto principle (also known as the 80/20 rule, the law of the vital few, or the principle of factor sparsity)[1] states that, for many events, roughly 80% of the effects come from 20% of the causes. (https://www.wikiwand.com/en/Pareto_principle)

For example:

  • 20% of the customers create 80% of the revenue
  • 20% of the software bugs cause 80% of the crashes
  • 20% of the features cause 80% of the usage
  • 20% of users create 80% of the technology support tickets.

80/20 is often seen as a negative metric, when in fact, is a great opportunity to improve PD outcomes.

Following the 80/20 rule, any given PD item needs to be mastered by only 20% of the organization in order for the entire organization to benefit.

Read More @ The International Educator

 

Posted in Educational Technology, Instructional Technology, TIEONLINE, Tony DePrato | Tagged , , , , | Leave a comment

Technology Surveys for New Hires

By: Tony DePrato | Follow me on Twitter @tdeprato

Since 2008, I have been working with groups of new hires. There is much stress and confusion when people are relocating to another country. I always try and provide the information new hires need to understand the technology culture at the school, and within the country.

Initially, I was simply doing Q and A, and creating FAQ documents. One year, I realized that I was missing a huge opportunity to do some data driven decision making. I began to develop a set of surveys.

Survey data helps to shape the professional development for orientation and possible configurations for IT systems. Additionally, the data aids in the team building process by identifying new people with higher level skills. These people can then immediately contribute at the level they should be contributing instead of being sidelined because they are new.

Meet Them Where They Are

Many schools are hesitant to do surveys because new hires have a tremendous amount of paperwork to complete. Schools often do not want to add any additional communication to an already very busy process.

I do understand this view point, however, new hires will not be overwhelmed if a technology survey is incorporated into an already required technology process.

In the spring, I recommend all schools setup and activate the email accounts for the new hires. The moment they sign in the first time, they are a captive audience. The first email they see in their inbox could be the technology survey. New hires usually like getting their new account in the spring, so they will not be irritated at the process.

If the school has setup social media for new hires, such as a Facebook Group, those accounts can also be used to share links to surveys.

Read The Rest of the Post @TheInternationalEducator, TIE ONLINE

Posted in Educational Technology, Google Apps, Helpful Tips, Instructional Technology, TIEONLINE, Tony DePrato | Tagged , , , | 1 Comment

Interactive whiteboards should DIE!!!

Yes, you read it correctly. I typed three exclamation points in the title. That is how strongly I feel about interactive whiteboards. Let me tell you where that comes from. It was my first international school and they were doing a quick tech presentations (setting up email, our electronic grade book, etc.) One of those presentations was with the school’s new Promethean Interactive Whiteboard.

At first blush I was impressed. I really was and wish I had one in my class. There appeared to be endless possibilities for student engagement. It seemed like a perfect marriage of technology and education. A great compliment to great lessons.

That’s the problem. The interactive whiteboards are not complimentary to lesson plans. In order for them to be effective, in my opinion, the interactive whiteboard has to be the center of the lesson and you have to use it all the time.

Menus

You may ask yourself Patrick, what’s the big deal? I am so glad you asked. Let’s start with the menus. Dear God the menus. There are way too many of them. Just to bring up that coordinate plane, it would take 3-5 clicks (or taps using the pen). You have to go from menu, to menu, to menu to find something and if you didn’t know where it was and had to looking forward then you have a few minutes of dead air in your classroom.

Then there is the alignment and calibration. Calibrating the pen for the boards is usually quite easy, but if the projector is not properly installed, sometimes calibrating the pen can be a real pain. The reason is, the software usually has places for you to tap with the pen in order to make sure when you place the pen on the board it lands true and makes the selection or annotation that you want to make.

You see good reader, if the projector is installed incorrectly or not set up well enough, then those calibration targets may be on the border of off the whiteboard all together making accurate calibration a pipe dream. Sometimes, this can be remedied with a quick adjustment on the projector, but there are other times, when the projector itself will have to be moved and remounted – a time consuming and depending on your interactive whiteboard provider could be an additional cost.

Cost

You can’t talk about a major addition to a classroom or school without considering cost. As we all know money does not fall from the sky and schools need to work within a budget (if they want to keep their doors open that is). So one needs to consider these costs:

  • Unit itself
  • Installation
  • Upkeep
  • Repairs

Now these costs differ from vendor to vendor and depending on your particular contract. I am going to say $5,000 per unit which includes a projector. If you pay more than this, please don’t go to your vendor, show them this piece and say Hey bub! What’s the dealio? I’ve known schools who pay much more just because of their location and services available.

Now, one thing that most schools don’t think about is the long term with these devices. Once it is installed and working properly it will take care of itself. Like a refrigerator or a stove. This is true . . . for a while. The very first time you turn on the projector, the picture will look great (at least I hope it does). However, if you compare day 1 to day 50 to day to day 365 you will notice that the picture will be dimmer each time. It’s just how light bulbs work and that is the heart of that projector. When that light bulb goes out, you need to replace it. These bulbs are not cheap and if you’re replacing 20% of your bulbs every year, then your costs go up. Be sure to tack that onto the original budgeted item. As you can see these costs can add up quickly.

Alternatives

So there are my problems, so what can schools do about this? What options do they have? Schools and teachers don’t want to not have an interactive display in their room. It looks good for parents and guests walking through the halls. There are some teachers who do use them.

There are alternatives out there. There are interactive displays (no projectors) that can replace your school’s interactive white board. The advantages to these boards are numerous.

  1. Dim much slower
  2. No bulbs to replace
  3. Easier and cheaper to mount
  4. No pen calibration or very easy pen calibration
  5. Lower costs over time
  6. Higher resolution (better picture)

Microsoft makes one called the Surface Hub which comes in two sizes – 55″ and an 84″ Their prices are $9,000 and *gulp* $22,000. Yeah that second price is a little hard to swallow. If your school is on Office 365 it might be worth considering and there may be a discount. It does some neat things too and would be great for Skype in the Classroom.

Google has the newly minted Jamboard (what a terrible name) made by Benq for $5000 which looks pretty promising.

This is more of what I am talking about and while 55″ isn’t as large as I would like and the rolling stand is an extra $1000 and of course what would an IT product be without licensing which you have to pay every year.  However, add all that up and I still think the Jamboard would win out over a traditional interactive whiteboard.

Bottom line is I don’t like them. They don’t deliver on their promise of really enhancing a classroom. They are too cumbersome to use and just not super effective.

What do you think? Leave those comments below!

Posted in Opinion, Patrick Cauley | Tagged , , | 4 Comments

SoundCloud ☹

I’ve talked about SoundCloud before as a good, free service for classes and schools to use to publish podcasts. I mean there are Android and iOS apps which make posting dead simple. You can also find some pretty great music and other quality podcasts on the site too.

In the past couple of years there have been articles that SoundCloud is in financialtrouble. These articles have been around and people have been wondering how and when (if at all) the much beloved service would become profitable. I mean it has a really large user base, depending on where you look it looks like there are between 50-200 million users.

Well the news today is definitely not good. SoundCloud has closed two offices and laid off  40% of its staff. Not a sign of a company moving in the right direction. Apparently the people at Soundcloud are making this move in an effort to become profitable, but time will tell and it seems like an extreme action.

If you or your school uses SoundCloud it may be time to think of finding a new provider to host your podcasts.

There are a bunch out there. IT Babble uses Podomatic (the free version) there are other options out there. Rather than me drone on, I figured I would like to a pretty concise article from TechNorms.

Posted in Patrick Cauley, Podcast | Tagged | Leave a comment