
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.
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?
- Get the active spreadsheet using
- Get the active sheet and the modified cell’s row index and column index (one-based index)
- 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
- 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()
- 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
- Compare each column values and set necessary value to column D using getRange(row, column) and setValue(value)
OUTPUT:
Sours: https://javascript.tutorialink.com/trying-to-write-if-and-statement-in-google-apps-script/
Google apps script if else statements syntax
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 in Apps Script
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.
if (some condition is met) { //do something }Here is an example:
if (testScore >= 40 ) { //student passed the test return true; }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.
if (10) { //do something }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.
A note on null and NaN
A null value is similar to undefined. It represents something that does not exist.
NaN stands for "not a number". This value is usually the result of Apps Script encountering something else when it is expecting a number. For example, trying to divide a number by a string will result in NaN.
Logger.log(3 / "this is a string"); //outputs NaNThe 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.
if (some condition is met) { //IF block //do something } else { //ELSE block //do something else }Here is an example:
if (testScore >= 40 ) { //student passed the test return true; } else { //student failed the test return false; }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.
function scoreToGrade(score) { if (score >= 85) { return "A"; } else if (score >= 70) { return "B"; } else if (score >= 55) { return "C"; } else if (score >= 40) { return "D"; } else { return "F"; } }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".
Have feedback for me?
I'd appreciate any feedback you can give me regarding this post.
Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!
On the phone. We made a pit stop. Alex went to the toilet.
App script statement google if
Sonya was the first to interrupt the complete silence. How good, Tan. she said.
Google apps script-If else statements 05But I only wheezed. My hands did not know what to do with this dear, beautiful body, gently fucking me. They slid from breasts along smooth sides to buttocks and back.
You will also like:
- Allied electronics locations near me
- Andersen 400 series window hardware
- Trees n trends weekly ad
- Pressure washer nozzles near me
- Ping g425 hybrid adjustment chart
- What happened to eva gabor
He slowly stretched out in the seat and closed his eyes. She's a cute little thing, he muttered. Yes, - said Hans, - but hard to crack the vagina is terrible. Harry glanced at the captain and then threw his head back into one of the lifebuoys scattered around the cockpit.