I have created a sign in/out sheet for students within my building. Students will type their name in column A, click a checkbox as a timestamp when leaving and entering and a dropdown for where they are going. The information for each class is then sent back to another sheet where I process the information and gain data on each individual student's habits using a query function. I have utilized a few scripts for the timestamp and for overwrite protection to protect students from altering previous student information. My next goal is to have the students type their name and then generate a popup window asking for a password (specifically a lunch code that is unique to each student) to keep students from leaving their rooms under the guise of another student's information. Does anyone have an idea of how I could create such a thing? In the sheet that I am attaching the student names are located in tab labeled StudentRoster, in the roster column A has their name, and column B has their lunch code. In my mind I would like the student to select/type their name in the Sign In/Out tab in column A and generate a pop window to ask for their lunch code and to only proceed if the lunch code matches what is found in column B of StudentRoster tab. If this is not possible I am willing to hear suggestions/ideas for figuring something like this out.


  • Hi, welcome to WebApps. Two quick thoughts: 1) "specifically a lunch code that is unique to each student" call me sceptical but, "students being students", I don't think that this is would keep students from leaving their rooms under the guise of another student's information 2) if the validation code is in a spreadsheet, then there is the possibility that students can access those codes. Question: are you using the "Education" version of Google Docs?
    – Tedinoz
    Jun 3 at 2:30
  • Are you familiar with Prompt dialogs? Then combine the result with Is there a way to emulate Vlookup in Google Script?. I think the indexOf approach would be best.
    – Tedinoz
    Jun 3 at 7:47
  • 1
    You may want to consider creating a form instead of sharing a spreadsheet for data input. For authentication, use the option to collect their email addresses. Jun 3 at 19:19
  • Your script project has three onEdit() functions — only one of them will run. Jun 3 at 19:19
  • I second the suggestion of @doubleunary to consider using a Google Form for this. Form validation based on values from a spreadsheet contains an answer that explains how to create the Form validation (I have tested it in your scenario, let me know if you want the code), and How do i change/update validation of question (item) in google form with apps scripts shows how to update the Form validation (again I have tested code for you if you want it).
    – Tedinoz
    Jun 4 at 23:14


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.