reset cell to zero when closing a Google Sheet

Question

I have a Google sheet with multiple sheets to display a student's incomplete assignments. The students write a student number in cell A5 to display their data on the "input" sheet. I want cell A5 to be zero when the student opens the file. I tried using the code below with the trigger "on open" but it is too slow to keep students from seeing other students' data. Is there a way to set A5 to zero on closing? The "input" sheet should always be the active sheet since it is the only sheet that isn't hidden.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("A5");
  range.clearContent();
}

Even if it wasn't slow, if two students open the sheet simultaneously, if one enters his id, student1's data will be visible to both student1 and student2. — TheMaster
TheMaster,True, it is the best I can do for now. When I'm not so busy with distance teaching, I will make a separate file for each student with a password. I'm just trying to make it as private as possible with my limited time. — FergyC
A modal dialog should ensure privacy. developers.google.com/apps-script/guides/dialogs — TheMaster

Unknown Unknown · Accepted Answer · 2020-04-20T10:00:51

You should use a modal dialog to prompt the user for the student id. Once you receive the student id you can decide whether display the desired information in another modal, send en email to the user, create a private sheet for him etc...

Here more info about model dialogues.

reset cell to zero when closing a Google Sheet

1 Answers