Skip to content

Trying to write IF/AND statement in Google Apps Script

I have searched thoroughly but cannot find a the solution. I am trying to write an IF/AND statement in Google Apps Script. I will have 2-3 conditions with values coming from 2-3 columns respectively. Here is what I want the script to do

If column A is ‘abco’ and column B is ‘swift’ then ‘double stack’ in column D

If column A is ‘safeco’ and column B is ‘DHL’ and column C is ‘Airway’ then ‘single stack’ in column D

Columns A, B and C will all have several drop down options and different combinations will trigger different messages in column D, such as “single stack”. The script will have to work on 12 different tabs, 1 for each month. Here is what I have tried. Thanks for any help on this.

 function onEdit(e) {
      var ss = SpreadsheetApp.getActiveSheet();
      var r = ss.getActiveCell();
      var celladdress ='D'+ r.getRowIndex() 

      var value = (typeof e.value == "object" ? e.range.getValue() : e.value);

 
    if (r.getColumn() === 1 && value == "abco" && r.getColumn()=== 2 && value == "swift" && ss.getName()=='January'){
   
ss.getRange(celladdress).setValue('double stack');
}}

Answer

The problem in your current code is that you are just checking the value of the modified cell using e.range.getValue()

What you really need to do is just get he row index of the modified cell, and use that row index when checking the values of columns A to C and setting the value of column D


You can refer to this sample code:

function onEdit(e){
  var ss = e.source;
  var sheet = ss.getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var validSheets = ["January","February","March"];
  var value = [];
  //Check if modified cell is in column A to D and not the header row 1.
  //Modified sheet should be listed in the validSheets
  if(row > 1 && col <= 4 && validSheets.includes(sheet.getName())){
    
    //Get current row values from column A to C
    //Convert the 2-d array of values to 1-d array using flat()
    value = sheet.getRange(row,1,1,3).getDisplayValues().flat();
 
    if(value[0] == "abco" && value[1] == "swift"){
       sheet.getRange(row,4).setValue("double stack");
    }

    if(value[0] == "safeco" && value[1] == "DHL" && value[2] == "Airway"){
       sheet.getRange(row,4).setValue("single stack");
    }
  }

}

What it does?

  1. Get the active spreadsheet using e.source
  2. Get the active sheet and the modified cell’s row index and column index (one-based index)
  3. Define a list of sheet name where you want the onEdit() procedure be implemented. In this sample code, I defined a list of sheets under validSheets
  4. Check if the modified cell is within Column A to D and not the first row. Verify if the current active sheet name is listed in the validSheets using array.includes()
  5. Get the value of column A, B and C based on the current row index using getRange(row, column, numRows, numColumns) and getDisplayValues(). Use flat() to convert 2-d array to 1-d array of values.

I preferred using getDisplayValue() instead of getValue() since it will return the cell’s display value as a string

  1. Compare each column values and set necessary value to column D using getRange(row, column) and setValue(value)

OUTPUT:

enter image description here