How to Check if a Cell is Empty in Google Apps Script(Google Sheets) -With Examples

Google sheets cells are used to store values in a tabular format.

You can check if a cell is empty in Google Apps Script using the SpreadsheetApp.getActiveSheet().getRange(“A5”).isBlank() method.

Basic Example

var values = SpreadsheetApp.getActiveSheet().getRange("A5").isBlank();

console.log(values);

This tutorial teaches you how to check if a cell is empty in Google Apps script using different methods and when it is appropriate to use each method. It also teaches checking if multiple cells are empty in a single statement.

Using GetRange and IsBlank Function

The isBlank() function checks if the specified cell is empty.

It returns

• True if the cell is empty
• False – if the cell is not empty

Use this method when you want to check if the cell is empty and doesn’t want to know the value of the cell.

To use this function and check if a cell is empty in the Google Apps Script

• Get the desired cell using the getRange() method by specifying the Cell range
• Invoke the isBlank() method to check if the range is empty

Code

var values = SpreadsheetApp.getActiveSheet().getRange("A5").isBlank();

console.log(values);

Output

false

Using getValue() and Comparison Operator (===)

The getValue() function gets the value of the specified cell.

Use this method when you want to know the value of a cell and check if it’s empty before using the value.

To check if the cell is empty using the getValue() method,

• Get the value of the cell using the getRange() method and the getValue() method
• Compare the value in the If construct and the === operator
• If the cell is empty, it’ll be evaluated as True
• If the cell is NOT empty, it’ll be evaluated as False

Code

var value = SpreadsheetApp.getActiveSheet().getRange("A6").getValue();

  if (value === '') {

    console.log(“The cell is empty");

  } else {

    console.log("The cell is NOT empty, and it has a value: " + value);

  }

Output

The cell is NOT empty, and it has a value: 5

The above methods demonstrated how to check if a single cell is empty. Now, let us see how to check if multiple cells are empty.

Check If Multiple Cells Are Empty Using isBlank

To check if multiple cells are empty using the isBlank() method

• Get multiple cells by specifying their range in the getRange() method. For example. A2:B3 fetches the cells A2, B2, A3, and B3
• Invoke the isBlank() method to check if these cells are empty
• It returns True if All the specified cells are empty
• It returns False if at least one cell is not empty

Use this method when you want to check if all the cells specified are empty or not at once.

Code

var value = SpreadsheetApp.getActiveSheet().getRange("A2:B3").isBlank();

console.log(value);

Output

false

Check If Multiple Cells Are Empty Using Comparison Operator

To check if multiple cells are empty using the comparison operator,

• Get multiple cells by specifying the range of cells in the getRange() method
• Get all values from the specified range of cells using the getValues() method
• It returns the values as a two-dimensional array
• Iterate through the array and check if each value is empty by comparing it against the empty value using the comparison operator

Code

 var values = SpreadsheetApp.getActiveSheet().getRange("A2:B3").getValues();

  for (const row of values) {

    for (i = 0; i < row.length; i++) {

      if(row[i]!==''){

      console.log(row[i]);

      }

    }

  }

Output

The value of the non-empty cell(s) will be printed as follows.

1

Additional Resources

• How to Get the Range to Set Value of a Cell In Google Apps Script