Archive Rows From Different Sheets And Remove Them To Initial Position (to Initial Sheet) When I Want To Unarchive (google Sheets)

I have 4 sheets in the same spreadsheet: 3 per employee (Elisa, Miriam, Victor) and 1 for archive (Archive). When a project is done, each employee can archive the entire row -thank

Solution 1:

First: If you want the same script to run for Miriam and Victor - you do not need to make a copies of the script.

More precisely, you should not do it, because this will lead to having three onEdit functions in the same project which is not allowed.

Also, copying the script and saving it under the other employes' names will not make it work for them.

Instead, you need to modify the script.

The line

if(s.getName() == "Elisa" && r.getColumn() == 2 && r.getValue() == true) {

specifies that the row shall only be archived if the sheet name is "Elisa". To make it work for the other employees modify it to:

if((s.getName() == "Elisa"||s.getName() == "Victor"||s.getName() == "Miriam" )&& r.getColumn() == 2 && r.getValue() == true) {

Second:

The line var targetSheet = ss.getSheetByName("Elisa"); specifies that the sheet to which the row shall be moved back is "Elisa".

If you want it to move to the sheet of the person who created this row - this row must have some information about its creator. Does it have some? If not, you must implement an additional column in all sheets that specify the creator.

If the creator name is stored in column S, you can change the else statement of your code as following:

else if(s.getName() == "Archive" && r.getColumn() == 2 && r.getValue() == false) {
    var row = r.getRow();
    var nameColumn = 19;
    var name = s.getRange(row, nameColumn).getValue();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName(name);
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }

Basically, you retrieve the name of the row creator with

var name = s.getRange(row, nameColumn).getValue();

and then pass the content of the variable to

var targetSheet = ss.getSheetByName(name);.

Thus, the targetsheet will be established dynamically, depending on the row.