Google sheets script to move some values from a row to a row in another tab-CodePudding

I have a sheet in which I manage the progress of my clients' files. I tried one script that I found here to move row from one tab to another one. I need to modify the code to fit my needs...

This script does is moves a row from one tab to another when a specific value is entered (in the code below, it moves a row from "Suivi Clients" to "Clients Finalisé" when the word "OUI" is entered in column 21 (Column "U")).This script works well to move a whole row with one condition.

What I need is a bit more complex.

First of all, the 2 tabs "Suivi Clients" and "Clients Finalisé" are exactly the same and contain the same formulas. The formulas are ARRAYFORMULA in columns B-H-I-J-K-O-Q-R-S-T-X and Y. The other columns A-C-D-E-F-G-L-M-N-P-U-V-W and Z are empty and must be filled in manually. I explain this because what I would like, first of all, is that only the contents of the manually filled columns are moved from one tab to another.

Secondly, I would like the conditions in the "Suivi Clients" tab to be "OUI" or "ANNULÉ" in the U column so that the rows go from "Suivi Clients" to "Clients Finalisé". This would mean that the customer's file is paid or cancelled, so it is finished.

Once a customer's row is in the "Clients Finalisé" tab, I would like that if I change the value of the U column from "OUI" or "ANNULÉ" to "NON", the row goes back to the "Suivi Clients" tab. This would mean that the customer has decided not to cancel anymore or that there was a problem with the payment and therefore the file is not completely finished.

I might need 2 identical scripts that I would adapt and assign to each tab.

function onEdit() {
  // moves a row from a sheet to another when a magic value is entered in a column
  // adjust the following variables to fit your needs

  var sheetNameToWatch = "Suivi Clients";

  var columnNumberToWatch = 21; // column A = 1, B = 2, etc.
  var valueToWatch = "OUI";
  var sheetNameToMoveTheRowTo = "Clients Finalisé";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();


  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {

    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());
  }
}

That's my sheet for those who would like to see the form:

https://docs.google.com/spreadsheets/d/1CPcMx3Dhbqi-zO4D3jYNxO-PGjyW3iTfRo5gRmEB9p4/edit#gid=0

Thanks.

CodePudding user response：

Try this way

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var rng = e.source.getActiveRange();

  if (sh.getName()=='Suivi Clients' && rng.getColumn()==21){
    if(rng.getValue()=='OUI' || rng.getValue()=='ANNULÉ'){

      var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients Finalisé')
      dest.insertRowBefore(7)

      var plage = sh.getRange('C'   rng.getRow()   ':G'    rng.getRow())
      plage.copyTo(dest.getRange("C7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('L'   rng.getRow()   ':N'    rng.getRow())
      plage.copyTo(dest.getRange("L7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('P'   rng.getRow())
      plage.copyTo(dest.getRange("P7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('U'   rng.getRow()   ':W'    rng.getRow())
      plage.copyTo(dest.getRange("U7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)

      sh.deleteRow(rng.getRow())
    }
  }

  if (sh.getName()=='Clients Finalisé' && rng.getColumn()==21){
    if(rng.getValue()=='NON'){

      var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients')
      dest.insertRowBefore(7)

      var plage = sh.getRange('C'   rng.getRow()   ':G'    rng.getRow())
      plage.copyTo(dest.getRange("C7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('L'   rng.getRow()   ':N'    rng.getRow())
      plage.copyTo(dest.getRange("L7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('P'   rng.getRow())
      plage.copyTo(dest.getRange("P7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('U'   rng.getRow()   ':W'    rng.getRow())
      plage.copyTo(dest.getRange("U7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)

      sh.deleteRow(rng.getRow())
    }
  }

  // other onEdit here
}