'Googlesheets: Add script for when on click, apply a True and Date from range to another range based on value, AND update Data Validation

I have a project I'm working on, in which I am tracking names with an Entry and Exit date. What currently happens, is that I will add a Name and In-date to two cells, click a button to Add them, and they get appended into a row in a different location on the same sheet.

I am now creating a button which allows a user to select a Name from the list of Names that have been added (Data Validation), and also add an Out-date. I want my Button when clicked, to find that patient and apply a date to their Discharge Date column, and also, add a TRUE value to the in-line checkbox column that I have.

Lastly, I want the Data Validation for the selection range to exclude that "Out" name. Ideally, this would be done by only having names from A9:A show if D9:D=FALSE.

Here is a copy of my sheet. https://docs.google.com/spreadsheets/d/19Alj9Shytt7vGgquYTahowuWEzkYM0F3VOFswxFNmqM/edit?usp=sharing

As an example, I want a user to

  • select in J3, the name "Joey Wheeler" from the Data Validation
  • in H3, add a discharge date of 5/20/2022
  • the user will click the Discharge Patient button

What should happen is that for Joey Wheeler's E10, the date from H3 should be added, and for Joey Wheeler's D10, the checkbox should be changed to TRUE. Lastly, if the user were to open the Data Validation box to do this for another person, they would no longer see Joey Wheeler present.

How might I do the Data change (Date update and Set=TRUE) in a script? Also I suspect for the Data Validation, I'll need to create another column that does a Filter, and have my Data Validation grab from that, but I'm curious if I can do that in a script.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source