Tab-Level Edit Rights in Google Sheets

In Episode 50 of the Google Teacher Tribe Podcast, Bailee Sandsmark, a 6th-grade middle school PE teacher, asked a Google Sheets question that got my gears turning.  All that gear turning inspired a new #EduGIF from me.  First, let’s look at her question:

I’d like to send out a Sheets template to all 250 of my students for them to individually track their fitness testing data, but then I would like to have an efficient way for them to share that info with me so that I can see all of my students’ data in one sheet. Having to access 250 different sheets makes my head spin…

While Matt & Kasey had a handful of good ideas of their own, I had another one that I wanted to share.  It came from a thought that Matt shared: it’d be nice to give each student a tab in one spreadsheet, but then each student could edit the others’ tabs.  That’s where “tab-level permissions” comes into play.  If you click on the tabs at the bottom of your Google Sheet, there’s a Protect Sheet option.  It’s also accessible from under Tools.  As you’ll see in the Animated GIF below, you can use this to give tab-level edit rights to specific students.

Before we get the GIF, a few notes:

  • If each tab will be identical, you can duplicate the tabs.
  • If you’d like to create a tab for each of your students, you can use Alice Keeler’s Template Tab add-on.
  • You can also use this to give or limit edit access for just specific cells – I do this sometimes to make sure no one messes up formulas that I have running.
  • In Bailee’s, situation, she’ll still have the issue that each student can see their classmate’s information (even though they can’t edit it).  To prevent this, you could give them code names or numbers.
  • The tabs that we’re referring to are technically called sheets, but I think that’s super confusing that the individual parts of Google Sheets are Sheets.  What!?

And finally, the GIF:

Tab-Level Edit Rights in Google Sheets Animation

Published by

Jake Miller

Jake is the host of the Educational Duct Tape podcast, the #EduGIF Guy, a Tech Integration Coach, speaker, Former STEM, Math & Science Teacher, and a presenter.

  • I have struggled with the same challenge. I used Alice Keeler’s FilterRoster add-on to create separate tabs for each student with all the information they enter into the Form Responses tab via a Google Form. I wanted students to be able to view their own tab and only their own tab. I wish it were possible to do for Viewing what you did with Editing by protecting individual tabs. But the only way I could figure to do what I wanted to do was to create separate Google Sheets for each student and then transfer all the information from a student tab in the master spreadsheet to a separate student spreadsheet with an importRange formula in the first cell. Then I share each new spreadsheet with just one student. It’s a little tedious to set up. If there’s an easier way to accomplish the same end, I’d love to know it.

    • I agree! It’d be nice if that were possible. Only options I see are a) my suggestions but with code names so they don’t know whose gsb is whose or b) fancy use of formulas like you’re suggesting.

  • @rdetwiler ImportRange was my thought as well…Not sure how it could be built but my visions is:

    -A Form is created asking for student name and spreadsheet ID from the student’s copy

    -The info from this form populates to a spreadsheet with Tab 1 called “Form Responses 1”. On this tab, the names would be in Col A and the IDs would be in Col B

    – Script is written and applied to the “Form Responses 1” tab that would: name each tab based on the student name (in Col A) and execute an importRange function that would include spreadsheet ID from Col B so that the data from student spreadsheets was automatically pulled in.

    – The script would create a third column on the Form Responses 1 tab that would link to each tab so there would not be a need to scroll through 200+ tabs.

    – The script would also create a “back to Form Responses 1 tab” button to get back to the master list easily.

    If this could be written, there would need to very little work done by the teacher.

    I am just starting my script journey so this is beyond me but I am pretty sure it could be done!

    • Whoa! Cool idea Jen. I’ve never done a script before, but in terms of Sheets formulas – definitely possible. The catch is: you’d have to have the kids approve the import range in their own spreadsheet in order for the data to show up.