Best way to collaborate with a coworker who is collecting data from multiple people

Question

I work in a school office.  I took over my position a couple of years ago from someone who was tracking and documenting everything on paper, and I'm trying to streamline this as time allows.  Currently, I've been puzzling over how to better collaborate with my teachers next year regarding forms that they have to collect for every student in their class.  At the beginning of every school year we have hard copies of approx. 11 forms that go home with every student.  As new students start throughout the year, these forms are collected from them as well.  Sometimes they're given to me, sometimes they're given to the teacher.  I don't see these paper copies changing anytime soon.  In the past, each teacher (approximately 50 homeroom teachers) receives a paper to check off each form as they're collected.  When I started it was explained to me that when all of the forms are collected for a particular class, the teacher then turn in their paper (basically a printed spreadsheet) to the office.  This just isn't efficient.  For one, it doesn't take into consideration new students that start throughout the year.  I also need to know at a glance if the forms have been turned in, and follow up with families if they aren't.  Most paper spreadsheets aren't returned to the office at all, because of missing forms. 
When I explained this issue to a coworker, she suggested using Google Forms.  I'm not as familiar with Forms, and after a little research don't understand how that would work.  In my mind Google Sheets would work for this issue.  I could set up a Sheet for each teacher, and easily set up their students.  After looking over everything, there are only 2 papers that would need to be kept in the classroom by the teachers, as opposed to the current 5 forms per student that they keep up with now.  Two columns on the Sheet would be for those 2 forms, all other other forms could be checked off by me.  I have to sort and route all of that paperwork to various departments anyway.  The down side seems to be that I'd really only like a teacher to be able to view data in their own specific tab, and not everyone else's.  From what I've read, I'm not sure that that's possible with Sheets.  I did read about a work around, but it seemed like a lot of work to set up.  
If Google Forms would be a good option for this sort of task, I'd appreciate being pointed in the right direction to learn how that would work.  I did read that the data from Forms could be linked to a Sheet.  I'm just confused as to how a Form would work to track multiple forms for a classroom of students, especially since the papers aren't all turned in at the same time.

Adam D. PE · Accepted Answer

Hi Lisa,
I think your coworker is correct in that Google Forms will be an important part of streamlining the collection of the information.  How this occurs depends on if you need to have hard copies of the forms due to required signatures.  If you do not need physical signatures, then I suggest you try the following:
Create a Form that contains all of the questions from the 7 forms or create a Form for each of your forms
Email the Form(s) to the families for completion
You can then use AutoCrat to mail merge the results from the Form submissions into PDFs of the forms.  AutoCrat can then email completed copies of the Forms to the families, admin, and/or teachers.  Copies of the forms can also be stored in specific Drive folders.  This will allow you to share with the teachers only the Forms they need.  AutoCrat is a free add-on that is also FERPA compliant.
In the Sheets file that is linked to the Form(s), you can then use the spreadsheet to manage who has completed and turned in each Form.  All of the links to the merged documents is also stored in the linked Sheets file.
This system does take a little time to set-up but once it is running, it is extremely efficient.  We use this type of system to create every teacher's individual schedule that includes teacher specific lesson plans, directions, student alerts, and more.  These schedules can then be printed out for substitutes.
We also use Forms to collect volunteers personal data that is needed to run background checks before they can work with children in the buildings.
If you have not used AutoCrat before, I would highly recommend that you check it out.  There are many videos and tutorials online that can walk you through many of the options and uses for this add-on.
Please post any specific questions you may have and the volunteers here will do their best to help you with this project.
I hope this helps!
 
Adam
Diamond PE Docs volunteer
Silver PE Classroom volunteer
Cert. Educator 1 & 2
Cert. Trainer
(not a Google employee)
 
When you've received a response that answers your question, please observe these forum courtesies:
 • If you had a Sheets problem, leave your demo sheet shared as View Only as part of this solution's archive,
 • Click Recommend on the post that best addressed your question, and
 • Post again soon!