Home » Google » Apps » Docs » Google Spreadsheet: Reference an External Spreadsheet

Google Spreadsheet: Reference an External Spreadsheet

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.
      https://docs.google.com/spreadsheet/ccc?key=0AvflfTGZ15dhdEd4TTlrOG1xZHRTWkZZV3RGLS0wcmc#gid=0
    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.

7 thoughts on “Google Spreadsheet: Reference an External Spreadsheet”

  1. I use a form of this already, but I have hit a wall trying to use Gdocs for a similar task. I would like students to keep track of project milestones and check off when they are done. I would like to have a spreadsheet per class, but am worried that students might (accidentially or maliciously) check boxes for other students. I guess that I need a way to let students only edit a part of the spreadsheet with out protecting and inviting them. With 150 students, this would be a lot of administrative overhead. Any ideas?

    1. I do not see a way to let an individual student edit part of a spreadsheet without inviting or protecting. You can not assign to a person without assigning to a person… if that makes sense. Here is a thought. How about you create spreadsheet (A) that is a list of your tasks or assignments. Then create a spreadsheet (B) where the first page imports your list of assignments. In the column next to the assignments have a column header of “Completed.” Have each student COPY the spreadsheet you created. Now each student has a copy of your formula. They would then have a column that says completed. In a Google Form have each student submit the spreadsheet code that you need for the importrange. I would then write a formula to automatically write the importrange text string. In order to protect student information make your own copy of spreadsheet B, call this spreadsheet C. Copy and paste the import range for each student into the columns alongside the list of assignments. Have students put an X in their completed column and this x should automatically update to your spreadsheet C.

  2. You can automate all this process very easily with Apps Script (script.google.com). A simple script can create automatically all the spreadsheets, give the right sharing permissions, and even send a personalized email to every student with a link to his spreadsheet.

  3. Tryed both =importrange(“0Amn2x2SohOgWdEp5QXg1ZTZ1YnUxTjNFb3REcjZPQXc”;”List!A2:A10”) and =importrange(“0Amn2x2SohOgWdEp5QXg1ZTZ1YnUxTjNFb3REcjZPQXc”,”List!A2:A10”). It doesn’t work (Parse error).

    Please help!

    1. I use it all the time, so there is probably one tiny thing. For example I notice your first one has a semi colon (;) instead of a comma. The second one…. not sure, hard to tell without looking at it. Possibly you labeled the tab incorrectly.

  4. Anastassia and Admin,

    Google made some changes to sheets based on users imput, they are now more like excell than ever. So now insteat of looking for the key in thel url you just past in the URL then ther is no need to use the spreadsheet name just. The new formulas look like this.

    =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/10tXfnPWFxmCHCQhboFEwLxF_NwNfuQROY8mUZqkBiWE/edit#gid=0”, “!A8”)

    This a formula that I am using right now and it works! Good luck:) Going to check google scripts to see if that makes life eaiser.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.