Last school year I had all my students turn in their work to a Google Form.  This allowed me to go paperless.  They posted all of their work to a WordPress blog, which gave them a student portfolio, but would turn in the link to the Google Form.

What I loved about this system is I did not have to do any data entry.  I did not have any papers to carry around to grade.  I was able to give students quick and direct feedback via the comment boxes in WordPress.  The information was in a spreadsheet so I could sort the information any way that I wanted.

Where my system fell apart was being able to give each student a list of assignments they had created.  Today I feel one step closer to being able to make this system work.  I discovered how to link from one spreadsheet in Google to another.  The initial set-up would be cumbersome for a high school teacher with 200 students, but theoretically it would be a one time set up.

  1. Create a Google Form to collect student assignment data.
  2. Leave this spreadsheet private.
  3. Create a Google Spreadsheet per student!  Share that spreadsheet with the student but allow them to only VIEW not edit.

    1. Theoretically you could publish the spreadsheet links to your website since the student spreadsheets are private and only shared with viewing rights with the student.  So only if the student is signed in could the spreadsheet be viewed.
    2. By creating a public list of the student spreadsheets you could avoid students not remembering how to find their individual spreadsheet.
  4. In the first cell use the formula =importrange(“spreadsheet key”,”Tab_Name!Range:”)
    1. The spreadsheet key comes from the URL of the original spreadsheet.
    2. Notice in the URL it says key=
      Copy not the entire URL but just the key that comes after the equals sign.
      Do not include the #gid.  (#gid specifically refers to the sheet of the workbook)
    3. You need to put the key in quotations.
    4. Put a comma.
    5. Also in quotation marks, which sheet in your original Google workbook has the student data, type the name of the sheet.
    6. Type an exclamation point.
    7. Give the range the students data is contained.  For example A1:D30
    8. End quotation marks.
    9. End parenthesis.

For example if Mark J’s information is on a tab named “Mark J” and the data is between cells A1 and E14 then on the student spreadsheet you could have the formula =importrange(“0AvflfTGZ15dhdEd4TTlrOG1xZHRTWkZZV3RGLS0wcmc”,”Mark J!A1:E14″)

Theoretically this would auto update.

Subscribe to the Alice Keeler Newsletter


WP Twitter Auto Publish Powered By :
%d bloggers like this: