Iterate through every cell in a range in Google Sheets using Apps Script

A common use case when working with data in Google Sheets using Apps Script involves taking some action on every cell in a range. For example, you might want to ensure that every cell in the range meets some criteria.

There are two ways to iterate through a range: iterating through cell values (the data) or iterating through the actual cell objects (which gives access to formatting, formulas, etc.). This tutorial will show you both approaches.

In this tutorial, I will show you how to iterate through every cell in a range in Google Sheets using Google Apps Script.

Since a spreadsheet is a two-dimensional grid of cells, the data in a range that is larger than a single cell is structured as a two-dimensional array in Apps Script (i.e., an array of rows where each row is an array of columns).

[
    [R1C1, R1C2, R1C3],
    [R2C1, R2C2, R2C3]
]

Method 1: Iterating through cell values

One way to iterate through the values in every cell in a range is by using two nested forEach loops. The code below shows you how to do this and uses two nested forEach loops to log the data in every cell in a range.

//@OnlyCurrentDoc

function logDataInEveryCell() {
 var range = SpreadsheetApp.getActive().getRangeByName("SalesData");
 var values = range.getValues();
 values.forEach(function(row) {
   row.forEach(function(col) {
     Logger.log(col);
   });
 });
}

var range = SpreadsheetApp.getActive().getRangeByName("SalesData");

var values = range.getValues();

values.forEach(function(row) {
   row.forEach(function(col) {
     Logger.log(col);
   });
 });

Method 2: Iterating through actual cell objects

If you need access to more than just the cell values, such as formatting, formulas, or notes, you'll need to iterate through the actual cell objects instead. Here's how to do this using nested for loops:

//@OnlyCurrentDoc

function iterateActualCells() {
  var range = SpreadsheetApp.getActive().getRangeByName("SalesData");
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  
  for (var row = 1; row <= numRows; row++) {
    for (var col = 1; col <= numCols; col++) {
      var cell = range.getCell(row, col);
      Logger.log('Cell value: ' + cell.getValue());
      Logger.log('Cell formula: ' + cell.getFormula());
      Logger.log('Cell background: ' + cell.getBackground());
    }
  }
}

var range = SpreadsheetApp.getActive().getRangeByName("SalesData");
var numRows = range.getNumRows();
var numCols = range.getNumColumns();

for (var row = 1; row <= numRows; row++) {
  for (var col = 1; col <= numCols; col++) {
    var cell = range.getCell(row, col);

Logger.log('Cell value: ' + cell.getValue());
Logger.log('Cell formula: ' + cell.getFormula());
Logger.log('Cell background: ' + cell.getBackground());

Which method should you use?

• Use Method 1 (iterating values) when you only need the data contained in the cells. This is more efficient because it makes fewer API calls to the Google Sheets service.

• Use Method 2 (iterating cell objects) when you need access to formatting, formulas, notes, or other cell properties beyond just the values.

In your code, you should replace the Logger.log()statements with the action you want to take on each cell or cell value. This could be generating a summary statistic, performing some analysis or even putting together some HTML to send out an email from Google Sheets.

Conclusion

In this tutorial, I showed you two ways to iterate through every cell in a range in Google Sheets: iterating through cell values using nested forEach loops, and iterating through actual cell objects using nested for loops. Choose the method that best fits your needs based on whether you need just the values or access to other cell properties.

Thanks for reading!