As a teacher, I use collaborative spreadsheets with students to have everyone in my class participate on the same document at the same time. Spreadsheets allow me to create multiple tabs to give each student their own sheet to work on within the same document. This gives me one document to open and assess.
Previously I had posted a script I wrote (RosterTab) that generates a new blank tab for each student by naming a tab in the spreadsheet for each student on the roster. Giving students a blank sheet to organize their responses or calculations is very useful, however sometimes students need to respond to specific prompts or have their work organized in a specific way.
This spreadsheet template allows you to create a graphic organizer, rubric or other template sheet to easily make multiple copies of the template. The script in the spreadsheet takes a roster of your students and assigns each student to a copy of the template by listing their name on the spreadsheet tab.
TemplateTab Template
Go to https://alicekeeler.com/templatetab to create a copy of the template for you to use with your students.
Suggested Uses
- Classroom Discussions: Create a tab for each discussion topic by putting a single word summary onto the roster tab instead of student names. Share the spreadsheet with your students and give them editing rights. For each tab, students place their response to the discussion. CLICK HERE for my template for classroom discussions.
- Rubric Grading: Create the rubric for your assignment on the template tab. TemplateTab will generate a copy of the rubric for each student on the roster. This will allow you to evaluate each student using the rubric without having to make paper copies.
- Peer Evaluation: On the roster tab list out the students in the class. On the template tab create a student friendly rubric along with spots to create comment boxes. Run the script to have a peer evaluation template for each student in the class. Use Google Classroom to give each student their own copy. Students will use their peer evaluation spreadsheet to provide peer feedback for each classmate.
- Graphic Organizer: Spreadsheets are designed to organize information. Create a graphic organizer template on the template tab. Create a graphic organizer per student on the roster. Alternatively, you may wish to have students fill out the graphic organizer multiple times; such as for each character in a book. Instead of listing student names in the roster, list the different topics the students will fill the graphic organizer out for.
25 thoughts on “Google Sheets: Copy a Template for Each Student”
Hi Alice,
Thanks for posting this. I tried to do it and the script didn’t work. It asked for my authorization and then nothing happened. Any ideas?
In the last few days the authorization thing seems to be funky. Sometimes you have to authorize twice. Run it again.
Hi I have also been trying to use this and I tried three times and nothing happened. Would love if you have any ideas for trouble shooting. I don’t know if it matters but I am in Sweden. Looking forward to using your awesome tools so would love some help. Thank you in advance.
Try it in your personal Gmail
Thank you yes it worked with my personal. Do you know any reason why it doesn’t work with my work one? I think a lot of my staff members would enjoy these tools but it makes it a pain to do it with a personal account. Would love any tips you have
Your apps admin disabled DriveApp
Thanks so much for this. I am not a teacher, but I need a sheet copied for 70+ employees. It worked great.
So glad it was helpful! I have a script that can kick that up a few notches! Script Project
Hi there, this looks really great but how do you keep students from writing on each other’s templates or deleting/ messing with each others’ work if you have those kinds of students?
This does not share the work with individual students. Each student makes their own copy. Unless the student gives access to other students they should not have an issue. Are you using Google Classroom? That would help a lot.
Oh so they each make their own copy of their sheet..No haven’t started with Google Classroom… will get onto that…
I know some people are against student numbers, but I think it can be useful. Instead of putting names in the roster, I can type student numbers. Got example, 1 to 25. Next, use the script. Then, a student would go to his/her student tab and rename (replace the number) with his/her name. How can this be helpful to me? Anywhere I still see a number is a student that was absent, which reminds me to keep the absent student on top of things. BTW, this is a great script! Where can I learn how to script?
Start by typing in script into my blog. I have a couple of blog posts on Google Apps Script.
Any suggestions on how to make a sheet file for each student, but keeping protected cells protected? When using Google Classroom, the kids are the owners and are able to over write what I protected.
Only if you know Google Apps Script. If the student is the owner of the doc, they have full editing rights regardless.
Alice
Love your app on creating multiple tabs from a template. I am working towards my admin degree and have been requested to create a sheet that automatically creates tabs for each student, which this dose, but they want to be able to calculate six times a year to see if students meeting the district goals and if the students are reaching their goals. Is it possible to create a countif statement each time a new sheet is create so it will tell the number of students who are above proficient and number of students meeting their goal.
I am sure you could.
The workshop in Plano, Texas on Saturday was great. I immediately created a template tab warm up with a tab for each student. I also like the idea of using this idea for a reading comprehension check. I created tabs such as plot, setting, main characters, etc. How do I allow each student to complete all tabs in their own spreadsheet, instead of collaborative as I did with the warm up? Or is there a better tool to use?
The purpose of this tool is collaboration. If you want separate spreadsheets you make a copy of a template for each student… Google Classroom is the easiest way to do this.
Hi Alice,
Currently, Template Tab is not verified with Google. I was planning to show it at a conference tomorrow. Is there any chance that you could get it reverified with Google?
Thanks!
No. It won’t be verified possibly never.
For template tab which bit of the script do I need to change please if my list of names is not on sheet1
Put your list of names on sheet one. Move the tab.
Hello! I have found Template Tab to be incredibly useful, however, I would like to use that first and then use Roster Tab2 to send emails to students with a link to their tab. I haven’t been able to figure out how to use both add ons at the same time.
You would not easily be able to use both unfortunately.