Tuesday, April 03, 2012

Managing Google Docs in the Classroom

Saturday was a terrific day at EdCampMe. It was a great chance to hang with some friends and chat about meaningful ways to educate children.  One of the questions that came up in a session I was in is something that I get asked a lot.
 "How do I manage my Google Docs with so many  documents coming in from so many students?" 
Teachers love how easy it is to collaborate with their students, not having to worry about losing the file  and all the other bonuses that using Docs brings, but tracking down assignments can be a nightmare.  I've covered this before from the teachers perspective and the students perspective here, but I'll run through  how I do it step by step.

Step 1) Students create a new Collection for the class and title it: Subject - Name
For example: Science - Johnny

Step 2) Students then share the Collection with the teacher.

Step 3) You make a new Collection called Period 2 or Science or whatever makes sense. Finds the student Collections (in the lower left under Collections shared with me.) Drag them to your newly created Collection.

Step 4) Click here to copy the Assessment Collector Google Spreadsheet. (You must be logged into your Google Account.)

Step 5)  Embed the form into your website. Don't have a website? Think about using a student as a webmaster.

Step 6)  Once you edit the names of the assignments and students have entered the links to their assignments, you can filter by period and assignment.

Then use the built-in Viewer tab.

This allows you to view, comment or edit the Google Doc, Spreadsheet or Presentation 'handed in' without ever having to leave the window. You can then click NEXT through all the collected  assignments.

An important aspect of this process is that the students are sharing work in a way that teachers can edit while simultaneously building their own collection of assessments.

Step 7) One limitation of the viewer is that it only reads URLs that begin with http not https. So, you use the Find and Replace feature built into spreadsheets, or if you're a little adventurous set it up to do it automatically.  The script to do this is already included, you just have to turn on the Form Submit trigger.

Step 7a) Select Tools > Script editor
Step 7b) Select Resources > Current script's triggers...

Step 7b) Click  No triggers set up. Click here to add one now.
                      Change On open to On form submit and click Save

Step 7c) Click Authorize > Click Close and Exit that tab.

This means whenever a student copy and pastes a URL that starts with https it will replace it with http.

Why can't it just be turned on when you take the copy you ask? 
Because of the possibility of nefarious users sharing malicious code with you.
This way you have to authorize the use of the script.


  1. If you are using Google Docs heavily for classroom management, I'd recommend checking out classconnect.com.

    Disclaimer: I'm not associated with them, I just recently discovered them while looking for different curriculum management systems.

  2. Hi Kern
    Fascinating use of Google Forms.
    Can you say anymore about how the viewer works in your Google docs document. You have some very interesting formulae in the spreadsheet which I don't follow.
    Anyway, thanks for this great idea. If I can get it working, it will be brilliant for quickly reviewing student work.

  3. Hi "Sleepy" thanks for the heads up, I'm checking it out now :)

    Thanks Gerry,
    The formulas can be found here:

    and the viewer is a custom Google Gadget inserted (that's why in Chrome it asked if you want to Load Anyway)

  4. Hi Kern. I love how you've set this up! Wow. I have run into a problem that I can't seem to fix quite yet.
    Many of the docs assignments that students may turn in will have a URL beginning with https. I went through your steps to set up and run the FindReplace script, but so far I've received an error on line 4.
    (var p = sheet.getRange('F2:F').getValues();)
    I tried to submit a couple of assignments with the https prefix and when I go to VIEWER, it just displays the URL instead of the live assignment. I assume that has been because of the error when trying to run the script.
    Any ideas?
    Thanks so much for this post - I think it will be incredibly useful!

  5. Anonymous11:35 PM

    How'd you set up your document so the link leads to 'make a copy' prompt?

  6. This only works with spreadsheets, but if you make a sheet public,
    then copy and paste the URL and replace the text after the # of the URL with &newcopy.

    So this:


    You can put that link on your website and others logged in can click on it and get a copy of the spreadsheet.

  7. Mr. Hoegh, You can use the Find and Replace built into spreadsheets and it will do the same thing. You are correct, if the viewer sees https, it shows as text.

  8. Love the viewer idea on your submission form! Thanks for sharing the support link on that too!

  9. Hi Everyone:

    The Sleepy Bibliophile should check out www.learnboost.com The Free LMS is fully integrated with Google products. We are using all the Google docs in our Google EDU platform...and love it! Kern excellent blog and resources. I promote with all my teaching staff as you take us, step by step through the "Google Wonderland" Thank you!

  10. I seem to have a problem with this. The entries from the form show up in the "entry' tab of the spreadsheet but not the "filter' tab which seems to be where the 'viewer' tab pulls from.
    Any help will be welcome!

  11. Hi Chris,
    When the kids copy and paste their link into the form, it will populate the entry tab. In the filter tab, click on one of the yellow cells. A pull down should appear on the right, this allows you to filter by the collected work. remember, you need to change the name of the assignments in the form itself.

  12. Just applied this to my Google Data dashboard. Very excited to pull one more thing into one place. Thanks again for sharing!

  13. I'm having trouble getting the find and replace function to actually work after setting the trigger. Have it been working for you?

  14. Hi Andy,
    I noticed a problem with the code, it's fixed now so you can get a new copy of the spreadsheet, or paste this code in replace what's there:

    function findandreplace(){
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    //Get https in a variable in column F
    var p = sheet.getRange('F2:F').getValues();
    var fp = []
    for(var i in p){
    //Filtered values

    //Function to replace https with http
    function filter(d){
    var regex=/https/g;
    if (regex.test(d))
    return d.replace(regex,'http');
    else return d;

  15. Thanks for looking into it--I made an intermediate hidden sheet that sorts the list and changes the https with an array formula:
    =arrayformula(if(iferror( (find("https",entry!F1:F,1) ) ,entry!F1:F)=1,replace(entry!F1:F,find("https",entry!F1:F),5,"http"),entry!F1:F))

  16. That's great Andy, I'll check that out, thanks!