Grade Book across a semester


Hey automators, I am a teacher and I would like to be able to keep a spreadsheet of my students’ final grades across the weeks. Ideally I want to be able to print out a spreadsheet (excel or .csv) from our LMS which includes the students’ names and final grade of that week. I would then like to be able to import that information to the master spreadsheet so I could see all of the students with their weekly grades from left to right. This all seems easy to do manually, except I have 200+ students and there is a significant enough amount of students removed or added throughout the semester.

Writing this I think a relational database tool like Airtable may be better suited, but it’s the comparison part that is throwing me. Is it possible to have the data automatically analyzed and then any students who are removed from the grade book can be greyed out and maybe any new students highlighted a certain color?


It wouldn’t be too tough to do this in Google Sheets by importing the new data to a new tab with the semester/week/whatever code as the tab name.
Using formulae such as INDIRECT, IMPORTRANGE, UNIQUE and FILTER, the data can all be pulled into the correct columns on a master sheet.
Conditional formatting can do the colours.

Other solutions may be better, but I do things like this for our data.