Google app script if statement

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.

Answer

The problem in your current code is that you are just checking the value of the modified cell using

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:

What it does?

1. Get the active spreadsheet using
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
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 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:

In this post, you’re going to learn different types of Google apps script if else statements , multiple if else statements, nested if else statements, syntax and examples.

Google apps script if statement

 Google apps script If statement check/validate the condition(s) present inside the parenthesis ().Google apps script if else statements instruction(s) should be enclosed within the curly braces.

It executes the instructions enclosed within the if statement block only if the condition returns true. Otherwise, Google script compiler starts compiling the code present next to if statement block.

Syntax

if (condition) {

//block of code to be executed if the condition is true

Instruction 1;

Instruction 2;

}

Example

Go to Google drive -> New -> More -> Google apps script. A script editor window will be opened in a new tab. Enter the below code in the script editor, save and execute it.

Output

my_var1 is lesser than my_var2

Note: Go to View -> Logs to view the output.

In the above function, two variables my_var1 and my_var2 are declared and initialized with value 20 and 30 respectively. Google script compiler validate whether the my_var1 is lesser than my_var2. Compiler executes the instructions present inside if blocks only if the condition returns true. In this case, the condition returns true so it prints the statement “ my_var1 is lesser than my_var2” in the console logger.

Google apps script if else statement

Like if statement, Google apps script If else statement check/validate the condition(s) present inside the parenthesis (). If the condition(s) is true then instructions in if block gets executed. Otherwise, instruction in the else block gets executed.

Syntax

if (condition)

//Google apps script if else statements

{

//block of code to be executed if the condition is true

Instruction 1;

Instruction 2;

}

else

{

//block of code to be executed if the condition is false

Instruction 1;

Instruction 2;

}

Example

Go to Google drive -> New -> More -> Google apps script. A script editor window will be opened in a new tab. Enter the below code in the script editor, save and execute it.

Output

my_var1 is greater than my_var2

Note: Go to View -> Logs to view the output.

In the function learn_if_else_statement(), two variables my_var1 and my_var2 are declared and initialized with value 40and 30 respectively.

In the if statement condition – Google script compiler validate whether the my_var1 is lesser than my_var2. Compiler executes the instructions present inside if blocks if the condition returns true. In this case, the condition returns false so it prints the statement in the else block “ my_var1 is greater than my_var2” in the console logger.

Google apps script multiple if else statement

Multiple If else statement is used to check the conditions in the sequential order. If anyone of the condition becomes true then compiler execute it instructions and comes out of the multiple if else statement block.

Syntax

if (condition 1)

{

//block of code to be executed if the condition 1 is true

Instruction 1;

Instruction 2;

}

else  if(condition 2)

{

//block of code to be executed if the condition 2 is true

Instruction 1;

Instruction 2;

}

else

{

//block of code to be executed if none of the condition is true

Instruction 1;

Instruction 2;

}

Example

Go to Google drive -> New -> More -> Google apps script. A script editor window will be opened in a new tab. Enter the below code in the script editor, save and execute it.

Output

my_var1 is equal to my_var2

Note: Go to View -> Logs to view the output.

In the function learn_multi_if_else_statement, two variables my_var1 and my_var2 are declared and initialized with value 40 each.

In the first if condition, Google script compiler check whether my_var1 is lesser than my_var2. If it returns true then a block of code under the if statement gets executed and comes out of the entire multiple if else statement blocks.

If the first condition fails then google script compiler checks the second condition whether my_var1 is equal to my_var2. If it returns true, then block of code under the else if statement gets executed. In this case, it returns true so it prints the statement “my_var1 is equal to my_var2” and terminate the multiple if else statement.

Google apps script nested if else statement

Nested if else statement is self-explanatory. It surrounded by Nested blocks of If else statements. This will be helpful if your script needs to satisfy multiple conditions.

Syntax

if (condition 1)

{

//block of code to be executed if the condition 1 is true

if(condition 2)

{

Instruction 1;

Instruction 2;

}

}

else  if(condition 2)

{

if(condition 2)

{

Instruction 1;

Instruction 2;

}

}

else

{

//block of code to be executed if none of the conditions is true

Instruction 1;

Instruction 2;

}

Example

Go to Google drive -> New -> More -> Google apps script. A script editor window will be opened in a new tab. Enter the below code in the script editor, save and execute it.

Output

my_var1 is equal to my_var2

In learn_nested_if_else_statement() function, first if  condition check whether my_var1 is lesser than 50. After it returns true, it enters into that block and checks the condition my_var1 is lesser than my_var2. It returns false. So, it checks the else if condition whether my_var1 is equal to my_var2. It returns true. It executes the statement inside the block and comes out of the nested if statement.

Are you interested in learning Google apps script from scratch?. Here’s my free video tutorial Google Apps Script Tutorials For Beginners

Recommended Post:

Google Apps script – Introduction

Google Apps script- Variable Declaration

Google Apps script- For Next statement

Google Apps script- Switch Case statement

If you want to view the below video in Youtube click here

Related

Tags:google apps script if else

About The Author

Rajan

Rajan is a web geek-Blogger-Programmer- working in corporate firm as system analyst engineer. Whenever time permits he used to blog on recent trends in technology, monetizing tips, Programming concepts and technical guides to beginners in amarindaz.

Conditional logic is used to make your program do something only if a condition is met.

The IF statement will first check if a condition that you specify is met. If the condition is met, the code within curly braces ({ and }) will be executed. If the condition is not met, the code within the curly braces will NOT be executed.

The code within the curly braces is called the IF block. This is the code block that will get executed if the condition is met.

The condition to be checked must be an expression that evaluates to some value. If it evaluates to TRUE, the condition is deemed to be met.

Here is an example:

The condition to be checked must evaluate to a value. However, this value does not have to be a boolean. Apps Script will convert the value to a boolean before checking to see if it is true.

For example, consider the following code.

The code within the curly braces will be executed since Apps Script will convert the value 10 to true.

The rules that govern conversion to boolean are as follows

• The number 0 and an empty string ("") are converted to false.
• The values null, NaN and undefined are converted to false.
• All other values are converted to true.

The IF - ELSE statement has two blocks of code. The IF block and the ELSE block. The IF block is executed if the condition is met. The ELSE block is executed if the condition is NOT met.

Here is an example:

The IF - ELSE ladder can be used to chain together a series of conditional statements. Let's say you want to assign grades based on test scores. The grading key is as follows:

• A: score >= 85
• B: score >= 70 and score < 85
• C: score >= 55 and score < 70
• D: score >= 40 and score < 55
• F: score < 40

Here is how you might implement a function that accepts the test score as input and outputs the grade.

The above function first checks if the score is greater than or equal to 85. If yes, it returns "A". If not, it checks if the score is greater than or equal to 70. If yes, it returns "B". If not, it checks if the score is greater than or equal to 55. If yes, it returns "C". If not, it checks if the score is greater than or equal to 40. If yes, it returns "D". If not, there is nothing left to check, it returns "F".