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 a Google for Education Certified Trainer and is the Lead Technology Integration Specialist for Brady Middle School in Ohio's Orange City Schools. In the past he taught STEM, Science & Math in Stow-Munroe Falls, Ohio, where he was also a leader in the district's Technology Leadership Team and a co-advisor for the middle school's STEM Club. He has been an educator since 2003. His Bachelor's Degree is in middle-level education (math/science) from the University of Akron and his Master's Degree is in Instructional Technology from Kent State University. He has enjoyed providing more than 100 professional development opportunities at conferences and school districts across the state of Ohio. He is very involved in Twitter (@JakeMillerTech) and provides regular pointers for educators with his #GAFETip tweets.

5 thoughts on “Tab-Level Edit Rights in Google Sheets”

  1. 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.

    1. 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.

  2. @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!

    1. 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.

Comments are closed.