Today I was on EduSlam where I shared about using =importrange.

Here is one use for =importrange in a Google Spreadsheet.  If you have information you want to share with students, you may want to organize it into a spreadsheet. The problem is that throughout the semester you are likely to have an “oh shoot” moment and need to edit the information or add more things to the list. This is not a problem if you’re using a Google Doc or a Google Spreadsheet since it is always current. But what if you want students to CHECK OFF or make comments about the list you are sharing.

Step 1

Create a list in a Google Spreadsheet that you want to share with students.

Step 2

Copy the key from the original spreadsheet out of the URL.

Step 3

Create a new Google Spreadsheet and type =importrange(“key”,”tab!range”) into one of the cells.

The key is from the original spreadsheet, notice the quotations around it. The tab range is the name of the tab which has the list you created. If the name of the tab is Sheet1 and you want to share columns A, B, C and D then you would put “Sheet1!A:D” for the tab range. You can also restrict the range to be something like “Sheet1!A5:D20” to show rows 5 to 20. Make sure you take notice of the exclamation mark between the name of the tab and the range you want to share.

Step 4

Share the 2nd spreadsheet with students and instruct them to use file->make a copy. OR you can modify the URL with &newcopy. Students would then be able to edit and add to the spreadsheet to personalize it, check off what they have done in the list or add new tabs to their spreadsheet.

Since the students spreadsheet is importing the list, when you make changes or updates to the list, ALL of your students copies will be updated.

Try It

Here is a link to a spreadsheet with a list of assignments:
The spreadsheet is set as anyone can edit.  Add an assignment to the list.

Here is a link to a second spreadsheet that is using =importrange(
Notice this sheet has your new assignment that you added to the list.

In a high school setting you may have 150-200 students. If all 200 students made a copy of the 2nd spreadsheet and then used column D to check off which assignments they completed, then if you updated the list by editing or adding more assignments ALL 200 student spreadsheets would be updated!