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:
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.
Yes! I am taking Alice Keeler’s Go Slow scripting course…learning a lot. Maybe when I get better I can give this a go.