Our awesome Google certified instructor Bill showed us an integration of Doctopus and Google spreadsheets. Doctopus takes some getting used to and I mentioned it in my post on Google Classroom. There is a video on that post that I think does an excellent job of walking you through how to get started with Doctopus (add on for Google sheets).
One really nice thing about Doctopus that sets it apart from Google Classroom is that Doctopus produces a spreadsheet for each assignment where you have a list of the students, the Google key for their assignment, and a clickable link to that assignment. But I learned something awesome at this workshop! You can do this with a Google Classroom assignment as well! Once you have assigned on Google Classroom, you can go into Doctopus and choose the option "Ingest Google CR assignment." Then you will be able to choose from a list of all of the assignments you have created in that class to create a spreadsheet for. You essentially get a slicked up version of this spreadsheet through the interface on Google Classroom when you click on the assignment, but it actually takes two clicks for each assignment instead of the one when you are looking at it from the spreadsheet. Also, you can use this spreadsheet for what the workshop was really all about--compiling data from student assignments (example used in this class was a rubric) into one neat easy-to-read spreadsheet.
Tip on this: I was not getting the "Ingest Google CR assignment" option on Doctopus so I did a quick Google search and found this awesome Google+ community for Doctopus. I was super excited when I saw the blurb from the page below--this just further confirms my theory that if I am having a problem with something, there is probably someone else who has had the same problem and blogged about it. Now I can be contributing to this wealth of knowledge! You just have to know how to search (which students need sooo much help with, let me just say). Just a side note here, look how awesome Google is at recognizing synonyms to search terms.
Now, what Bill was showing us is a project he worked on with the elementary level teachers to create a rubric that the teacher could fill out for each student to grade assignments throughout the semester. This rubric was shared with the students, distributed through Doctopus, though you could do this with Google Classroom as well (but you CANNOT set permissions for view only--Google classroom will make a copy for each student that they have editing rights to and you would have to manually change the editing rights for each rubric, so Doctopus is a slightly better option at this point for this type of situation). Each student has their own rubric that they can see and so as you add grades to it, the student is able to see those grades. Now the really awesome thing is that you can import that data onto a dashboard spreadsheet and see all of the grades at once in one place! That is the really tricky part. It involves spreadsheet equations that can seem a little daunting. Here is the link that Bill created as a quick reference guide, and here is a video tutorial he made for how he set it up in case you want to see the details. I am going to give you a few notes on the equations that I learned that are good overall notes for using spreadsheet equations when importing data from other spreadsheets. Here is a sample web address for a Google sheet for reference:
https://docs.google.com/spreadsheets/d/1cLlGCD0HTnuDNgvoUAwPqQKr2nhaudXh7_qRDtkG0Ao/edit
The part highlighted in red is the unique key that is tied to this particular sheet (there is a key for any Google product you have in your drive). When I list "key" from now on, this is what I am talking about. You can just copy the key from your address bar when you are in the sheet and paste it into the equation, replacing the word "key" that you see in the equation below. The key is the way to reference the spreadsheet you want to pull data from into your new dashboard spreadsheet. Here is the equation for importing. (For the parts underlined, you enter your own info):
=IMPORTRANGE("key","tab name!firstcell:lastcell")
So if I wanted to import data from the sheet link above, and the information was on the tab labeled "New Roster" and the information started in cell A2 and ended in cell B19, the equation would look like this:
=IMPORTRANGE("1cLlGCD0HTnuDNgvoUAwPqQKr2nhaudXh7_qRDtkG0Ao","New Roster!A2:B19")
Note that for these equations, you will get a REF error and you will need to click the cell and click "Allow Access" before the data will be imported. To establish your dashboard spreadsheet in the first place, this info would be taken from the assignment spreadsheet that Doctopus produced or that you ingested from Google Classroom. You would want to start with importing the students' first and last names and they key for each of their assignments. Once you have this imported, you can use the assignment key as a reference for each student, so just use the cell that holds the key in your formula. If I wanted to pull data for student Susie Q.'s rubric (let's say her rubric key is listed in cell C2) from the tab we called "Writing" for the average grades (listed in the 7th row on the sheet) for all assignments listed across many columns (ie D-P), my formula would look like this:
=IMPORTRANGE("C2","Writing!D7:P7")
If you are going to do this type of import, it is important that the criteria for the rubric is listed vertically while the part that is filled out is listed horizantally.
Once you have your dashboard set up, you can do conditional formatting to see certain grades as a certain color which is helpful for getting info at a glance. Bill also showed us how to use Data Validation to get a drop down menu of options for a cell so that filling out the rubric is fast and easy. (Right click on a cell to get the Data Validation option). You could have options be 1, 2, 3 and write a note about what the numbers mean (see below). Or you could have words, but you would need to use some fancy "if, then" equations to do the conversions to numbers if you wanted to get averages.
Now when I hover over the cell, I get the descriptions of the options (top pic below) and when I click on the cell, I get that list of choices (bottom pic below).
That's about it! I will definitely need some practice with all of this, but there were little tidbits here and there that I can start using right away to make grading more efficient! My biggest concern is that using both Doctopus and Google classroom will result in the students having multiple folders to try to figure out how to deal with. As long as you are really clear about where you want students to find what you are pushing out to them, this shouldn't be a problem, and as they get used to the process it will become easier. I like having the option on Doctopus to choose more permissions for the assignments AND by the way, Doctopus has a differentiation option which allows you to send different assignments to different students, Classroom does not have that option (yet).
Also don't forget about Goobric. The Goobric rubrics are not speadsheets and could not be used for integration this way (I don't think... I will have to play around with it) but Goobric is a really awesome way to attach rubrics to student work. It is an extension for Chrome and is available in the Chrome store. As I was going back over my notes and working though this post, I came across this video, Google Classroom, Doctopus, Goobric, and Docappender for Self, Peer, and Teacher Assessment. I haven't watched it yet, but I am going to leave it here to take a look at later!
