How to have Google Spreadsheet add new rows automatically when users are added in another worksheet?

Question

I've created a Google Spreadsheet for a list of employees. I have separated each list into separate worksheets labeled Sales, Operations, and Admin. I have a fourth sheet that is to be the entire staff directory. What I'd like to do is have the rows automatically get inserted and/or updated in this fourth worksheet as new users are being created, old users are being deleted, and current users are being updated.

The column headers are Full Name, Email, Skype, Phone Extension.

What is the best way to approach this? I am not really a coder or know much about the Google Spreadsheet API, so any guidance is much appreciated.

Thanks in advance.

Answer 1

Would be easy to do the other way around - have one master sheet with "the entire staff" in. When you add/remove people, change this master sheet.

The other sheets would only need a single =Filter() formula in. The Sales, Operations, and Admin sheets would pull the filtered staff details from the master sheet.

Answer 2

A good way to copy a section from one sheet to another is using copyValuesToRange() function. Here is an example, Click here since you want this to be automated, you will also will use function like getLastRow() and triggers can be used as well. You also might want to make sure that you don't have duplicates and and a for loop and if statement to check to see if that person is already in that sheet.

I would suggest either creating a form and have those the headers in the form there and have a trigger for form submission. Or you can also mess with the UI of the spreadsheet to run a function that will place the info under two sheets at a time. You can do this with radio buttons for what type of employee they are, and textarea for the info that will be imported into the spreadsheet.

I hope I gave you a good start, good luck with your code.