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 emptyFalse
– 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 cellsA2
,B2
,A3
, andB3
- 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