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.
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
Is it possible, if I was to add an extra column at the end called Delete? with all rows set to
Could a script be run when the user sets the delete column to
Y where the script does the following:
- Copies the entire row and pastes the values and formatting (I.e. not any of the
MATCHformulas 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).
- Clears the Employee Number, First Name, Surname, Section and Role information from the row, but leaves the
- 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.)
- Set the Delete? cell back to
I'm sorry, but I have zero knowledge of Google Script and what I'd like to do may be a little complex.