Collaboration platform with restricted fields / roles for school reports

Question

In a project, the task is to create a platform where teachers can enter texts for the school reports of their pupils. Not all teachers should be able to enter text for every pupil. In fact, they should only enter text where their ID is assigned to the subject.

I have got an Excel file which has 9 sheets in total. 8 sheets are containing the data of the pupils, their subjects and corresponding teachers including the ID. Then there is one sheet that has all the names of the teachers and their IDs (for the mapping later on). The pupils can have different teachers for similar subjects (English is taught by two different persons in the same class) and they can have different subjects in general (Sports, Arts etc.). As I already mentioned, the teachers should only be able to edit or see the fields/cells that they are intended to edit.

This is what a list for one class looks like (example)

For now, I was looking on several approaches and systems, but as I am not a backend developer (although I can edit / modify systems to fit my needs). There was nothing that gave me a good solution.

A) Google Docs - Spreadsheet

They offer protected cells, but you have to assign them one by one. Or is there a way to create a script that can lock / unlock a field based on the value of another field? At least, I did not find this and still - how should I find out the ID of the user that is currently signed in?

B) CMS

Maybe there is a CMS that offers the ability to hide text fields from users? I could create the users in the backend/frontend from the excel list and then they need to login to enter the texts. But the texts need to be stored somewhere →

C) individual SIMPLE Database / PHP solution

Is this maybe a really easy task for a simple database / PHP script? The Excel data could be loaded into the database and then be altered via a PHP page. I think that the task is not that complicated - but I really have no idea where to start.

Answer 1

I would suggest first modify spreadsheet pages into a teachers page with for each each teachers their names, subject(s) & classes taught in that/those subject(s) with a designation for each + if each group of students has a class/homeroom teacher, etc. which class(es) & Extras they are responsible for, e.g.:

Teacher    Home Room    Subject Groups    Extras
Dr H. Watson  4B        Logic 1A, Biology 3C, Biology 4A   Shooting Club
etc.

Then for each year have a page with the pupils, in alphabetical order, have which form they are in which subject streams they attended and which extras that they attended, e.g.,

Student   Home Room    Subject Streams   Extras
Jane Doe     4B        Logic 3A, Biology 3C, ..   Football 3
etc.

This is probably a more natural set of information and you may already have it.

Then using python you can use xlrd to parse your excel sheet directly or use Excel to save the data as a .csv file of teachers plus one for each year and then use the built in csv library to parse the files.

Once you have parsed the files into a set of lists of dictionaries you can run through them something like:

for teacher in teachers:
    template = Template.Generate(teacher)
    homeroom = teacher['home room']
    subjects = set(teacher['subjects streams'])
    extras = set(teacher['extras'])
    for student in students:
        s_name = student['name']
        if student['home room'] == homeroom:
            template.AddHomeroomEntry(s_name)
        for class in set(student['subjects streams']) & subjects:
            template.AddClassEntry(class, s_name)
        for extra in set(student['extras']):
            template.AddExtraEntry(extra, s_name)
    template.write()

Where Template is a class that has the methods used. The template write method could use xlwt to generate a spreadsheet per teacher with form elements for them to fill in for each student in their homeroom and each student in each class that they teach & extras group that they lead. You could either have these organised by student or by year/class & extras - I would suggest discussing this with the teachers. For each pupil you can have both some sort of grade, (or more than one), plus comments, e.g. you could have drop downs in the subject class entries for ability, attention, attitude, etc. and a comments field while the homeroom entry would have different drop down fields.

These spreadsheets could then be sent to each teacher, (python could do that for you too), to fill in. You could then use a similar approach to collate all of the returned spreadsheets and generate the individual students reports.

Note that as you get more familiar with python you could also use it in a similar manner to generate a set of web pages to allow teachers to fill in the data online or other approaches but for the moment one spreadsheet per teacher is probably the simplest approach to get started with.

Thus:

• each teacher will only see their own comments on each student
• each teacher will only be asked about those students that they interact with
• a given pupil will hopefully have comments from each teacher that they interact with
• if more than one teacher teaches a given group of pupils they will both be asked to comment on the pupils in the group.

If your locations legal framework is similar to ours would suggest that each spreadsheet sent to the teachers includes a reminder that the spreadsheets, complete with who filled them in will have to be retained for a number of years and that they may have to be disclosed to legal authorities in some situations.

Answer 2

The solution I am suggesting here would come under the 3rd option you have listed (custom solution using PHP or some programming language). Essential Spreadsheet for JavaScript can be used with a Microsoft .NET based backend to display the Excel file and you can limit the editing of individual cells based on the currently logged in user. A sample application that shows this functionality is available here.

The entire product is available for free using the community license if you qualify. Note: I work for Syncfusion.