Monday, August 15, 2016

Google Sheets Checkmarks

Last post I mentioned how I use Google Sheets with checkmarks to keep track of what students have completed in my class. Below I have created a video that shows how to do this:

Monday, August 8, 2016


A few years ago I started to become dissatisfied with our SIS that our district uses. I tried to set it up so that it will only show checkmarks to students on an assignment, but that ended up not working at all. I was (and still am) extremely tired of the point game that we have students play in our classes. So, I started looking into alternatives of tracking what my students are learning.

It took some time to find something. I finally settled on For All Rubrics. It allowed me to create a checklist of assignments that I wanted students to complete while in my class. I really liked this. It took some time and practice to get things the way I wanted them. I created several checklists for my students and then imported my students into classes. I assigned the checklists to those classes. I was able to use my iPad to walk around the class and individually keep track of what students were completing in class. No grades or points were assigned to these checklists, so I didn't need to worry about having my students play the point game.

This worked really well for quite some time. In fact, if you want to get away from your SIS and start having students completing assignment and not playing the points game, I would highly recommend looking at For All Rubrics. You can share rubrics and checklists with other teachers. They even have a badging system that you can use to award badges to your students. (I haven't really gotten into the badging thing, so of course I couldn't push it off to my students very well, but you may love that feature).

I wanted to have my students see their report without having to worry about another username and password. For All Rubrics does not have Google Sign In integration, so I didn't want them worrying about remembering (or better yet not remembering) a different username and password. I was able to find the report page that does not require a log in to see. It allows them to see the checkmarks they have completed and the other assignments they need to complete. I was able to figure out a way to link that page to a student's individual SIS account. It is a very complicated process that takes me several days to set up at the beginning of a grading period, but once completed it is there for students and parents to see with just a few clicks. If you want details on how to do it, please contact me and I can help you out with it.

The one problem with this whole system is that it is not very friendly for other teachers to use in their classrooms. Yes, it would be great if our whole school or district used the For All Rubrics website, but I don't have that type of control (I barely have control of myself). Plus, even if we went to this system, I'm doubting that many teachers would jump on board.

So, this last school year I wanted to try something different. We have GAFE in our district, and so students are pretty used to using Google Drive. I wondered if I could create a checkmark system for my students using Google Drive.

I first tried to do something in a Doc. However, to truly get it working right I ended up creating a table. Whenever I start using tables in a document I always start thinking that a spreadsheet would be better. So, I started a Google Sheet. This was the best thing I could start.

After some searching I found that Google Sheets has a data validation that allows a user to create a list of items with images. So, I did a little playing around with both Docs and Sheets. When I was playing with Google Docs before I found a special character of a checkmark and an empty box. I was able to copy those and put thing into Google Sheets in the data validation list of items. I also found out that I could use those same two images in formulas in Google Sheets.

With formulas and the data validation I was able to create a checklist of assignment that I wanted students to complete. The next thing I had to figure out was how to share those sheets with about 400+ students. I know I could create a copy of the sheet for each student and then share it with them, but I know that would take some time to do so.

I also wanted a way to have a sheet where I could see all of my students from a class and see where they are in the process of completing assignments. I was able to do this with For All Rubrics. I could bring up my class and see how many checkmarks each student had on one page. This was great for me, because it allowed me to zero in on individual students and talk with them about their progress (or lack thereof) and try to encourage them (or push them--motivationally, not physically) to complete the assignments they were missing. I wanted to still do this with my new Google Sheets as well.

So, as I started looking for a way to do this I came across a Google Drive Extension that I had been introduced to several years earlier. It is called Doctopus. It has become a life-changer for me. This extension allows you to take one file (or multiple files, or even a whole folder) in Google Drive and share it with all of your students. It puts the document into their Google Drive where they can look at it all they want. Of course Doctopus has a LOT of features that I don't use, so I would suggest truly looking at the whole thing as it could be a solution for you on many different levels.

Doctopus was a great answer for teachers before Google Classroom came out. However, Doctopus can also be used WITH Google Classroom. The cool thing with Doctopus is that it allows you to link into the individual sheet and gather information from that sheet.

I decided to try this out. I had my sheet with checkmarks. I then started creating a Doctopus sheet and imported my students into the sheet. I then was able to pick the checkmark sheet and was able to send it to each individual student. It took a few minutes for Doctopus to do so, but it was faster than I would have been. I learned to tweak the sheets so that the students couldn't enter the checkmarks themselves. I really like my students, but I work with junior high kids, and I know that a lot of them would just mark the checkmarks themselves just to say they got it done when they truly hadn't (sorry, I'm a bit jaded with this). However, having my mark the checkmarks also allows me to talk and meet with each individual student several times.

I then used the INPORTRANGE funtion in Google Sheets to count how many checkmarks each individual student had on their sheet. This allowed me again to see who was not progressing very fast and to touch bases with them.

With the checklist on Google Drive I found I couldn't use the Google Sheets app on my iPad to change the empty boxes to checkmarks. However, I found that I could use my Android phone to do so. This turned out to be so much better. Instead of "lugging" around my iPad in my hands, I could pull out my phone and change a box into a checkmark and then place it back into my pocket.

I have totally loved this whole new system. I don't have to worry about points for my students. I can help them continue to learn without playing the stupid point game. I also started tracking how many assignments were completed throughout a class compared to when I kept track of them on my SIS. This last semester I had on average students completing twice as many assignments as I did when I used points. I am hoping to make this even more, and to hopefully get past the "how many assignments completed" idea as well. I want my students learning. If that happens by completing the assignments I have created for them, great. However, if they can think of a different way to learn and they can prove that learning, then I want to be able to be open to that as well. This will take some time, though.

Monday, August 1, 2016

Learning ePortfolio

This last year as I was helping my students to learn without using grades I started thinking about how students can prove that they have learned the information they are supposed to learn in school. This lead me to start looking into ePortfolios. So, I started searching around for examples of ePortoflios that are out there and how people are using them in schools. Surprisingly there is a lot out there about ePortfolios, but I could not find examples of how I could use it in my classroom with my students.

Then, one day I found that a local college (Salt Lake Community College-SLCC) that requires all of their general education students to create and use an electronic portfolio. I was really intrigued. The coolest thing was that they have videos to show students how to actually create the portfolio. As I looked through their tutorials and resources I began to really like the ideas they had come up with.

SLCC (pronounced "slick") allows students to use Google Sites, Weebly, Wix, Jimbo, or WordPress. However, some of these websites are blocked in our district, and let's be frank, WordPress is not the easiest thing for people (especially for most 7th-9th graders to learn to use). Our district recently started using Google Apps for Education (GAFE), so I started leaning towards Google Sites for my students' ePortfolios.

I went through the Google Sites tutorial from SLCC on how to create their ePortfolio. As a programmer I have never been too impressed with Google Sites because it doesn't allow a lot of flexibility, especially for those that know how to program. However, it is very simple to use and would be easy for students to use (mostly because of the simplicity). I was able to setup my first ePortfolio site and found it pretty simple to do. So, I thought I would try it out with some of my 7th-graders. Let's just say it didn't go too well (not terribly, but I have done things better).

So, I found out that Google Sites allows a user to create Templates. I then started putting together a template that my students could use to create their ePortfolios without actually having to set up all of the pages. I then started trying it out with a few of my other students. This worked a lot better. However, I wasn't too impressed with the setup of the site.

I started thinking about what happens if one of my students transferred to another school in our district. Then I wondered if they could use the ePortfolio if they went to another district in my state. Going further, what would happen if they moved to a different state or bigger yet, another country. Would they be able to use the ePortfolio they started with in my class, or would they need to start all over again because it was specific for my school, district, state, or country?

This began a huge search that would take several months. I first started listing classes that students cold possibly take. However, that seemed limited because those same classes could not even be offered in other places. So, I wanted to list the knowledge and skills that a student should be learning while taking classes.

My principal had shared the Future-Ready Plan from the Lakeville Area Public Schools. Although I have come to dislike the phrase "Future-Ready," I found myself returning to this file to look at the overall global things we want students to learn. You will see on page 9 the six basic literacies that this district listed. I decided to start with these literacies on my ePortfolio template.

So, instead of classes I started listing these literacies. I also started listing other basic, general literacies that students should know or have. I then had to start searching for what those literacies really meant. How could students upload an assignment or video or whatever to prove that they have the knowledge and/or skills for that literacy. I started to find national standards that have been created for a lot of these literacies. I liked the national standards better because I found that a lot of the state standards we have are very limited and outdated.

Now, I didn't want to get extremely detailed with these standards. I didn't want just a checklist of specific assignments for students to do. I want my students to see that what they are learning in all of their classes are bigger ideas instead of just the assignments that teachers have created. And, instead of listing "Classes" I decided to create a page called "Knowledge & Skills" to reflect truly what was being presented.

My goal in all of this is to help students to start to learn how to create a portfolio. I would hope that they could take their ePortfolio with them as they go through the schools in our district and start to collect, select, and reflect on the proof of their learning. Then, after they graduate, hopefully they can continue to take the ePortfolio with them into tertiary school and to their work life as well.

I also try and tell my students that this is just a template. They can change the portfolio any way they want. I also tell them that if they want to use a different site for their portfolio, they are more than welcome to do so. However, if they choose to use a different site, they need to do that on their own and basically have the same type of content.

This next year I will be trying to use this template a little more with my students than I did last year (isn't that what we do as teachers?). If you have any comments or suggestion, please help me to make this template better. You of course may use it all you want. Copy it, change it, improve it. Tell me how you did so and how it worked so I can improve mine as well.

Here is the link to the template (make sure you follow the link as there is one that is similarly named, but is totally different):

The site should look like this (if the template you see has birds, you are on the wrong template):

When you go to copy this to your Google account, you may get an error stating that you need to request permission to use the template. If that happens, return to the page and click on the "Use Template" button again.

I am also currently starting to set this template up for the "New" Google Sites. This will take some time, so please stay tuned.

Third parties may be placing and reading cookies on your browser, or using web beacons to collect information as a result of ad serving on this website.