Google app script if statement

Google app script if statement DEFAULT

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

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
  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:

enter image description here




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 else statements

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.

Sours: https://amarindaz.com/google-apps-script-if-else-statements/
  1. 2019 ford transit connect reviews
  2. Shark mouth open clip art
  3. Is bought an action verb

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 NaN

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.

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!

Sours: https://spreadsheet.dev/conditional-logic-apps-script
Google apps script-If else statements 05

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 05

But 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:

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.



2280 2281 2282 2283 2284