The Chemistry Data Book That Students Keep Overwriting
Northgate High School's chemistry team teaches five sections of 30 students, and lab groups of four share one sheet per group. Students currently create a new Google Sheet in My Drive, click Share, and set Anyone with the link can edit because the teacher's share with your group instruction was ambiguous. Result: duplicate files named Lab Data fill the Drive, two groups' data vanished when the owner deleted the file, and the department cannot tell which group entered which trial. The team wants a single data book per class that students can append to but not overwrite, and wants to keep the audit trail for a lab practical that is 20% of the grade. The school has Google Workspace for Education Fundamentals; shared drives are enabled but teachers cannot create them without an admin request. The IT admin is responsive but slow.
1. Recommend where the master data book should live and who should own it, given the shared drive constraint.
Read the worked answer
Put the master data book in a shared drive, not in a teacher's My Drive. Shared-drive files are owned by the district, so they survive staff turnover, and membership is by role rather than by link. Request the shared drive from the admin now; while you wait, use a teacher-owned folder containing a Google Form that appends each trial to a response Sheet. Students submit trial number, group code, and values; they cannot edit the Sheet because they only have submit access to the Form. That solves overwriting immediately and creates a timestamped log. The trade-off is that Forms are row-based, so a titration grid with 12 columns is awkward; if students must edit a grid, create one Sheet per group plus a master Sheet that pulls with IMPORTRANGE or a QUERY, and protect the master. Do not use Anyone with the link can edit as the interim fix; it creates unattributable edits, which is exactly what a 20% practical cannot tolerate. Document the interim model in the department meeting notes so the admin request has a rationale.
2. How do you preserve the audit trail for a grade that is 20% of the final mark?
Read the worked answer
Treat the audit trail as three layers, not one. Layer one: the Form response Sheet, which records timestamp, account, and submitted values and is not editable by students. Layer two: the master Sheet with protected ranges and named versions; at the deadline, use File > Version history > Name current version (Lab practical - submitted) so the state is frozen and labeled. Layer three: a PDF export of the master plus the Form responses at the deadline, stored in a read-only Assessment archive folder. Version history alone is not a forensic audit: a student can edit a cell and the history shows the change, but it does not stop a group from colluding outside the file, and restoring a version restores the whole file, not one tab. If a grade is challenged, open an older version, copy the affected tab into a new Sheet, and compare against the Form timestamps. Tell students the audit model in advance; visible assessment conditions are fairer than surprise forensics.
3. A group reports that their tab was overwritten. Walk through diagnosis and prevention.
Read the worked answer
Diagnose before you restore. Open File > Version history > See version history, select the tab, and look for the edit that replaced the cells; note the account and time. Because version history restores the entire file, do not click Restore on the whole book; instead open the older version in a second tab, copy the intact range, paste it into the current file, then re-protect. Check the Form log to see whether the group submitted values that match the intact version. Prevention: protect formula and header ranges with Protect range and set permissions so only the teacher can edit; give each group a named tab; and use data validation to reject text in numeric cells. If the overwrite was a paste of values over formulas, protect the formula column and teach paste special. Finally, ask whether the group was sharing a single login; shared accounts destroy attribution and are the usual root cause. Fix the account practice before adding more technology.