How to Modify Matched Cells Across Multiple Sheets with Google Script

Question

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background:

I have a Google Spreadsheet used for user administration in my company with a main sheet containing a long list of users, sorted by their user ID. The user IDs are permanent and a user is assigned an ID which they relinquish when they leave. The first few columns are filled in manually and include Employee Number, First Name, Surname, Section and Role. The remaining columns relate to the various company systems a person may or may not have access to and can be set to either Y or N:

UserID | Employee Number | First Name | Surname | Section | Role | System1 | System2 | System3 | System4 | System5

The system columns are automatically set using the MATCH formula which checks another sheet containing IDs of all users with access to that system.
If the userID is found on the other sheet, it sets that column to Y on the main sheet. So here's an example row on the main sheet:

2365 | 76438467 | Matt | Overton | Admin | Assistant | Y | Y | N | Y | N

My question:
Is it possible, if I was to add an extra column at the end called Delete? with all rows set to N?

Could a script be run when the user sets the delete column to Y where the script does the following:

1. Copies the entire row and pastes the values and formatting (I.e. not any of the MATCH formulas themselves) into a new row on another sheet called Deleted Users with an additional column where the current date is filled in (to show when the deletion took place).
2. Clears the Employee Number, First Name, Surname, Section and Role information from the row, but leaves the userID intact.
3. Where an access column shows Y - locates the ID on the relevant sheet and clears the cell containing the ID from the sheet. (So the match formula on the main sheet would automatically take care of the rest and set itself back to N on its own.)
4. Set the Delete? cell back to N again.

I'm sorry, but I have zero knowledge of Google Script and what I'd like to do may be a little complex.

Answer 1

From the question

Could a script be run when the user sets the delete column to Y

Yes, it's possible by using an on edit simple/installable or on change installable triggers.

References

• https://developers.google.com/apps-script/guides/triggers

Answer 2

As a former system administrator this is a bad idea.

In general: You do not want to ever to anything that you can't undo.

In general: Anything that destroys data should have some way of double confirmation that this is what you want to do. The last thing you want is a script that runs when someone puts a Y in a certain cell.

You never clear the data from a user ID. Lock it? yes. Clear it? No. You need an audit trail.

In one sense you are reinventing the wheel. In unix system you have Network Information Systems (NIS) which can create arbitrary maps of various associations. In this set up, users are members of one or more groups, and groups are given access to particular systems.

When a person leaves, all the admin does is put a comment as the first character in the user's group membership list. This keeps his ID (you may want later to remember just who was user 37724) in place; keeps you from re-using his ID, and allows to see his name instead of 37724 on files he used to own.

Windows has a different way of doing this sort of thing in terms of domains and organizational units and such. Never got comfortable with that. Put in Windows NIS clients instead.