Apps Script Course: Conditions

Conditions are very useful and allow you to perform actions (or not) based on one or more conditions.

It's somewhat like the IF function in Google Sheets but even more practical.

If

To execute one or more lines of code under a condition, use if followed by the condition in () and the instructions to execute in {} if the condition is true.

Here is an example of code that will start by retrieving the value of A1, then check if this value is empty, and only if the condition is true, enter "To be completed" in cell A1:

const cellA1 = SpreadsheetApp.getActiveSheet().getRange('A1');
const valueA1 = cellA1.getValue();

if (valueA1 == '') {
  cellA1.setValue('To be completed');
}

Else

To perform another action if the condition is false, add else followed by the instructions in {}:

const cellA1 = SpreadsheetApp.getActiveSheet().getRange('A1');
const valueA1 = cellA1.getValue();

if (valueA1 == '') {
  cellA1.setValue('To be completed');
} else {
  console.log('Cell A1 not empty');
}

Else if

To add intermediate conditions, add else if followed by the condition in () and the instructions in {}:

const cellA1 = SpreadsheetApp.getActiveSheet().getRange('A1');
const valueA1 = cellA1.getValue();

if (valueA1 == '') {
  cellA1.setValue('To be completed');
} else if (valueA1 > 0) {
  console.log('Value = ' + valueA1);
} else {
  console.log('Invalid value');
}

Note that you can add multiple else if instructions if necessary and that the else instruction is optional.

Comparison Operators

Here is the list of comparison operators you can use in your conditions:

The difference between the == and === operators is that the latter also checks if the data type is the same. Here's an example:

const value1 = 10;
const value2 = '10';

if (value1 == value2) {
  console.log('Test A'); // Displayed because values are identical
}
if (value1 === value2) {
  console.log('Test B'); // Not displayed because types are different (value1 = numeric, value2 = text)
}

When in doubt between using == or ===, it's better to choose the latter.

And

To add multiple conditions at once and check if they are all true, use the && operator (the equivalent of the Google Sheets AND function):

const number = 45;

if (number > 0 && number <= 100) {
  console.log('Number within the range 1 to 100'); // Displayed because both conditions are true
}

Or

To add multiple conditions at once and check if at least one condition is true, use the || operator (the equivalent of the Google Sheets OR function):

const number = 234;

if (number < 100 || number >= 200) {
  console.log('Number outside the range 100 to 199'); // Displayed because one of the 2 conditions is true
}

Not

To start, remember that instructions are executed only if the condition is true, so it is generally unnecessary to specify == true:

const test = true;

if (test == true) {
  console.log('"test" is true'); // Displayed
}

You can simply write it like this:

const test = true;

if (test) {
  console.log('"test" is true'); // Displayed
}

If in this case you want to know if it's false, specify == false (or === false):

const test = false;

if (test === false) {
  console.log('"test" is false'); // Displayed
}

The message here is displayed because the condition is true (the condition asks if "test" equals "false", which is true).

Another way to do this is to use the ! operator (the equivalent of the Google Sheets NOT function) which will return the opposite boolean value (in other words, "true" will become "false", "false" will become "true"):

const test = false;

if (!test) {
  console.log('"test" is false'); // Displayed
}

Ternary Operator

Here is an example that adds "child" or "adult" to the variable text depending on the age:

const age = 12;
let text = 'Applied rate: ';

if (age < 16) {
  text += 'child';
} else {
  text += 'adult';
}

console.log(text); // Displays: "Applied rate: child"

This type of condition can be simplified into one line using the ternary operator, by entering the condition ? value if true : value if false (somewhat like the IF function in Google Sheets):

const age = 12;
let text = 'Applied rate: ';

text += age < 16 ? 'child' : 'adult';

console.log(text); // Displays: "Applied rate: child"

Or even shorter:

const age = 12;
const text = 'Applied rate: ' + (age < 16 ? 'child' : 'adult');

console.log(text); // Displays: "Applied rate: child"

Feel free to use the ternary operator or if/else statements in a case like this.