Google sheets script if function with several criteria-CodePudding

I use a script in which I have a line with the IF function that says: If in column 21 of the tab "Suivi Clients" there are the values "OUI" or "ANNULÉ", then the function executes.

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

I would like to modify to say: If in the column 21 AND column 12 of the tab "Suivi Clients" there are the values "OUI" or "ANNULÉ", then the function executes.

I tried like this but it doesn't work.

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

The code is here:

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())
      SpreadsheetApp.flush()
      sortCF()
    }
  }

CodePudding user response：

When I saw your script of sh.getName() == 'Suivi Clients' && rng.getColumn() == 12 && rng.getColumn() == 21, in this case, it is required to be true for all cases. But, I thought that manually editing the columns "L" and "U" simultaneously might not be your expected goal. I thought that this might be the reason of your issue.

If you want to run the script in your situation, how about the following modification?

Modified script:

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var rng = e.range;
  var value = rng.getValue();
  if (sh.getName() == 'Suivi Clients' && [21, 12].includes(rng.columnStart) && ['OUI', 'ANNULÉ'].includes(value)) {
    
    // do something.
    // Please put the script you want to run when the if statement is true.
    
  }
}

In this case, the columns and the values are checked using includes.

Reference:

includes()

CodePudding user response：

Try the code below:

function onEdit(e) {
  var spreadsheet = e.source;
  var sh = spreadsheet.getActiveSheet();
  var rng = spreadsheet.getActiveRange();
  var rngRow = rng.getRow();
  var rngCol = rng.getColumn();

  var dest = spreadsheet.getSheetByName('Clients Finalisé');

  var otherCol = sh.getName() == 'Suivi Clients' ? rngCol == 12 ? 21 : 12 : '';
  var [cell, otherCell] = sh.getRangeList([`R${rngRow}C${rngCol}`, `R${rngRow}C${otherCol}`])
                              .getRanges().map(range => range.getValue());

  if ((cell == 'OUI' || cell == 'ANNULÉ') && (otherCell == 'OUI' || otherCell == 'ANNULÉ')) {

    dest.insertRowBefore(7)

      sh.getRangeList([`C${rngRow}:G${rngRow}`, `L${rngRow}:N${rngRow}`, `P${rngRow}`, `U${rngRow}:W${rngRow}`])
      .getRanges().forEach(range => range.copyTo(dest.getRange(`${range.getA1Notation()[0]}7`), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false))

    sh.deleteRow(rng.getRow())
    SpreadsheetApp.flush()
    sortCF()
    
  }
};

The reason why your code did not work was because of rng.getColumn() == 12 && rng.getColumn() == 21 since you are using an onEdit() and you are only editing at one cell so technically it can only be either column 12 or column 21.

In the codes I've added a var to determine the other column so you have a hold of both columns. You can now check the cell and otherCell if it is either 'OUI' or 'ANNULÉ'.

Also for the script to copy multiple range, it uses too much line for repetitive codes so I tried to make it shorter.

Let me know if this works!

Here's the reference on how to copy multiple ranges: Apps script copy multiple range from 1 sheet to another spreadsheet