Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

One Spreadsheet to Rule Them All

One Spreadsheet to Rule Them All

Untitled drawing (2)

 

If you have a task you want your entire class to do, here is a spreadsheet tip that can make the process easier.

Create a Google Spreadsheet

spreadsheet

 

Blue Share Button

Click on the blue share button in the upper right hand corner. Change the sharing rights to anyone with the link can edit.

change sharing

 

anyone with the link can edit alice keeler

 

Alternatively you can copy and paste the email address of all your students into the sharing settings. This will allow you to restrict who can type on what tab.

Create a Tab

By pressing on the plus icon in the bottom left of the spreadsheet you can create a tab per student.

add a tab

 

If you create a template to help students organize their work you can click on the arrow on the tab (hover over the tab, it appears) and choose to duplicate.

duplicate the tab alice keeler

 

Rename the Tabs

Double click on the tabs to put the students name on the tab.

Protect the Sheet

If you are concerned about students editing the tabs of other students you can protect the sheet. This only works if you expressly entered the email address of the students into the sharing settings. Click on the arrow of the tab and choose to “Protect sheet…”

protect sheet alice keeler

 

Choose the 3rd option “Me, and the collaborators selected below:” Check box the student who can edit that tab.

share with specific people alice keeler

 

Share the Link

Share the link to the spreadsheet with the students. This means you have one document to check over. It also makes it easy to see each students progress and to give them comments as they work on it. Simply click on the different tabs.

Hint: If you go to a tab and copy the URL while on that that tab, the link goes directly to that tab. This means each student has a unique URL pointing to their work.

5 thoughts on “One Spreadsheet to Rule Them All

  1. I sent a tweet asking about something like this, then searched your website a bit more and found this post that comes pretty close to what I’m looking for.

    I’ve got a Google Sheet with 22 tabs. The first tab is my own, and I don’t want anybody messing with it (except me). I can protext that tab.

    The next 21 tabs each belong to a specific student. I’d like to be able to invite each student to view her/his own tab. You have a post showing how to use “#gid=7” to point a student to a specific page. But what I don’t see exactly is how to restrict a student to seeing their own page and NONE of the other pages.

    The workaround in my mind is to simply create a separate spreadhseet for each student. This isn’t a horrible idea necessarily. But I wondered if it’s possible to give each student access to that one tab only?

    Thanks.
    Lowell

      1. Just to add to this great post, when using the Importrange function, the new sheets requires this format.
        =IMPORTRANGE(“1cPeM3cSyoIb-0bD2H9Ze7Nt6754dfRTP0eoXEHDvWw/edit#gid=1363493686″,”A1:B3”). It’s not what the documentation was saying so I thought I would share.

Leave a Reply

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

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.